Excel automation problem

[Arvi Laanemets]

Hi

I want open an Excel workbook through vbscript from below. The workbook
contains 4 ODBC queries, which are refreshed on open. After some time, the
workbook is saved and closed. It all works OK, but is there a way to get rid
of message "This workbook contains queries to external data, that refresh
automatically. ....", with choices enable/disable refresh. Of course I can
check 'Enable automatic query refresh for all workbooks ...', but I want the
automatic refresh run without asking for this workbook only! Is there some
way to enforce this from vbscript - using some parameters for Getobject()
maybe? I don't want to have any code in workbook at all, as then I have to
cope with macro warning, and I don't want to lower the security level - so
an Open event is not an option - then I better set all workbooks to be
refreshed automatically.

********
Option Explicit

Dim objXLApp, objXLBook, strPath, strBook

strPath = "Drive:\Path\"
strBook = "MyWorkbook.xls"
Set objXLBook=GetObject(strPath & strBook)
Set objXLApp = objXLBook.Parent
objXLApp.Visible = True
objXLBook.Windows(1).Visible = True
WScript.Sleep(10000)
objXLApp.DisplayAlerts=False
objXLApp.ActiveWorkbook.SaveAs(strPath & strBook)
objXLApp.DisplayAlerts=True

objXLApp.Quit
*********


Thanks in advance!

 

[papou]

Hello
objXLBook.RefreshAll

HTH
Cordially
Pascal

 

[NickHK]

Arvi,
Apart from the fact that you are not opening this workbook, but getting a
reference to it after...
Using:
Set WB = Application.Workbooks.Open("MyFile.xls")
with Excel 2K, this dialog is not shown anyway.
If you to refresh each one or not depends on your requirement.
Dim QT as QueryTable
For each QT In WB.QueryTables
QT.refresh
....

NickHK

 

[Arvi Laanemets]

Thanks
The final version of script:


Option Explicit

Dim objXLApp, objXLBook, strPath, strBook

strPath = "Drive:\Path\"
strBook = "MyWorkbook.xls"
Set objXLBook=GetObject(strPath & strBook)
Set objXLApp = objXLBook.Parent
objXLApp.Visible = True
objXLBook.Windows(1).Visible = True
objXLBook.Sheets("Sheet1").QueryTables(1).Refresh
objXLBook.Sheets("Sheet2").QueryTables(1).Refresh
objXLBook.Sheets("Sheet3").QueryTables(1).Refresh
objXLBook.Sheets("Sheet4").QueryTables(1).Refresh
WScript.Sleep(10000)
objXLApp.DisplayAlerts=False
objXLApp.ActiveWorkbook.SaveAs(strPath & strBook)
objXLApp.DisplayAlerts=True

objXLApp.Quit


This script will run at every night on server. The workbook MyWorkbook.xls
resides on shared network resource, and users use 4 tables in it as a
datasource for various ODBC queries (original data location isn't accessible
for most of users).