ChangeLink

  • Thread starter Thread starter Joe Bannister
  • Start date Start date
J

Joe Bannister

Hello,

I am using the following code:


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

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

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

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

ActiveWorkbook.ChangeLink secondFilename, fourthFilename,
xlExcelLinks

fifthFilename = "C:\"
fifthFilename = fifthFilename & CStr(Range("D2").Value)
fifthFilename = fifthFilename & "_04_siteatills.xls"

ActiveWorkbook.ChangeLink thirdFilename, fifthFilename,
xlExcelLinks

D2 is the week no. which obviously increases by one each
week. The spreadsheet has numerous links to two other
sheets which i alter by using ChangeLink.

This has been working fine until recently when i created
copies of the spreadsheet for other sites. Now i get an
error saying "Object 'ChangeLink' of Object'_Workbook'
failed.
If i go back to my original spreadsheet there is no
problem, can anyone tell me what's going on?

Cheers

Joe
 
First thing I would look at is that the file names you are building are
valid paths/filenames in the context of the machines having the problems.

Also, what version of excel and how is the code triggered. If in Excel 97
using a commandbutton, you should try changing the takefocusonclick property
to false.
 
Back
Top