ChangeLink Command Not Working

Dave Peterson · 01-19-2006, 04:10 PM

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 · 02-17-2006, 12:00 AM

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** · 11-30-2011, 02:23 PM

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

Here is my Macro:

Sub UpdatePSReports()
'
' UpdatePSReports Macro
'

'PSReports As String
'Sheet1 AS String
'PSFileCurrent As String

Application.DisplayAlerts = False
PSReports = ActiveWorkbook.Name
ActiveWorkbook.Worksheets("sheet1").Select
Range("D1").Select
Numcount = Selection.Rows.Count

For n = 1 To Numcount
Workbooks(PSReports).Activate
PSFileCurrent = Cells(n + 1, 4)
PSFileNew = Cells(n + 1, 5)
  
ActiveWorkbook.ChangeLink Name:=PSFileCurrent, NewName:=PSFileNew, Type:=xlExcelLinks

Next n
End Sub

**anonymous212270** · 08-23-2012, 03:53 PM

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