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

"Jay" wrote:

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



"Jay" wrote:

Hi Phillip -

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

--
Jay

"Jay" wrote:

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").V alue, _
.Worksheets("sheetName").Range("TextForNewLink").V alue, xlExcelLinks
End With
End Sub

---
Jay