Excel automation problem
 

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!
--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel automation problem
 

Hello
objXLBook.RefreshAll

HTH
Cordially
Pascal

"Arvi Laanemets" a écrit dans le message de news:
...
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!
--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel automation problem
 

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" wrote in message
...
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!
--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel automation problem
 

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).



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )