Link - Absolute Reference?

  • Thread starter Thread starter DAA
  • Start date Start date
D

DAA

I have a template workbook(1) that is link to another
workbook(2).

Lets say in WB(1) Sheet1 A1 refers to WB(2) Sheet2 B2.

So in WB(1) Sheet1 A1 = +'C:\DAta\[wb(2).xls]INFO'!$B$2

I made a macro to EDIT $B$2 to be a relative reference =
"=+'C:\DAta\[wb(2).xls]INFO'!B2" so I can copy a wide
range. I made a macro to run the whole process. My
problem is, if I link WB(1) to another workbook, because
of changing from absolute to relative reference =

ActiveCell.FormulaR1C1 = "'C:\Data\[wb(2).xls = R[-10]C"

it will always refer to WB(2)!

Is there a way that I can change the macro to refer it to
a 'wildcard' file!

Thanks
 
DAA,

WorkbookName = "whatever.xls"

"=+'C:\DAta\[" & WorkbookName & "]INFO'!B2"

Utterly untested, but I think it's right.
 
I'm sorry Earl but it didn't work.
Regards,
DAA
-----Original Message-----

DAA,

WorkbookName = "whatever.xls"

"=+'C:\DAta\[" & WorkbookName & "]INFO'!B2"

Utterly untested, but I think it's right.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

I have a template workbook(1) that is link to another
workbook(2).

Lets say in WB(1) Sheet1 A1 refers to WB(2) Sheet2 B2.

So in WB(1) Sheet1 A1 = >
I made a macro to EDIT $B$2 to be a relative reference =
"=+'C:\DAta\[wb(2).xls]INFO'!B2" so I can copy a wide
range. I made a macro to run the whole process. My
problem is, if I link WB(1) to another workbook, because
of changing from absolute to relative reference =

ActiveCell.FormulaR1C1 = "'C:\Data\[wb(2).xls = R[-10]C"

it will always refer to WB(2)!

Is there a way that I can change the macro to refer it to
a 'wildcard' file!

Thanks
Click to expand...


.
Click to expand...
 
It kept giving a message: syntax or compile error and but
after adjusting and re-adjusting the macro, it worked!

Thanks for your tip Earl. Have a great weekend!

Regards

-----Original Message-----
I'm sorry Earl but it didn't work.
Regards,
DAA
-----Original Message-----

DAA,

WorkbookName = "whatever.xls"

"=+'C:\DAta\[" & WorkbookName & "]INFO'!B2"

Utterly untested, but I think it's right.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

I have a template workbook(1) that is link to another
workbook(2).

Lets say in WB(1) Sheet1 A1 refers to WB(2) Sheet2 B2.

So in WB(1) Sheet1 A1 = >
I made a macro to EDIT $B$2 to be a relative reference =
"=+'C:\DAta\[wb(2).xls]INFO'!B2" so I can copy a wide
range. I made a macro to run the whole process. My
problem is, if I link WB(1) to another workbook, because
of changing from absolute to relative reference =

ActiveCell.FormulaR1C1 = "'C:\Data\[wb(2).xls = R[-10] C"

it will always refer to WB(2)!

Is there a way that I can change the macro to refer it to
a 'wildcard' file!

Thanks
Click to expand...


.
Click to expand...
.
Click to expand...
 
Back
Top