Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation problem
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 c++ automation problem. | Excel Worksheet Functions | |||
Automation problem saving Excel as HTML. | Excel Programming | |||
Excel Automation Problem With Add-Ins | Excel Programming | |||
Problem in graph automation in excel. | Excel Programming | |||
TypeConverter Excel-Automation problem | Excel Programming |