ActiveWorkbook.ChangeLink

[Philip J Smith]

I have some code which updates the links for named files

Sub UpdateLink1()
ActiveWorkbook.ChangeLink _
"\Forecasting\FY2006\1106\OpsPlan\1106JudgementPaperLinksv003.xls", _
"\Forecasting\FY2007\0307\OpsPlan\0307JudgementPaperLinksv001.xls",
xlExcelLinks
End Sub

I want to make the macro dynamic so that it picks up these filenames from
named ranges TextForOldLink and TextForNewLink.

I tried the following substitution but it didn't work

Sub UpdateLink2()
ActiveWorkbook.ChangeLink _
Worksheet.Range("TextForOldLink").Value, _
Worksheet.Range("TextForNewLink").Value, xlExcelLinks
End Sub

Can anyone tell me please, how do I use the contents of Named Ranges in VBA
Code?

In this case the contents are lables.

Regards

 

[Susan]

Worksheet.Range("TextForNewLink").Value, xlExcelLinks

try taking out the quotes...........

Worksheet.Range(TextForNewLink).Value, xlExcelLinks


susan

 

[Jay]

Hi Phillip -

Given that your original static approach worked, a small change should be
all that is necessary.

Change Worksheet.Range to ActiveSheet.Range or to Worksheets("sheetName").
In fact, it might help to restructure your code just a bit. For example:

Sub UpdateLink2()
With ActiveWorkbook
.ChangeLink _
.Worksheets("sheetName").Range("TextForOldLink").Value, _
.Worksheets("sheetName").Range("TextForNewLink").Value, xlExcelLinks
End With
End Sub

 

[Susan]

Worksheet.Range("TextForNewLink").Value, xlExcelLinks

try taking out the quotes............

Worksheet.Range(TextForNewLink).Value, xlExcelLinks


susan

 

[Jay]

Hi Phillip -

As Susan suggests, also remove the quotes from the code in my previous post.
Thanks Susan!

 

[Philip J Smith]

Thanks for your input. I tried it and got the response
Runtime error - Object Required.

Regards Phil

 

[Philip J Smith]

Hi Jay.

Thanks for your response. I tried the code, after ammending for the sheet
name and recieved the message

"Compile Error - Argument not Optional" .ChangeLink was highlighted.

Regards

Phil

 

[Jay]

Hi again Phillip...

I just tested the code without the quotes around the range names and it
failed. If it doesn't work without the quotes, put them back in
("TextForOldLink").

 

[Jay]

Hi Phillip -

After making the quote adjustment in my third post (above), do you still
receive the compile error ? If so, check that your puncutation is correct in
the ChangeLink statement. Especially check that a space and the continuation
underscore character "_" follow the keyword ChangeLink. This sometimes gets
automatically deleted and might cause the Compile Error.

The compile error is telling us that either the oldlink name or the newlink
name is not being provided to the ChangeLink method. If the comma is missing
between the two, that would be a likely cause of the compile error.

 

[Philip J Smith]

Hi Jay.

Thanks for your help.

Ive tried the routine both with and without quotes with the same result.

When I run the macro I get an error message.

"Compile error Argument not optional" and .ChangeLink is highlighted in VBE
Debug.

I think that the filenames are not being passed to the .ChangeLink

Regards

Phil