Category: Excel

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.

C:\Windows\System32\config\systemprofile\Desktop

C:\Windows\SysWOW64\config\systemprofile\Desktop