I have a folder containing several Excel files, each containing a macro named “FETCH_LATEST_DATA”. The following steps will detail how I had scheduled a VBScript to run these Excel macros nightly.
First, I created the VBScript below that opens each Excel file successively and calls the particular macro. Note that I have disabled alerts and events since I intended to run it as an unattended job, and that I am using “Wscript.Echo” to output some start/end info for debugging purposes should something go wrong in the future.
excelFolder = "\\fileserver\share\folder" Set objFso = CreateObject("Scripting.FileSystemObject") Set objFolder = objFso.GetFolder(excelFolder) Set excelApp = CreateObject("Excel.Application") excelApp.DisplayAlerts = False excelApp.EnableEvents = False for each objFile In objFso.GetFolder(excelFolder).files Wscript.Echo "Starting " & objFile.name & " " & now filename = objFile.name Set excelBook = excelApp.Workbooks.Open(excelFolder & "\" & filename) If excelBook.ReadOnly Then Wscript.Echo "Readonly mode detected, skipping" Else excelApp.Run "FETCH_LATEST_DATA" excelBook.Save End if Wscript.Echo "Ending " & objFile.name & " " & now excelBook.Close False set excelBook = Nothing Next excelApp.Quit Set excelApp = Nothing set objFso = nothing WScript.Quit
Next, I inserted a line in my already-existing batch file that contains all the other jobs that I typically run on this Windows server every evening. Note that I am using “>>” to direct any console output (see “Wscript.Echo” above) to a log file should there be any debugging needs.
[...] C:\windows\syswow64\cscript.exe C:\scripts\update_excel.vbs >> update_excel.log [...]
Before we can run Excel unattended, we must create these two folders. If these two folders are missing, the scheduled job would sit perpetually in Windows Task Scheduler as if running, but it would never actually complete; in the way that the logging info is setup above, you would see something like “Starting file1.xlsm 6/20/2019 10:15:00 AM” without any additional lines being logged. If your Windows server is running in 32-bit mode, you can ignore the second folder.
One Reply to “Using VBScript to run Excel macro”
hey Peter, thanks for this highly efficient piece of code.