Using VBScript to run Excel macro

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 " & & " " & now
	filename =
	Set excelBook = excelApp.Workbooks.Open(excelFolder & "\" & filename)

	If excelBook.ReadOnly Then
		Wscript.Echo "Readonly mode detected, skipping"
		excelApp.Run "FETCH_LATEST_DATA"
	End if

	Wscript.Echo "Ending " & & " " & now
	excelBook.Close False
	set excelBook = Nothing

Set excelApp = Nothing
set objFso = nothing

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.



Calculating time difference in Excel

For the purpose of the demonstration, in an Excel spreadsheet, let’s say we have a date-time value of “9/13/14 3:02 PM” in cell A2 and “9/13/14 5:43 PM” in cell B2. Even though the screen displays them in a format friendly to me at my location in the United States, in the background Excel actually treats them as a decimal number that is universal regardless of the display formatting. In this case the two values are actually 41895.63
and 41895.74, respectively; the details on this number is outside the scope of this particular article. Getting back on track, in order to calculate the number of days, hours, or minutes that has elapsed between these two values, we can simply use these formulas:

Difference in days:    =(B2-A2)
Difference in hours:   =(B2-A2)*24
Difference in seconds: =(B2-A2)*24*60

Prevent Excel from recognizing a string as a number when opening CSV files

I often use CSV format to transport data, largely because its text nature makes the data extremely portable across different platforms and applications. Yesterday when a colleague opened a CSV file from me using Excel, one of the fields I provided was displayed incorrectly. Two examples of the troubles Excel are listed below.

Value Provided in CSV Shown by Excel
0E36 0
0055 55

In the first case, Excel thought the value was a scientific notion; in the second case, Excel treated it as a number. In both cases, they were both wrong.

This issue also happens frequently when displaying ZIP codes for addresses in the United States, eg. Excel showing the intended “04001” as “4001”.

Because this is an Excel-specific behavior, I consider the following fix to be a work-around rather than a permanent solution; nevertheless, it works well for me. Instead of a simple CSV format as I had originally:


I inserted an equal sign in front of it. This forces Excel to think that I am writing a formula that outputs a string, thus solving the Excel issue.