How to call VBA function from batch file

Question

I have a .bat file which executes some code after I enter a date in cmd:

test.bat contains:

 - set /P PWeekday=asofdate:

Since i always enter the previous weekday i wanted to write some code that will do just that. But having found it very difficult to do as part of a batch script, I read that it's much easier to create a macro in excel and pass values into the command line for the batch script to run. So i done just that:

PWeekday function in VBA

Public Function PWeekday() As String

Dim offset Day As Integer

If Weekday(Date) = 1 Then ' Monday

  offsetDay = 3

End If

PWeekDay = Format(Date - offsetDay, "YYYYMMDD")

End Function

Question:

What do I have to add to the batch file so that when i run it, it uses the Pweekday value from the vba function and not my user input?

Thank you very much, any help or advice will be appreciated.

Do you want to handle public/bank holidays or do you just want plain weekday? — Pankaj Jaju, Oct 27 '17 at 14:36
Plain weekdays will suffice and the vba snippet above does the job but i just don't have a clue how to incorporate that into the batch. Thanks — pirloe, Oct 27 '17 at 14:43
You might want to consider using VBScript instead of VBA. VBA is hosted in a, well, *host* application; you can't invoke any VBA without loading the host AFAIK. — Mathieu Guindon, Oct 27 '17 at 15:04
I'm not sure what you mean, could you perhaps advise on what that means for what i currently have above? do i have to create a .vbs file with my function and then specify the path of the file in my .bat file? Thanks — pirloe, Oct 27 '17 at 15:20

Pankaj Jaju · Accepted Answer · 2017-10-29T17:48:33.993

Although you could do all this in your bat file but I can see why you would want to do this in a hybrid script. So instead of using VBA, I have used VBScript to get the desired results

VBScript Code

Dim AsOfDate
If WeekDay(Date,2) = 1 Then 'WeekDay(Date,2) = 2 to ensure monday as start of week because default of vbs function is sunday
    AsOfDate=DateAdd("d",-3,Date)
Else
    AsOfDate=Date
End If
WScript.Echo DatePart("yyyy",AsOfDate,2) & DatePart("m",AsOfDate,2) & DatePart("d",AsOfDate,2) 'DatePart(xxx,AsOfDate,2) = 2 to ensure monday as start of week

Batch code

for /f "delims=" %%r in ('cscript //nologo C:\Users\pankaj.jaju\Desktop\Test.vbs') do set result=%%r
echo %result%

This is perfect, just what i was looking for - thank you! – pirloe Oct 30 '17 at 12:22 — pirloe, Oct 30 '17 at 12:22

score 2 · Answer 2 · answered Oct 27 '17 at 17:14

The calculation of the previous weekday is not "very difficult" to do as part of a batch script; it is just somewhat large:

@echo off
setlocal

REM Reference: http://www.hermetic.ch/cal_stud/jdn.htm#comp

rem Convert the Date in MM/DD/YYYY format to Julian Day Number and get the Day Of Week
for /F "tokens=1-3 delims=/" %%a in ("%date%") do (
   set /A "a=(%%a-14)/12, JDN=(1461*(%%c+4800+a))/4+(367*(%%a-2-12*a))/12-(3*((%%c+4900+a)/100))/4+%%b-32075, DOW=(JDN+1)%%7"
)
if %DOW% equ 1 set /A JDN-=3

rem Convert the Julian Day Number back to Date in YYYYMMDD format
set /A "l=JDN+68569,n=(4*l)/146097,l=l-(146097*n+3)/4,i=(4000*(l+1))/1461001,l=l-(1461*i)/4+31,j=(80*l)/2447"
set /A "dd=100+l-(2447*j)/80,l=j/11,mm=100+j+2-(12*l),yyyy=100*(n-49)+i+l"
set "PWeekDay=%yyyy%%mm:~1%%dd:~1%"
echo %PWeekDay%

This is a simple method that works correctly when the format of %date% variable is MM/DD/YYYY. If your date format is different, just change the %%a and %%b values in set /A expression accordingly. If you want the program run with any date format, it may be slightly modified to get the date from wmic command.

NOTE: I am afraid I am confused by the formula used in this example. IMHO if the current day is not monday, one day should be subtracted from the current day; otherwise the day used is the same day, not the previous one:

if %DOW% equ 1 (set /A JDN-=3) else set /A JDN-=1

score 0 · Answer 3 · answered Oct 27 '17 at 16:12

I would agree that VBA is often an easier tool for building these kinds of custom functions, but like others have pointed out, it requires the overhead of Excel or another VBA program to run the function. This is possible, but might be overkill for your underlying need in this case.

Although VBScript lacks the nice Format() function of VBA, you can write something very similar that will run in VBScript, and use the Echo function to output the return back to your batch file. You will see that I had to change very little in your function to run it in VBScript.

Dim offsetDay
Dim returnDate

If Weekday(Date) = 1 Then ' Monday

  offsetDay = 3

End If

returnDate = Date - offsetDay

' Build return format with leading zeros
WScript.Echo DatePart("yyyy", returnDate) & _
    Right("0" & DatePart("m", returnDate),2) & _
    Right("0" & DatePart("d", returnDate),2)

To test it, you can double-click the VBScript file and see the return in a message box. When you call it from your batch file, it will output the result into your batch program.

Here are a couple links to get you started on calling the VBScript from your batch file: pass value from vbscript to batch and Pass variable from a vbscript to batch file

Hope that helps!!

Great explanation, got me going in the right direction thank you — pirloe, Oct 30 '17 at 12:19

score 0 · Answer 4 · answered Oct 27 '17 at 19:36

Here is another way to do it using PowerShell. This also handles both Sunday and Monday by using -$weekday - 2 as the offset.

powershell -NoProfile -Command "$weekday = (Get-Date).DayOfWeek.value__;" ^
    "$offset = -1;" ^
    "if ($weekday -lt 2) { $offset = -$weekday - 2 };" ^
    "((Get-Date).AddDays($offset)).ToString('yyyyMMdd');" >y.txt
SET /P "PWeekday=" <y.txt

How to call VBA function from batch file

4 Answers4