VBA - SAP 'Save As' dialogue box

[marc81]

Hi All,


I'm new to the forum and pretty new to VBA so be easy with me


I'm currently trying to write a script that will run a transaction in SAP, and download the output to a specific location. There are 2 ways to download the output, one through the menu structure in SAP which I can code as SAP records the macro, but the one I need involves clicking on a 'download to excel' button that in turn opens up a 'save as' box, for some reason SAP doesn't record this action as a macro. I can get VBA to get to the point of opening up the dialogue box but I can't get it to fill in the filename and then hit save (it doesn't seem to recognise that the save as box is open). Any advice? My current script is below;


------------------------------------------------------------------------



Sub SAPTest()

If Not IsObject(App) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set App = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = App.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 App, "on"
End If

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "ZL18"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtS_SHIPP").Text = ThisWorkbook.Sheets("Sheet1").Range("C4").Value
session.findById("wnd[0]/usr/ctxtS_DDAT").Text = ThisWorkbook.Sheets("Sheet1").Range("D2").Value
session.findById("wnd[0]/usr/ctxtS_ROUTE-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtS_ROUTE-LOW").caretPosition = 0
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[34]").press

End Sub

[D_Marcel]

Hi marc81, be welcome to the forum!
I came across the very same situation when I was trying to automate some Excel reports build with SAP data in my former company. The bad new is that the SAP Script Recorder do not record this action indeed, as this window is not a SAP window, but a "Windows window", got it?
Unfortunately I do not work with SAP anymore at my current job, but I could workaround this situation calling this screen:

20022

Many SAP transactions has this screen as an option to export your results, and you can access it in the toolbar. This one is SAP screen and you can set the directory and the file, as well as the file extension.

Hope you can perform your task as best as possible.

Regards,

Douglas

[marc81]

Hi marc81, be welcome to the forum!
I came across the very same situation when I was trying to automate some Excel reports build with SAP data in my former company. The bad new is that the SAP Script Recorder do not record this action indeed, as this window is not a SAP window, but a "Windows window", got it?
Unfortunately I do not work with SAP anymore at my current job, but I could workaround this situation calling this screen:

20022

Many SAP transactions has this screen as an option to export your results, and you can access it in the toolbar. This one is SAP screen and you can set the directory and the file, as well as the file extension.

Hope you can perform your task as best as possible.

Regards,

Douglas

Douglas,

Many thanks for your reply. I can indeed download it through the toolbar as suggested, the problem is that it only saves down the on screen version of the report this way, whereas if I use the 'download to excel' button the data is broken down to a lower level if that makes sense. I need to find a way of populating the 'windows window' as you rightfully put it :) I just can't find a way :(

Marc