PDA

View Full Version : Solved: How to handle "automatic links" alert when driving Excel with VBScript?



Sukotto
04-26-2007, 06:59 AM
I am writing Excel driver scripts to automate open/button press/save/close of spreadsheets. I'm doing this in classic vbscript. We schedule these to run overnight without human supervision.

Recently I ran into a workbook that gives the following alert when opening

The workbook you opened contains automatic links to information in
another workbook. Do you want to update this workbook with changes
made to the other workbook?

* To update all linked information, click Yes.
* To keep the existing information, click No.

How can my automation script instruct Excel to NOT update the links (as if I clicked "No")? (There's quite a lot of information out there about finding and removing links.... but I don't want to do that.)

I can disable the alert ExcelObj.AskToUpdateLinks = false but then Excel acts like I clicked "yes".

I have code similar to the following.
Dim ExcelObj
Dim WorkBookObj
Set ExcelObj = CreateObject("EXCEL.APPLICATION")

'disable "Automatic links" dialog
ExcelObj.AskToUpdateLinks = False

'set automatic links to false
'<<<<<<<<<<<<<<<<<<<<<<<<<<< HOW?

Set WorkBookObj = ExcelObj.Workbooks.Open("myBook.xls")

'disable auto-calc
ExcelObj.Calculation = -4135

'Do work
ExcelObj.Worksheets("mySheet").myFunction()
WorkBookObj.save
WorkBookObj.close(false)

'Clean up
ExcelObj.quit
Set WorkBookObj = nothing
Set ExcelObj = nothing


Please help me. This is driving me nuts! :banghead:

Sukotto
04-26-2007, 07:17 AM
Figures I'd figure it out right after posting :doh:

Change
Set WorkBookObj = ExcelObj.Workbooks.Open("myBook.xls")
To
Set WorkBookObj = ExcelObj.Workbooks.Open("myBook.xls",0)

The Open method takes a parameter "UpdateLinks" that controls updating...



UpdateLinks Optional Variant. Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.

Value Meaning
0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references


I hope this post will save someone else some pain in the future.

lucas
04-26-2007, 07:33 AM
I hope this post will save someone else some pain in the future
Thanks for posting your solution......good to know.