-1

I use vbscript to run SAP GUI via Excel VBA, the following is my actions:

  • Login
  • select Tcode
  • give data and execute
  • export as worksheets. My question is that the last action (export as worksheets) will show a file dialog that I need to choose path and press. But I want to export automatically by my set path and filename without the "save as" dialog.

Is there any method to do that?

Here is my VBA code:

Dim SAPconn As Object
Dim SAPsession As Object
Dim SapGuiAuto As Object
Dim WshShell As Object

Shell "C:\Program Files\SAP\FrontEnd\SAPgui\saplogon.exe", 4
Set WshShell = CreateObject("WScript.Shell")

Do Until WshShell.AppActivate("SAP Logon ")
    Application.Wait Now + TimeValue("0:00:01")
Loop

Set WshShell = Nothing

Set SapGuiAuto = GetObject("SAPGUI")
Set SAPapp = SapGuiAuto.GetScriptingEngine

Set SAPconn = SAPapp.Openconnection("ConnectionsName", True)
Set SAPsession = SAPconn.Children(0)


'LOGON
SAPsession.findById("wnd[0]").maximize
SAPsession.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "100"
SAPsession.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "userid"
SAPsession.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "password"
SAPsession.findById("wnd[0]").sendVKey 0
SAPsession.findById("wnd[0]/tbar[0]/okcd").Text = "tcode"
SAPsession.findById("wnd[0]").sendVKey 0
'run the script(skip)
SAPsession.findById("wnd[0]/usr/ctxtWERKS-LOW").Text = Cells(1, 2)......
'execute
SAPsession.findById("wnd[0]/tbar[1]/btn[8]").press
'show list
SAPsession.findById("wnd[0]/tbar[1]/btn[48]").press
'export
SAPsession.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select 
'↑ just select export>as worksheet and open the file dailog....
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
pgrkie
  • 1
  • 1
  • 3
  • ? https://stackoverflow.com/questions/45465172/export-sap-to-excel-completely-via-vba or https://stackoverflow.com/questions/19452461/vba-general-way-for-pulling-data-out-of-sap – Tim Williams Jun 02 '20 at 07:18
  • If your SAP GUI option "display Windows native dialog" is switched off; the SAP dialog is displayed instead, and so you may add some code to enter the file path as for any other SAP screen. – Sandra Rossi Jun 02 '20 at 08:59
  • Sandra, my SAP is 720, I do not have the option "display Windows native dialog" . – pgrkie Jun 03 '20 at 04:02

1 Answers1

0

This is the code I use to save from SAP - the exact path to the elements

(e.g "wnd[1]/usr/ctxtDY_PATH") will be different to your case as it depends on what tables you are working with:

SAPsession.findById("wnd[1]/tbar[0]/btn[0]").press
SAPsession.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Downloads\"
SAPsession.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "Downloaded_file_name"
SAPsession.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 13
SAPsession.findById("wnd[1]/tbar[0]/btn[11]").press

This does the following:

  • Clicks the save button
  • specifies the desired file path where the file is to be saved
  • specifies the desired file name
  • positions the cursor and presses save

As far as I'm aware, it is not possible to entirely bypass the save dialog.

Nick
  • 3,454
  • 6
  • 33
  • 56
  • Hi Nick, thanks for your answer.I use this syntax but excel is waiting for OLE.It seems like I could not control the file dialog ... Did you set up something in SAP or Excel? – pgrkie Jun 02 '20 at 09:00
  • I have a similar set up to yours. I use VBA to run the SAP script. You need to comment out or delete the last line in your code that ends in `.Select`. I think that is you trying to select the `Export to excel` option on the file menu? Then add the lines from my answer, modifying the table names as required. – Nick Jun 02 '20 at 16:45
  • Thank you! I will try it. And there is another question I just found. When I export excel sheet, the file will open automatically. Is it possible to close the function?Just save and don't open it. – pgrkie Jun 03 '20 at 00:16
  • When you use this method the file will just be saved and not open. let me know if that is not the case. – Nick Jun 03 '20 at 07:37
  • Hi Nick, it worked! As you said the file didn't open.Appreciate your help very much!! And do you know how to close the sap logon window by script? (I can close the gui) – pgrkie Jun 04 '20 at 01:52
  • Glad i could help :). As for the sap logon window, does this answer your question: https://stackoverflow.com/a/56750286/2570277 – Nick Jun 04 '20 at 07:36
  • There is not the option in my version, but the question was sloved. TKS! – pgrkie Jun 11 '20 at 04:55