ChangeLink Command Not Working
in [Programming]
|
From: Dina on 19 Jan 2006 11:04 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.
From: Dave Peterson on 19 Jan 2006 13:10 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
From: Dina on 19 Jan 2006 13:21 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 >
From: Dave Peterson on 19 Jan 2006 13:35 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
From: Dina on 19 Jan 2006 13:59 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 >
|
Next
|
Last
Pages: 1 2 Prev: Setting an Essbase Option using code Next: Pivot Table version (xlPivotTableVersion12) |