Download SAP Report in Spreadsheet Format with automatic Save As selection

[Former Member]

Hi All,

I'm using an Excel sheet that links up to SAP to grab some data off of QE03. Due to the nature of my data, I have to save the data into Excel/Spreadsheet format (aka this button )

BUT, when you click that button it stops the script for a "Save As" screen, since it isn't a part of SAP. Since I have a macro immediately after the save that cuts the data, I need this to be fully automated and uninterrupted.

Here is the script I have in excel:

Dim SapGuiAuto
Dim Apps
Dim Connection
Dim session
Dim WScript

Sub Master()

        With Sheets("Graphs")
        Call SAP
        Call QE03

        End With

End Sub


Sub SAP()


If Not IsObject(Apps) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set Apps = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = Apps.Children(0)
End If
If Not IsObject(session) Then
   Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject Application, "on"
End If


End Sub


Sub QE03()


session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nqe03"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 4
session.findById("wnd[1]/usr/tabsG_SELONETABSTRIP/tabpTAB005/ssubSUBSCR_PRESEL:SAPLSDH4:0220/sub:SAPLSDH4:0220/ctxtG_SELFLD_TAB-LOW[1,24]").Text = "123456"
session.findById("wnd[1]/usr/tabsG_SELONETABSTRIP/tabpTAB005/ssubSUBSCR_PRESEL:SAPLSDH4:0220/sub:SAPLSDH4:0220/ctxtG_SELFLD_TAB-LOW[1,24]").SetFocus
session.findById("wnd[1]/usr/tabsG_SELONETABSTRIP/tabpTAB005/ssubSUBSCR_PRESEL:SAPLSDH4:0220/sub:SAPLSDH4:0220/ctxtG_SELFLD_TAB-LOW[1,24]").caretPosition = 8
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[1]/usr/lbl[1,1]").SetFocus
session.findById("wnd[1]/usr/lbl[1,1]").caretPosition = 8
session.findById("wnd[1]").sendVKey 2
session.findById("wnd[1]/usr/lbl[1,3]").SetFocus
session.findById("wnd[1]/usr/lbl[1,3]").caretPosition = 7
session.findById("wnd[1]").sendVKey 2
session.findById("wnd[0]/usr/ctxtQAQEE-VORNR").Text = "1234"
session.findById("wnd[0]/usr/ctxtQAQEE-VORNR").SetFocus
session.findById("wnd[0]/usr/ctxtQAQEE-VORNR").caretPosition = 4
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/mbar/menu[1]/menu[0]/menu[0]").Select
session.findById("wnd[0]/tbar[1]/btn[40]").press
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[1]/tbar[0]/btn[37]").press


End Sub

I get stuck right at the last line, "session.findById("wnd[1]/tbar[0]/btn[37]").press" which opens the Save As dialog box. I've seen some similar topics around here, but I am honestly not too savvy at scripting and this is an Excel specific model, so does anyone have any advice?

[Former Member]

Hi,

Why are you trying to use  that button?

It's much easier to use simple its called "Local file" u can grab it by shortcut CTRL+SHIFT+f9...

Another option is to use SAP functions:: put "%PC" to command field then enter, and you will get the window with option to save data in excel.

Please find below some examples to download data to excel:

'For STANDARD T-CODES like MB52

'-----------------------------------------------------------------------------------------------

'export to excel button

Session.FindById("wnd[0]/tbar[1]/btn[45]").Press

Session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select

Session.FindById("wnd[1]/tbar[0]/btn[0]").Press

Uname = CreateObject("WScript.Network").UserName

Session.FindById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\" & uname & "\Desktop\"

Session.FindById("wnd[1]/usr/ctxtDY_FILENAME").Text = "TEMP.xls"

'rewrite

Session.FindById("wnd[1]/tbar[0]/btn[11]").Press

OPTION WITH "%PC"

'-----------------------------------------------------------------------------------------------

'save to TEMP

session.findById("wnd[0]/tbar[0]/okcd").Text = "%pc"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select

session.findById("wnd[1]/tbar[0]/btn[0]").press

session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\" & Uname & "\Desktop\"

session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "TEMP.xls"

session.findById("wnd[1]/tbar[0]/btn[11]").press

Best,

[Former Member]

Hi Tomasz,

Thanks for your response. The reason I don't use the "Local File" shortcut is because it will not work with this T-Code. In QE03, the local file export looks at single values for the inspection lots - but the parts I'm working with are one part per order, so there are no "single values" to evaluate. Hence, why I need the other icon unfortunately.

Here's the screen I'm looking at to export the data. It's a view of the individual characteristic, then clicking "Results History" to pop this up.

When I tried your standard-t-codes option, Session.FindById("wnd[0]/tbar[1]/btn[45]").Press is the equivalent of checking single values, which wont' work for my data. A script recording shows that the button I need to export is scripted as session.FindById("wnd[1]/tbar[0]/btn[37]").Press

When I tried your %PC option, it says "Function code cannot be selected"

I hope this helps clarify the problem to find a solution...