![[H]ard|Forum](/styles/hardforum/xenforo/logo_dark.png){kind=logo}
I need to run an Excel VBA Macro from a batch file fired off by Task Scheduler, and it creates a BUNCH of new spreadsheets.
I've got a really big nightly project that is split between a bunch of Virtual Machines across a couple hosts. Excel works best for formatting and producing PDF's. It's really easy to control page formatting in Excel and get it to look right every time. And most of the work is tabular and mathematical with conditional formatting- it would be much more difficult to use anything other than Excel for the report generation.
The virtual machines are all on PCIe SSD arrays, so I'm doing all of the processing on the local storage, and then copying the final work back to the file server.
And the idea is with a pre-produced PDF, load time is sub 1 second, so when my staff needs the report for a quick decision with the client on the phone, they don't wait at all for processing. Data 1 day current is sufficient for this report, so it doesn't need to be real-time anyway- but there is a ton of processing to build the report.
The Batch File:
The meat of the VBS Script also includes SMTP e-mail messaging to the IT Management group. I didn't include that here.
The nice thing is the .BAT file updates the VBS to the most current version, and the VBS updates the .BAT and .XLSM to the most current version, so it keeps it's self current without intervention.
But also by using a .BAT file set up with Task Scheduler, It's really easy to call all of the VM's from command line; and all of the Task XML files are the same and can be easily imported to new VM's, and you don't have to worry about getting the default scripting host right or anything like that- it just works:
Task XML Snippet:
Run ALL Batch file, mainly used for testing off-hours:
The VBS Code then gets the most current XLSM, does some messaging, creates some log files, and creates an Excel Object. The it opens the XLSM file and runs the Macro:
And then later on, the Excel Call part:
This then runs the RunDSR Macro in the .XLSM file. And I know it works because the log file writes all the way up to the first Workbook.Add call:
And then it stops.
The whole routine goes all the way thru to completion when I run the Batch file manually as Administrator.
What I have read is that when Application.Excel is opened via OLE Automation- none of the Add-ins are started with it. So, when Workbook.Add is called, the underlying reference is not there.
Long story, short question-
How to I initiate that reference to get this to work automatically?
This has been totally eating my lunch, so ask away- I need to get this working...
I've got a really big nightly project that is split between a bunch of Virtual Machines across a couple hosts. Excel works best for formatting and producing PDF's. It's really easy to control page formatting in Excel and get it to look right every time. And most of the work is tabular and mathematical with conditional formatting- it would be much more difficult to use anything other than Excel for the report generation.
The virtual machines are all on PCIe SSD arrays, so I'm doing all of the processing on the local storage, and then copying the final work back to the file server.
And the idea is with a pre-produced PDF, load time is sub 1 second, so when my staff needs the report for a quick decision with the client on the phone, they don't wait at all for processing. Data 1 day current is sufficient for this report, so it doesn't need to be real-time anyway- but there is a ton of processing to build the report.
The Batch File:
Code:
cd C:\DSR
ROBOCOPY "\\xxxxx005\System\Run_DSR.vbs" "C:\DSR\Run_DSR.vbs" /R:1 /W:1 /COPY:DATS
wscript.exe "C:\DSR\Run_DSR.vbs"
exitThe meat of the VBS Script also includes SMTP e-mail messaging to the IT Management group. I didn't include that here.
The nice thing is the .BAT file updates the VBS to the most current version, and the VBS updates the .BAT and .XLSM to the most current version, so it keeps it's self current without intervention.
But also by using a .BAT file set up with Task Scheduler, It's really easy to call all of the VM's from command line; and all of the Task XML files are the same and can be easily imported to new VM's, and you don't have to worry about getting the default scripting host right or anything like that- it just works:
Task XML Snippet:
Code:
<Actions Context="Author">
<Exec>
<Command>Run_VBS.bat</Command>
<WorkingDirectory>C:\DSR\</WorkingDirectory>
</Exec>
</Actions>Run ALL Batch file, mainly used for testing off-hours:
Code:
SCHTASKS /RUN /S STV10001 /TN \DSRTask
SCHTASKS /RUN /S STV20001 /TN \DSRTask
SCHTASKS /RUN /S STV10002 /TN \DSRTask
SCHTASKS /RUN /S STV20002 /TN \DSRTask
...The VBS Code then gets the most current XLSM, does some messaging, creates some log files, and creates an Excel Object. The it opens the XLSM file and runs the Macro:
Code:
Set wshShell = CreateObject("WScript.Shell")
' Run As Adminstrator
If WScript.Arguments.length = 0 Then
Set ObjShell = CreateObject("Shell.Application")
ObjShell.ShellExecute "wscript.exe", """" & _
WScript.ScriptFullName & """" &_
" RunAsAdministrator", , "runas", 1
Wscript.Quit
End if
Set obj6 = CreateObject("Scripting.FileSystemObject")
obj6.CopyFile "\\xxxxx005\System\Run_VBS.bat","C:\DSR\Run_VBS.bat", True
Set obj6 = Nothing
...
E-Mail Start Notification and file cleanup
Logfile creation
...And then later on, the Excel Call part:
Code:
Dim xlBook, ObjExcel
Set ObjExcel = CreateObject("Excel.Application")
' Open the Workbook specified on the command-line
Dim oWorkBook
Dim strWorkerWB
strWorkerWB = "C:\DSR\DSR.xlsm"
Set oWorkBook = ObjExcel.Workbooks.Open(strWorkerWB)
on error resume next
' Run the calculation macro
ObjExcel.Run "'C:\DSR\DSR.xlsm'!Module1.RunDSR"
if err.number <> 0 Then
' Error occurred - just close it down.
End If
err.clear
on error goto 0
' Clean up and shut downThis then runs the RunDSR Macro in the .XLSM file. And I know it works because the log file writes all the way up to the first Workbook.Add call:
Code:
Log File Started. - 4/11/2016 4:39:33 PM
SQL Import Routine Started. - 4/11/2016 4:39:33 PM
Filesave - 3.0999999580672E-03
Data Acquisition - 5651.42770000011
...And then it stops.
The whole routine goes all the way thru to completion when I run the Batch file manually as Administrator.
What I have read is that when Application.Excel is opened via OLE Automation- none of the Add-ins are started with it. So, when Workbook.Add is called, the underlying reference is not there.
Long story, short question-
How to I initiate that reference to get this to work automatically?
This has been totally eating my lunch, so ask away- I need to get this working...
