none
Unexpected result of calling Excel Macro from Visual Basic Script (VBS) RRS feed

  • Question

  • Dear Sir/Madam,

    I need to call an Excel file's Macro procedure from Visual Basic Script (VBS).

    What the macro does is just to create a new worksheet and show message box before and after the action. Here's the code.

    Sub CreateNewWorksheetMacro()
        MsgBox ("Start")
        Sheets.Add After:=Sheets(Sheets.Count)
        MsgBox ("Finish")
    End Sub

    The Macro works perfectly fine if it's called by pressing the "Developer->Macros->CreateNewWorksheetMacro->Run" GUI button after opening Excel file.

    However, if the Macro is called by VBS code shown below, no new worksheet appears. The code just results opening of the  Excel file, show "Start" then "Finish" twice. Here's the code.

    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open("C:\test.xlsm")
    objExcel.Visible = True
    objExcel.Application.Run "CreateNewWorksheetMacro()"

    Please kindly advise why the Macro runs twice with no new worksheet created. How can I solve the problem ?

    Thank you so much.

    Regards,

    Mr. Humble Developer

    Friday, November 6, 2015 3:35 PM

Answers

  • The code's simple enough to demonstrate the issue that caused troublesome to me for nearly a day.

    Now I know the cause and wanna share the solution with you here.

    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open("C:\test.xlsm")
    objExcel.Visible = True
    objExcel.Application.Run "CreateNewWorksheetMacro"

    No bracket should be included in the underlined code at the time of calling Macro.

    Hope this post save some other's time with similar problem.

    Saturday, November 7, 2015 12:19 AM