Running from CMD and having problems to use SaveAs

Question

I am able to connect to our SAP BW (Business Warehouse) which is on a server, to open specific queries but I am unable to Save it in the end. I tried to search on various forums, but they all use the same logic as I do. I am using CMD to run this script.

Mine on the other hand throws an error which is:

Object doesn't support this property or method: 'SaveAs'
Code: 800A01B6

' Setup the global variables
Dim xl
Dim myConnection
Dim xlBook
 
' Launch Excel
Set xl = CreateObject("Excel.Application")
 
' Make it visible otherwise things just don't work well
xl.Visible = True
     
' Now that Excel is open, open the BEX Analyzer Addin xla file
xl.Workbooks.Open ("C:\Program Files (x86)\Common Files\SAP Shared\BW\BExAnalyzer.xla")

' Run the SetStart macro that comes with BEX so it pays attention to you
xl.Run ("BExAnalyzer.xla!SetStart")
 
' Logon directly to BW using the sapBEXgetConnection macro
Set myConnection = xl.Run("BExAnalyzer.xla!sapBEXgetConnection")
With myConnection
    .client = "100"
    .User = "user"
    .Password = "pass"
    .Language = "EN"
    .systemnumber = 00
    .ApplicationServer = "xxxyyy"
    .SAProuter = "xxxyyyyy"
    .Logon 0, True
End With

' Now initialize the connection to make it actually usable
xl.Run ("BExAnalyzer.xla!sapBEXinitConnection")

' Open query
xl.Run "BExAnalyzer.xla!runQuery","00O2TPBVULP4LPJ4LBZRIFQN3" 

' Initiate saving, turn off showing allerts
Application.DisplayAlerts = False
xl.SaveAs "C:\Output\NAME.xlsm"
'xl.SaveAs "C:\Output\NAME", 52 ' 52 should be the format for XLSM
Application.DisplayAlerts = True

'' Close Excel
'xl.Quit

I would like to save the XLSM to a specific folder.

score 1 · Answer 1 · answered Apr 05 '19 at 09:19

Your xl object refers to the Application object. SaveAs is a member of the Workbook object. Set a reference to the workbook and use that to do the save action

' Setup the global variables
Dim xl
Dim myConnection
Dim xlBook

' Launch Excel
Set xl = CreateObject("Excel.Application")

' Make it visible otherwise things just don't work well
xl.Visible = True

' Now that Excel is open, open the BEX Analyzer Addin xla file
Set xlBook = xl.Workbooks.Open ("C:\Program Files (x86)\Common Files\SAP Shared\BW\BExAnalyzer.xla")

' Run the SetStart macro that comes with BEX so it pays attention to you
xl.Run ("BExAnalyzer.xla!SetStart")

' Logon directly to BW using the sapBEXgetConnection macro
Set myConnection = xl.Run("BExAnalyzer.xla!sapBEXgetConnection")
With myConnection
    .client = "100"
    .User = "user"
    .Password = "pass"
    .Language = "EN"
    .systemnumber = 00
    .ApplicationServer = "xxxyyy"
    .SAProuter = "xxxyyyyy"
    .Logon 0, True
End With


' Now initialize the connection to make it actually usable
xl.Run ("BExAnalyzer.xla!sapBEXinitConnection")

' Open query
xl.Run "BExAnalyzer.xla!runQuery","00O2TPBVULP4LPJ4LBZRIFQN3" 

' Initiate saving, turn off showing allerts
Application.DisplayAlerts = False
xlBook.SaveAs "C:\Output\NAME.xlsm"
'xl.SaveAs "C:\Output\NAME", 52 ' 52 should be the format for XLSM
Application.DisplayAlerts = True


'' Close Excel
'xl.Quit

Running from CMD and having problems to use SaveAs

1 Answers1