VBA Code to export data from SAP and save to Desktop

[Former Member]

Hi Script Gurus,

Good day.

I just wanna ask for your help in figuring out what is the solution to my problem.

I want to export a report in SAP and save it as PDF. When i click the "Ok" button to save the report, a new dialog box with name "Pdf995 Save As" appear after 3 seconds. Im trying to control the dialog box using SendKeys but it is not working. Below is the code:

Please help.

session.findById("wnd[1]/usr/cmbPRIPAR_EXT-OSPRINTER").Key = "PDF995"

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

    WScript.Sleep 10000

    Set Wshell = CreateObject("Wscript.Shell")

    Do

    bWindowFound = Wshell.AppActivate("Pdf995 Save As")

    WScript.Sleep 1000

    Loop Until bWindowFound

    WScript.Sleep 5000

    bWindowFound = Wshell.AppActivate("Pdf995 Save As")

    If (bWindowFound) Then

    WScript.Sleep 5000

    Wshell.AppActivate "Pdf995 Save As"

    WScript.Sleep 100

    Wshell.SendKeys ("Parked and Blocked Report" & " " & Format(Date, "mmddyyyy") & ".pdf")

    WScript.Sleep 100

    Wshell.SendKeys ("{ENTER}")

    WScript.Sleep 100

    End If

[stefan_schnell]

Hello Michael,

welcome in the Scripting Language forum.

I tried the printing of a source code in the SE80 transaction successfully with the following script.

Set Wshell = CreateObject("Wscript.Shell")

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

Do

  bWindowFound = Wshell.AppActivate("Print Screen List")

  WScript.Sleep 1000

Loop Until bWindowFound

session.findById("wnd[1]/usr/ctxtPRI_PARAMS-PDEST").text = "LP01"

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

Do

  bWindowFound = Wshell.AppActivate("Information")

  WScript.Sleep 1000

Loop Until bWindowFound

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

This is exact the same way as yours, but with other controls. In my opinion your script should work. Here your script, which I optimized a little bit, unfortunately without being able to prove it..

WScript.Sleep 10000

Set Wshell = CreateObject("Wscript.Shell")

Do

  bWindowFound = Wshell.AppActivate("Pdf995 Save As")

  WScript.Sleep 1000

Loop Until bWindowFound

Wshell.SendKeys ("Parked and Blocked Report" & " " & Format(Date, "mmddyyyy") & ".pdf")

WScript.Sleep 100

Wshell.SendKeys ("{ENTER}")

WScript.Sleep 100

Let us know your results.

Cheers

Stefan

[Former Member]

Thanks for that Stefan.

Your script is working but not everytime. Sometimes it is working, sometimes no . I am not sure where is the problem, if it has to do with the sleep function.

After doing below step, SAP is waiting for 5-10 seconds before the "Pdf995 Save As" appear. It depends on the volume of the report.

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

The script is not consistent. Sometimes it proceed with the saving of PDF, sometimes it stops when the dialog box appear. Do i have to declare anything?

Thanks in advance.

Mike

[stefan_schnell]

Hello Mike,

thanks for your reply.

Please look here for different reasons why AppActivate returns false. Observe your dialog and compare the status of the dialog with the described conditions.

Do you interact with your computer if the script is running - keyboard input, mouse pointer move?

Let us know your results.

Cheers

Stefan

[Former Member]

Hi Stefan,

I am not playing with the mouse when i start running the report. I just noticed that after i press the last print button (session.findById("wnd[1]/tbar[0]/btn[13]").press), a window appear. Its the "SAPLPD.LOG - SAPLPD" window. Could it be the reason why sometimes the script is not working?

[stefan_schnell]

Hello Mike,

this could be a reason.

Please, can you try this:

WScript.Sleep 10000

Set Wshell = CreateObject("Wscript.Shell")

Do

  WScript.Sleep 100

  bWindowFound = Wshell.AppActivate("Pdf995 Save As")

Loop Until bWindowFound

Wshell.SendKeys ("Parked and Blocked Report" & " " & Format(Date, "mmddyyyy") & ".pdf")

WScript.Sleep 100

Wshell.SendKeys ("{ENTER}")

WScript.Sleep 100

I changed in the loop the duration from 1 second to 0.1 second and the sequence of the commands - Sleep before AppActivate. On this way you reduce your time difference between the activation of the dialog and the sending of the key presses. So has the SAPLPD Print Daemon not much occasion to spark between this activities. Hope it works better now.

Another way would be, if you wait until SAPLPD Print Daemon window is open, via an additional loop, and then you activate your dialog and send the key presses, e.g. like this:

WScript.Sleep 10000

Set Wshell = CreateObject("Wscript.Shell")

Do

  WScript.Sleep 100

  bWindowFound = Wshell.AppActivate("SAPLPD.LOG - SAPLPD")

Loop Until bWindowFound

Do

  WScript.Sleep 100

  bWindowFound = Wshell.AppActivate("Pdf995 Save As")

Loop Until bWindowFound

Wshell.SendKeys ("Parked and Blocked Report" & " " & Format(Date, "mmddyyyy") & ".pdf")

WScript.Sleep 100

Wshell.SendKeys ("{ENTER}")

WScript.Sleep 100

So you are really sure that no other process you know can push in between.

Let us know your results.

Cheers

Stefan

[Former Member]

Thanks Stefan. I tried but im not getting any luck.

i pasted below my whole code. can you check if i missed any declaration or variant etc?

Im feeling hopeless now.

Thanks

Sub Sap_DownPDF()

Dim application

If Not IsObject(application) Then

   Set SapGuiAuto = GetObject("SAPGUI")

   Set application = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(Connection) Then

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

'Start the transaction to view a table

    session.findById("wnd[0]/tbar[0]/okcd").Text = "/N/DS1/MM_C_BLK_PARK23"

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

'Update the Company Code

    session.findById("wnd[0]/usr/ctxtS_BUKRS-LOW").Text = Sheets("Home").Range("K4").Value

    session.findById("wnd[0]/usr/txtS_GJAHR-LOW").Text = Sheets("Home").Range("K5").Value

    session.findById("wnd[0]/usr/txtS_GJAHR-HIGH").Text = Sheets("Home").Range("M5").Value

    session.findById("wnd[0]/usr/ctxtS_BLART-LOW").SetFocus

    session.findById("wnd[0]/usr/ctxtS_BLART-LOW").caretPosition = 0

    session.findById("wnd[0]/usr/btn%_S_BLART_%_APP_%-VALU_PUSH").press

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

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

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

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

    session.findById("wnd[1]/usr/ctxtPRI_PARAMS-PDEST").Text = "LOCL"

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

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

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

    session.findById("wnd[1]/usr/cmbPRIPAR_EXT-OSPRINTER").Key = "PDF995"

    Set Wshell = CreateObject("WScript.Shell")

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

WScript.Sleep 10000

Set Wshell = CreateObject("Wscript.Shell")

Do

WScript.Sleep 100

bWindowFound = Wshell.AppActivate("Pdf995 Save As")

Loop Until bWindowFound

Wshell.SendKeys ("Parked and Blocked Report" & " " & Format(Date, "mmddyyyy") & ".pdf")

WScript.Sleep 100

Wshell.SendKeys ("{ENTER}")

WScript.Sleep 100

End Sub

[stefan_schnell]

Hello Michael,

in my case your code works perfect, here my example code:

If Not IsObject(application) Then

   Set SapGuiAuto = GetObject("SAPGUI")

   Set application = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(Connection) Then

   Set Connection = application.Children(0)

End If

If Not IsObject(session) Then

   Set session = Connection.Children(0)

End If

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

session.findById("wnd[1]/usr/ctxtPRI_PARAMS-PDEST").Text = "LOCL"

session.findById("wnd[1]/usr/cmbPRIPAR_EXT-OSPRINTER").Key = "Adobe PDF"

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

Set Wshell = CreateObject("WScript.Shell")

WScript.Sleep 10000

Do

  WScript.Sleep 100

  bWindowFound = Wshell.AppActivate("PDF-Datei speichern unter")

Loop Until bWindowFound

Wshell.SendKeys ("Parked and Blocked Report" & " " & FormatDateTime(Date, vbShortDate) & ".pdf")

WScript.Sleep 100

Wshell.SendKeys ("{ENTER}")

WScript.Sleep 100

It works with and without open print daemon window, it seems that this makes no difference.

Don't be hopeless, together we will find a solution.

Cheers

Stefan