Button "download to excel" using VBA ('save as' window)

[Former Member]

Hello everyone!

I've seen many topics on forum but my problem isn't yet solved.

I start sap-script from Excel VBA. When SAPscript is ready to download report and press yellow button, process stops and requires an user action (window "save as"). A problem arises due to the fact, that "save as" window is out of SAPscript control.

As macros works in cycle, I have to input file name and type every time.

How can I escape this action without manual operation?

Sub Y_KLD_31001046()

Dim SapGuiApp As Object

Dim oConnection As Object

Dim SAPCon As Object, SAPSesi As Object

Dim SAPGUIAuto As Object, SAPApp As Object

'start sap

    If SapGuiApp Is Nothing Then

    Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.1")

    End If

    If oConnection Is Nothing Then

    Set oConnection = SapGuiApp.OpenConnection("F6P EU SC Prod - SSO", True)      'select SAP box

    End If

    If SAPSesi Is Nothing Then

    Set SAPSesi = oConnection.Children(0)

    End If

'start transaction

    SAPSesi.StartTransaction "Y_KLD_31001046"

'fill data

With SAPSesi

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

.findById("wnd[0]/usr/ctxtS_WERKS-LOW").Text = "8127"

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

.findById("wnd[0]/usr/ctxtS_DATE-LOW").Text = "01.06.2015"

.findById("wnd[0]/usr/radP_PERSU").SetFocus

.findById("wnd[0]/tbar[1]/btn[8]").press                                              'button Execute

.findById("wnd[0]/tbar[1]/btn[6]").press                                              'button "Download to Excel" (yellow selection)

.findById("wnd[0]/tbar[0]/btn[3]").press                                              'button "back"

End With

End Sub

[former_member411491]

Hi Mari,

Yes you can do that also with Automation.

Am also using same functionality. AutoIT (Au3 files) these are automated script file.With the help of these you can send key and it it the name as per your script.

send ("Name of file")

send("{ENTER}")

You can trigger .AU3 file throug excel. But in the case the issue is your file is always overwrite whenever you call AutoIT if you need to change the name of file then make one column and add the names. Save the value in a variable and triger Auto IT with that variable name.

Regards

Sanjay

[Former Member]

Hi Sanjay,

I am new in scripting VBA. Need help here. I want to download Invoice copies thru VF03 and save it a desktop or any specific folder.Filw should be saved with Invoice name which will be picked up from range as per loop.

Have generated below loop but unable to save further.

Sub Download_Invoices()

'Initiate SAP

If Not IsObject(SapGuiAuto) Then

   Set SapGuiAuto = GetObject("SAPGUI")

   Set sapapplication = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(Connection) Then

   Set Connection = sapapplication.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

'SAP Initiated

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

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

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

i = 2

Do While (Cells(i, 1).Value <> "")

session.findById("wnd[0]/usr/ctxtVBRK-VBELN").Text = Cells(i, 1).Value

session.findById("wnd[0]/usr/ctxtVBRK-VBELN").caretPosition = 10

session.findById("wnd[0]/mbar/menu[0]/menu[11]").Select

session.findById("wnd[1]/usr/tblSAPLVMSGTABCONTROL").getAbsoluteRow(0).Selected = False

session.findById("wnd[1]/usr/tblSAPLVMSGTABCONTROL").getAbsoluteRow(1).Selected = True

session.findById("wnd[1]/usr/tblSAPLVMSGTABCONTROL/txtNAST-KSCHL[0,1]").SetFocus

session.findById("wnd[1]/usr/tblSAPLVMSGTABCONTROL/txtNAST-KSCHL[0,1]").caretPosition = 0

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

session.findById("wnd[0]/tbar[0]/okcd").Text = "PDF!"

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

i = i + 1

Loop

End Sub