Macro to download SAP report to Excel

[texasguy66]

Hello everyone,

The macro below is used to run a SAP report, and it works up to the point of saving the file in excel. SAP has a pop up "Save As" and I am unable to get the macro to copy the location and filename into the pop up. Have spent the past few days searching online but have not found a working solution next. Attached is a screen shot of the pop up window. Does anyone know how I can get this macro to work correctly?

Option Explicit

Dim ws As Worksheet
Dim sh As Worksheet
Private SapGuiAuto, WScript, msgcol
Private objGui As GuiApplication
Private objConn As GuiConnection
Private session As GuiSession
'Declare Variable Above

Sub SD07download()

Dim dts As String
Dim i As Integer
Dim response As VbMsgBoxResult
Dim bwindowfound As Boolean

Set SapGuiAuto = GetObject("SAPGUI")
Set objGui = SapGuiAuto.GetScriptingEngine
Set objConn = objGui.Children(0)
Set session = objConn.Children(0)
Set ws = ThisWorkbook.Sheets("Sheet1")
Set WScript = CreateObject("WScript.Shell")

dts = Format(Now, "mm-dd-yyyy- hh-mm-ss")

Application.DisplayAlerts = False

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/n/sd07"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtS_FKDAT-LOW").Text = "05/01/2023"
session.findById("wnd[0]/usr/ctxtS_FKDAT-HIGH").Text = "05/05/2023"
session.findById("wnd[0]/usr/ctxtP_VKORG").Text = "trus"
session.findById("wnd[0]/usr/ctxtP_VKORG").SetFocus
session.findById("wnd[0]/usr/ctxtP_VKORG").caretPosition = 4
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[3]/menu[0]/menu[1]").Select
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").firstVisibleRow = 415
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").currentCellRow = 389
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").firstVisibleRow = 385
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").selectedRows = "389"
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").clickCurrentCell
session.findById("wnd[0]/tbar[1]/btn[43]").press

session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[2]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Reports\SAP"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "SD07.xls"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 16
session.findById("wnd[1]/tbar[0]/btn[0]").press

MsgBox ("Done")

End Sub