ChangeLink Command Not Working

[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.

[Dave Peterson]

Are any of your worksheets protected?

Dina wrote:
>
> 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.

--

Dave Peterson

[Dina]

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

"Dave Peterson" wrote:

> Are any of your worksheets protected?
>
> Dina wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>

[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.

Dina wrote:
>
> No. They are all unprotected and I've Changed Directories (ChDir) to the
> proper directory as part of the loop.
>
> "Dave Peterson" wrote:
>
> > Are any of your worksheets protected?
> >
> > Dina wrote:
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[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.

"Dave Peterson" wrote:

> 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.
>
> Dina wrote:
> >
> > No. They are all unprotected and I've Changed Directories (ChDir) to the
> > proper directory as part of the loop.
> >
> > "Dave Peterson" wrote:
> >
> > > Are any of your worksheets protected?
> > >
> > > Dina wrote:
> > > >
> > > > 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.
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Is the workbook protected?

I'm out of guesses.

Dina wrote:
>
> 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.

--

Dave Peterson

[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?

"Dave Peterson" wrote:

> Is the workbook protected?
>
> I'm out of guesses.
>
> Dina wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I'd stay here and see if anybody else could guess.



Dina wrote:
>
> 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?
>
> "Dave Peterson" wrote:
>
> > Is the workbook protected?
> >
> > I'm out of guesses.
> >
> > Dina wrote:
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[whylite]

Did you ever figure this out. I am having the same problem?
--
Thanks!
Shane W


"Dave Peterson" wrote:

> I'd stay here and see if anybody else could guess.
>
>
>
> Dina wrote:
> >
> > 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?
> >
> > "Dave Peterson" wrote:
> >
> > > Is the workbook protected?
> > >
> > > I'm out of guesses.
> > >
> > > Dina wrote:
> > > >
> > > > 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.
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[JudithL]

I am having the same problem...using Excel 2007...sheet not protected

Here is my Macro:

Please Login or Register  to view this content.

[anonymous212270]

hey, i had the same problem

the reason it doesn't work is because when you recorded the macro, it already changed the name.

therefore, you can't change a link that's already changed (from filename1 to filename2) if filename1 no longer exists...
you can change filename2 to filename3 in your macro and it'll work fine (assuming your sheet isn't protected)

hope this helps

[jeffreybrown]

You do realize this thread was initiated over six years ago and JudithL posts closing in on a year ago.