+ Reply to Thread
Results 1 to 12 of 12

ChangeLink Command Not Working

Hybrid View

  1. 01-19-2006, 12:10 PM #1
    Dina
    Guest

    ChangeLink Command Not Working

    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.

    Reply With QuoteRegister To Reply
  2. 01-19-2006, 02:15 PM #2
    Dave Peterson
    Guest

    Re: ChangeLink Command Not Working

    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
    Reply With QuoteRegister To Reply
  3. 01-19-2006, 02:25 PM #3
    Dina
    Guest

    Re: ChangeLink Command Not Working

    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
    >
    Reply With QuoteRegister To Reply
  4. 01-19-2006, 02:40 PM #4
    Dave Peterson
    Guest

    Re: ChangeLink Command Not Working

    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
    Reply With QuoteRegister To Reply
  5. 01-19-2006, 03:00 PM #5
    Dina
    Guest

    Re: ChangeLink Command Not Working

    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
    >
    Reply With QuoteRegister To Reply
  6. 01-19-2006, 03:50 PM #6
    Dave Peterson
    Guest

    Re: ChangeLink Command Not Working

    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
    Reply With QuoteRegister To Reply
  7. 01-19-2006, 03:55 PM #7
    Dina
    Guest

    Re: ChangeLink Command Not Working

    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
    >
    Reply With QuoteRegister To Reply
  8. 01-19-2006, 04:10 PM #8
    Dave Peterson
    Guest

    Re: ChangeLink Command Not Working

    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
    Reply With QuoteRegister To Reply
  9. 02-17-2006, 12:00 AM #9
    whylite
    Guest

    Re: ChangeLink Command Not Working

    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
    >
    Reply With QuoteRegister To Reply
  10. 11-30-2011, 02:23 PM #10
    JudithL
    JudithL is offline
    Registered User
    Join Date
    11-30-2011
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: ChangeLink Command Not Working

    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
    Last edited by Cutter; 08-23-2012 at 08:29 PM. Reason: Added code tags
    Reply With QuoteRegister To Reply
  11. 08-23-2012, 03:57 PM #11
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: ChangeLink Command Not Working

    You do realize this thread was initiated over six years ago and JudithL posts closing in on a year ago.
    HTH
    Regards, Jeff
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1