K
ksu92me
Due to a server name change, we now have hundreds of Excel workbooks
with external links (in UNC form) to two other workbooks that are now
invalid. Opening one of these workbooks results in the dialog: "This
workbook contains one or more links that cannot be updated", with the
choices of Continue and Edit Links. Selecting Edit Links and browsing
to the required files (same location, just a different server name)
restores the links. The number of links in the workbook requires about
30 seconds to update.
I created a simple Visual Basic program that I'd hoped to let run
overnight, opening each workbook, changing the link paths, and saving.
It seemed very straightforward, but I have hit a snag.
Each workbook contains external links to two separate workbooks, but
when I try to change the link to the first workbook:
MyWorkbook.ChangeLink strOld1, strNew1, xlLinkTypeExcelLinks
the Excel (2002, SP3) application pops up a dialog, stopping the
program to wait for user interaction. The dialog is titled: "Update
Values:", and features the name of the second externally linked file.
That file will be updated by the next line in the program:
MyWorkbook.ChangeLink strOld2, strNew2, xlLinkTypeExcelLinks
Once I manually dismiss the Update Values dialog (and picking either OK
or Cancel works), the program runs to completion, updating all the
links to both external files. I have tried to suppress all file
dialogs by running these lines before the two ChangeLink lines above:
MyExcel.Visible = True
MyExcel.Workbooks.Add
MyExcel.AskToUpdateLinks = False
Set MyWorkbook = MyExcel.Workbooks.Open(strFile, False, False)
MyWorkbook.UpdateLinks = xlUpdateLinksNever
MyWorkbook.UpdateRemoteReferences = False
All of this results in the file opening with no prompts to update or
change links. It is only when the first ChangeLink line runs, the
program is stopped with the Update Values dialog. Swapping the order
of the two ChangeLink lines makes no difference, nor has changing the
DefaultFilePath to the same folder where both external files reside.
What am I missing?
with external links (in UNC form) to two other workbooks that are now
invalid. Opening one of these workbooks results in the dialog: "This
workbook contains one or more links that cannot be updated", with the
choices of Continue and Edit Links. Selecting Edit Links and browsing
to the required files (same location, just a different server name)
restores the links. The number of links in the workbook requires about
30 seconds to update.
I created a simple Visual Basic program that I'd hoped to let run
overnight, opening each workbook, changing the link paths, and saving.
It seemed very straightforward, but I have hit a snag.
Each workbook contains external links to two separate workbooks, but
when I try to change the link to the first workbook:
MyWorkbook.ChangeLink strOld1, strNew1, xlLinkTypeExcelLinks
the Excel (2002, SP3) application pops up a dialog, stopping the
program to wait for user interaction. The dialog is titled: "Update
Values:", and features the name of the second externally linked file.
That file will be updated by the next line in the program:
MyWorkbook.ChangeLink strOld2, strNew2, xlLinkTypeExcelLinks
Once I manually dismiss the Update Values dialog (and picking either OK
or Cancel works), the program runs to completion, updating all the
links to both external files. I have tried to suppress all file
dialogs by running these lines before the two ChangeLink lines above:
MyExcel.Visible = True
MyExcel.Workbooks.Add
MyExcel.AskToUpdateLinks = False
Set MyWorkbook = MyExcel.Workbooks.Open(strFile, False, False)
MyWorkbook.UpdateLinks = xlUpdateLinksNever
MyWorkbook.UpdateRemoteReferences = False
All of this results in the file opening with no prompts to update or
change links. It is only when the first ChangeLink line runs, the
program is stopped with the Update Values dialog. Swapping the order
of the two ChangeLink lines makes no difference, nor has changing the
DefaultFilePath to the same folder where both external files reside.
What am I missing?