How to refresh a Excel workbook with pivot & ADO query report in batch mode

[Karen Middleton]

I have a Excel report which as a report populated from a ADO VBA code
in a VBA subroutine which is driven from a button on the Excel
workbook which the user presses to refresh.

Also, I have some pivot reports on this workbook which is refreshed
from a VBA subroutine called from a Excel button.

Our users want this report to be refreshed in batch meaning they want
the report to be refreshed overnight and saved on the network share
that they can lookup the next morning without refreshing.

I am wondering if the workbook is called TestReport.xls and it as a
subroutine called RefreshAllReports please advise how to call this
Subroutine - RefreshAllReports in the Excel workbook - TestReport.XLS
from a VBScript from the operating system.

Please advise how I can implement this kind of scenario appreciate if
you can share the code to call a subroutine in a Excel workbook from a
VBScript.

Thanks
Karen

 

[Tom Ogilvy]

objExcel.Run "TestReport.XLS!RefreshAllReports"

General information on automating Excel is in these articles:

http://support.microsoft.com/support/OfficeDev/FaqVBOffice.asp
Frequently Asked Questions about Microsoft Office Automation Using Visual
Basic


http://support.microsoft.com/support/OfficeDev/offdevinapps.asp
Programming Office from Within Office

http://support.microsoft.com/support/officedev/iisfaq.asp
Integrating Office with IIS FAQ

http://support.microsoft.com/?id=219151
Q219151 - HOWTO: Automate Excel 97 and Excel 2000 from Visual Basic

http://support.microsoft.com/support/excel/content/Automation/automation.asp
Using Automation with Microsoft Excel 97

http://support.microsoft.com/?id=170745
XL97: How to Create PivotTable from Word

http://support.microsoft.com/?id=177760
VBA: How to Run Macros in Other Office Programs <=====****

http://support.microsoft.com/?id=153748
ACC: How to Call Excel Functions from Within Microsoft Access

http://support.microsoft.com/?id=167223
Microsoft Office 97 Automation Help File Available on MSL