Run Excel Macro using VBS

Question

Trying to run excel macro using command prompt through VBS. Excel file open ups but not able to run macro. The name of Macro as well as module is "Wallet"

While tryig to view macro, it reflects as "PERSONAL.XLSB!WALLET"

Pls help

Sub ExcelMacroExample()

Dim xlApp
Dim xlBook

Set xlApp = CreateObject("excel.application")
Set xlBook = xlApp.Workbooks.Open("C:\Output\abc.xlsb")
xlApp.Visible = True
xlApp.Run "Wallet"
xlApp.Save
xlApp.ActiveWorkbook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

End Sub

Try changing line xlApp.Run "Wallet" to xlApp.Run "PERSONAL.XLSB!WALLET" and then running the code. — shrivallabha.redij
– shrivallabha.redij, Commented May 10, 2020 at 12:50
I just realized that my personal.xlsb is not available in the excel i am trying to run macro. Is there any way I can make this available....? — Dipal Shah
– Dipal Shah, Commented May 10, 2020 at 14:08

Storax · Accepted Answer · 2020-05-10 14:25:42Z

2

Change your code like that

Sub ExcelMacroExample()

    Dim xlApp
    Dim xlBook

    Set xlApp = CreateObject("excel.application")
    Set xlBook = xlApp.Workbooks.Open("C:\Output\abc.xlsb")
    xlApp.Visible = True
    xlApp.Workbooks.Open ("Path to your Personal.XLSB")
    xlApp.Run "Personal.XLSB!Macro"

    'xlApp.Save                   <= This line would not work, xlApp does not have a Save Method
    'xlApp.ActiveWorkbook.Close   <= You already have the reference to the workbook. It is xlBook
    xlBook.Close True           ' <= This will save and close the workbook you opened
    xlApp.Quit

    Set xlBook = Nothing
    Set xlApp = Nothing

End Sub

If you do not know where your Personal.xlsb is try the standard location.

xlApp.Workbooks.Open (xlApp.StartupPath & xlApp.PathSeparator & "Personal.XLSB")

edited May 10, 2020 at 14:25

answered May 10, 2020 at 14:16

Storax

12.7k3 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Run Excel Macro using VBS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related