Using a command to change a link in a worksheet

[Vaslo]

Hello everyone,

I'm trying to change an external link in a worksheet. Basically I want to do the following -
1) Use a dummy link for initial setup so that all worksheets will have the same dummy link (with different cell ref)
2) In each tab, link to a different file name that is identical in setup and format but the values differ across tabs and are pulled in with external links (same link for each tab)

I want to get all values in one spreadsheet so I can do summing across tabs.

In short, I start with this in D12:
='Y:\FY14 Strategic Plan\BU Templates\Consolidated and Parsed by Template\Initial Test\[Copy of Manual_Schedules_BU_Strat Plan_final TEST.xlsx]Brand Sales'!D12
and want to change it to this:
='Y:\FY14 Strategic Plan\BU Templates\North America\US Consumer\[Manual_Schedules_BU_Strat Plan_final USCP.xlsx]Brand Sales'!D12

Of course the Cell Address will change and this link goes over 100 or so cells in each workbook, so I'd like the link and file name to change while the cell address matched the cell in both the master workbook and the individuals (D12 stays D12, links change depending on the worksheet).

I just can get it to work. I tried the following but got an error that says "Object doesn't support this property of method" . I deleted the extraneous code:

Please Login or Register  to view this content.

Coincidentally, BUPathArray points to an array of links, in particular this one is exactly: ='Y:\FY14 Strategic Plan\BU Templates\North America\US Consumer\[Manual_Schedules_BU_Strat Plan_final USCP.xlsx]

Thanks!

[Vaslo]

Is it possible that the ChangeLink command only changes full links? I can't seem to find much documentation that explains the limitations of the function. What about changing partial links. I thought this would have been so easy, and replace doesn't seem to do the trick either when it's an external link.

[JBeaucaire]

As it appears these links are merely formulaic references, I see no reason you couldn't just use the SEARCH/REPLACE (Ctrl-H) functionality to accomplish this.

Assuming:
1) The default string to "find in the formulas" is always the same
2) The replacement string is stored on the individual sheets somewhere, like Z1
or
2) the replacement string is provided to the macro as a popup

Please Login or Register  to view this content.

[Vaslo]

That was it - not sure what was wrong before. Thanks!

[JBeaucaire]

I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[Vaslo]

Thanks,

I honest to God tried to mark it but had no idea how to do it.