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:
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: