ChangeLink Command Not Working

D

Dina

I have a situation where I am copying files from a "common files" folder into
individual folders. Then I have to change the links to refer to the newly
copied files within each folder. I keep getting "Runtime error '1004'
Method 'ChangeLink' of object '_Workbook' failed"

It doesn't seem to matter whether I use absolute addresses or variables.
I've tried the following:

ActiveWorkbook.ChangeLink Name:= _
"C:\BudDataRun\Common Files\INPUTSHEETABA-GF.xls", NewName:= _
"C:\BudDataRun\Gen Fund Budget\N-E0011\INPUTSHEETABA-GF.xls", Type:= _
xlExcelLinks
(The macro recorder used the Name:= and NewName:= labels)

'ActiveWorkbook.ChangeLink "C:\BudDataRun\Common
Files\INPUTSHEETABA-GF.xls", _
'"INPUTSHEETABA-GF.xls", xlExcelLinks


'ActiveWorkbook.ChangeLink RootDirectoryName & CommonFolder &
ABAFile, _
'RootDirectoryName & FilePathName & FilePathName2 & ABAFile,
xlExcelLinks


All variations get the same error message.
I'm running on Windows XP if that makes any difference.
 
D

Dave Peterson

Are any of your worksheets protected?
I have a situation where I am copying files from a "common files" folder into
individual folders. Then I have to change the links to refer to the newly
copied files within each folder. I keep getting "Runtime error '1004'
Method 'ChangeLink' of object '_Workbook' failed"

It doesn't seem to matter whether I use absolute addresses or variables.
I've tried the following:

ActiveWorkbook.ChangeLink Name:= _
"C:\BudDataRun\Common Files\INPUTSHEETABA-GF.xls", NewName:= _
"C:\BudDataRun\Gen Fund Budget\N-E0011\INPUTSHEETABA-GF.xls", Type:= _
xlExcelLinks
(The macro recorder used the Name:= and NewName:= labels)

'ActiveWorkbook.ChangeLink "C:\BudDataRun\Common
Files\INPUTSHEETABA-GF.xls", _
'"INPUTSHEETABA-GF.xls", xlExcelLinks


'ActiveWorkbook.ChangeLink RootDirectoryName & CommonFolder &
ABAFile, _
'RootDirectoryName & FilePathName & FilePathName2 & ABAFile,
xlExcelLinks

All variations get the same error message.
I'm running on Windows XP if that makes any difference.
 
D

Dina

No. They are all unprotected and I've Changed Directories (ChDir) to the
proper directory as part of the loop.
 
D

Dave Peterson

Does it work if you do it manually (Edit|links)?

If it works ok, how about recording a macro when you do it there. Maybe it'll
help you debug the problem.
 
D

Dina

I've already tried that. The first sample is the one that the macro
recorded. It worked fine when I did it manually, but when I tried to run it
from the program by calling the macro it crashed.
 
D

Dave Peterson

Is the workbook protected?

I'm out of guesses.
I have a situation where I am copying files from a "common files" folder into
individual folders. Then I have to change the links to refer to the newly
copied files within each folder. I keep getting "Runtime error '1004'
Method 'ChangeLink' of object '_Workbook' failed"

It doesn't seem to matter whether I use absolute addresses or variables.
I've tried the following:

ActiveWorkbook.ChangeLink Name:= _
"C:\BudDataRun\Common Files\INPUTSHEETABA-GF.xls", NewName:= _
"C:\BudDataRun\Gen Fund Budget\N-E0011\INPUTSHEETABA-GF.xls", Type:= _
xlExcelLinks
(The macro recorder used the Name:= and NewName:= labels)

'ActiveWorkbook.ChangeLink "C:\BudDataRun\Common
Files\INPUTSHEETABA-GF.xls", _
'"INPUTSHEETABA-GF.xls", xlExcelLinks


'ActiveWorkbook.ChangeLink RootDirectoryName & CommonFolder &
ABAFile, _
'RootDirectoryName & FilePathName & FilePathName2 & ABAFile,
xlExcelLinks

All variations get the same error message.
I'm running on Windows XP if that makes any difference.
 
D

Dina

Thanks for trying.

I've been working on this for 2 days. I had this (or one like it) working
last year on my old computer. I was running Windows 2000. This year I have
Windows XP. The only thing I can think of is that there is a conflict. The
OS sometimes does wonky things to VBA commands.

What forum would I go to for expert help with this kind of conflict?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top