Calling specific module in Vba from batch script

Question

I am having three module in vba i.e Module1,Module2,Module3 I need to call a Main() method in Module2 from Batch script/from command line

I used the command start excel.exe /e "C:\temp\RelayRec.xlsm" After opening excel,Module1 Main() method will be executed. Now I want to be more specific calling to call Module2 Main() method from command line after removing link from Module1 Main() method.

Can anyone help me out please? Thanks in advance.

As of now I can run Module1 Main() method after opening RelayRec.xlsm worksheet by using 'Workbook_Open()' function.Now I need to remove Main() of Module1 from Workbook_Open() and have to call Main() method of Module2 from Batch Script itself. — user2582367, Jul 18 '14 at 12:56

score 1 · Answer 1 · edited May 23 '17 at 11:57

1

Use a VBS script to open an instance of excel and then you can load the file and run the macro/function you're interested in. This should offer you all the control you need from what you have described.

Some links on SO with answers

https://stackoverflow.com/a/2056066/2448686

https://stackoverflow.com/a/10894162/2448686

edited May 23 '17 at 11:57

Community

1
1

answered Jul 18 '14 at 13:33

ashareef

1,846
13
19

Thank you,I have to move from Macro to Batch Script and then to Macro,This is my requirement.I tried using Vbs it helped,but I am not supposed to use Vbs. – user2582367 Jul 22 '14 at 12:01

score 0 · Answer 2 · answered Jul 18 '14 at 13:39

One option is to use a command line parameter to specify which module Main method to call. I found some code to retrieve the command line here.

Create a new module and add this code:

Option Base 0
Option Explicit

Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)

Function CmdToSTr(Cmd As Long) As String
  Dim Buffer() As Byte
  Dim StrLen As Long

  If Cmd Then
    StrLen = lstrlenW(Cmd) * 2
    If StrLen Then
      ReDim Buffer(0 To (StrLen - 1)) As Byte
      CopyMemory Buffer(0), ByVal Cmd, StrLen
      CmdToSTr = Buffer
    End If
  End If
End Function

In you Workbook_Open method, retrieve the command line and check the parameters:

Private Sub Workbook_Open()
  Dim CmdRaw As Long
  Dim CmdLine As String

  CmdRaw = GetCommandLine
  CmdLine = CmdToSTr(CmdRaw)

  If InStr(CmdLine, "Main2") > 0 Then
    Module2.Main
  Else
    Module1.Main
  End If
End Sub

In you batch file, run Excel like this:

EXCEL.EXE C:\MyFolder\MyWorkbook.xlsm /e /Main2

Thank you,I am getting error of 'file couldn't be found' saying C:\MyFolder\MyWorkbook.xlsm /e /Main2 does not exist. — user2582367, Jul 22 '14 at 11:59
You will need to replace `C:\MyFolder\MyWorkbook.xlsm` with your actual path and file name. — aphoria, Jul 22 '14 at 14:07

Calling specific module in Vba from batch script

2 Answers2