Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChangeLinks routine stopped by Update Values dialog
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChangeLinks routine stopped by Update Values dialog
Try...
MyExcel.DisplayAlerts = False 'your changelink code MyExcel.DisplayAlerts = True -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware wrote in message -snip- 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 -snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Values Dialog | Excel Programming | |||
fastest sorting routine for 2-D array of long values | Excel Programming | |||
Trapping Erros on ChangeLinks | Excel Programming | |||
Update dialog box | Excel Worksheet Functions | |||
how to skip Update Values dialog when links change tonon-existent spreadsheets? | Excel Programming |