ChangeLink
 

Hello all,
Can anyone help please?

I have a spreadsheet that rolls forward on a weekly basis.
Much of the data is pulled in from other spreadsheets
which also roll forward on a weekly basis.

The following is the code i use:

secondFilename = "C:\headoffice\"
secondFilename = secondFilename & CStr(Range
("D2").Value)
secondFilename = secondFilename & "_04_abc.xls"

thirdFilename = "C:\headoffice\"
thirdFilename = thirdFilename & CStr(Range
("D2").Value)
thirdFilename = thirdFilename & "_04_def.xls"

Range("X1").Select
Selection.Copy
Range("D2").Select
Selection.pastespecial Paste:=xlPasteValues,
Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

fourthFilename = "C:\headoffice\"
fourthFilename = fourthFilename & CStr(Range
("D2").Value)
fourthFilename = fourthFilename & "_04_abc.xls"

ActiveWorkbook.ChangeLink secondFilename,
fourthFilename, xlLinkTypeExcelLinks

D2 represents the week no.

The sheet can roll forward for weeks without problem and
then i get an error as follows:

Run Time Error
Method 'ChangeLink' of object'_Workbook' failed

and the macro stops on the line:
ActiveWorkbook.Changelink secondFilename, fourthFilename,
xlLinkTypeExcelLinks

Can anyone tell me why this is happening?

Any help much appreciated

Cheers

Joe