ChangeLink Name Issues

Hi,

I'm all but about to give up with with macro. I am trying to Update a link in Excel 2007. The new file name will be the same but it is a new location.

This is the standard basic record macro code (that works):

XXX = Source Path
YYY = File Name

-----
ChDir _
"XXX"

ActiveWorkbook.ChangeLink Name:= _
"XXXYYY.xlsx" _
, NewName:="YYY.xlsx", Type:=xlExcelLinks
-----
Simplest looking macro ever, I want to make XXX dynamic using cell D1 named SourceLoc, why doesn't this work:

-----
Dim LinkLoc As String

LinkLoc = Sheets("Start Numbers").Range("SourceLoc")

ActiveWorkbook.ChangeLink Name:=LinkLoc, NewName:="YYY.xlsx", Type:=xlExcelLinks
-----

Even if I add in ChDir, change to ThisWorkbook or any bits like that i cannot seem to get it to work, it doesn't seem to like the syntax of ActiveWorkbook.ChangeLink Name:=LinkLoc,

Thoughts?

Have you tried to use the range, itself as a reference?

Please Login or Register  to view this content.