Calling a VBA macro from bat file

Question

I have a vba macro to un-merge the merged cells and repeat in excel, but i need to run this macro without opening the excel and also the same logic will be applied to multiple excels so i need a batch file which calls the vba and apply to the excel in a folder. Is there any way to do it.?

Possible duplicate of Way to run Excel macros from command line or batch file? — Robin Mackenzie
so what you are saying is that the xlsx should have a macro in your case "MyMacro", now when you call the batch file it will open the xlsx and run the macro and then close it, is that what you are saying? — Maharaj
Yes. You can write a macro that will open other Excel workbooks in your folder to apply the un-merge process and then apply the technique. If your question is 'how do I un-merge a cell in Workbook B from a macro running in Workbook A' then you might want to re-word your question. — Robin Mackenzie
is there any possibility that i could put the vba script for unmerge and duplicate directly in a batch script and passing the name of the excel to it, if that is too tedious i might go with your solution and change the title. — Maharaj

Jbjstam Jbjstam · Accepted Answer · 2016-10-04T15:33:42

You can call this VBscript from a batch file (in a loop)

Usage:

CScript inject.vbs "C:\johan\VbaStuff\Empty workbook.xlsx" "C: \johan\VbaStuff\DummyMacro.bas" "AddDateToA1"

inject.vbs:

dim oArgs, i, FSO
dim fileName, macroFileName, functionName
dim oApplication, oWorkbooks, oWorkbook, oVbProject
dim oComponents, oModule, fullFunction

Set oArgs = WScript.Arguments
For i = 0 to oArgs.Count - 1
   WScript.Echo "Arg" & i & ": " & oArgs(i)
Next
WScript.Echo ""

if oArgs.Count < 3 then
    WScript.Echo "Too few arguments"
    WScript.Quit
end if

fileName = oArgs(0)
macroFileName = oArgs(1)
functionName = oArgs(2)

set FSO = CreateObject("Scripting.FileSystemObject")

if not FSO.FileExists(fileName) then
    WScript.Echo "Excel file does not exist"
    WScript.Quit
end if

if not FSO.FileExists(macroFileName) then
    WScript.Echo "Macro file does not exist"
    WScript.Quit
end if

set oApplication = CreateObject("Excel.Application")

oApplication.DisplayAlerts = False

WScript.Echo "Injecting in Excel version: " & oApplication.Version

set oWorkbooks = oApplication.Workbooks
set oWorkbook = oWorkbooks.Open(fileName)
WScript.Echo oWorkbook.Name

set oVbProject = oWorkbook.VBProject
WScript.Echo oVbProject.Name

set oComponents = oVbProject.VBComponents
set oModule = oComponents.Import(macroFileName)

fullFunction = Trim(oModule.Name & "." & functionName)
WScript.Echo "Full function: '" & fullFunction & "'"
oApplication.Run(fullFunction)
oComponents.Remove(oModule)

oWorkbook.Save()
WScript.Echo "Saved changes"
oWorkbook.Close(False)

Call oApplication.Quit()

set oModule = Nothing
set oComponents = Nothing
set oVbProject = Nothing
set oWorkbook = Nothing
set oWorkbooks = Nothing
set oApplication = Nothing
set FSO = Nothing

Dummy macro for completeness:

Attribute VB_Name = "DummyMacro"
Sub AddDateToA1()
    Dim oCell As Range
    Dim sTime As String

    Set oCell = ThisWorkbook.Sheets(1).Cells(1, 1)
    sTime = CStr(TimeValue(Now))

    oCell.Value = sTime
    MsgBox "Wrote time " & sTime & " to " & oCell.Address(0, 0, xlA1, 1)
End Sub

Calling a VBA macro from bat file

1 Answers