CODEWITHSUNDEEP
excelvba
Dipal Shah
Dipal Shah

Reputation: 1

Run Excel Macro using VBS

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

Upvotes: 0

Views: 188

Answers (1)

Storax
Storax

Reputation: 12177

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")

Upvotes: 2

Related Questions