on 02-26-2010 3:52 PM
I have a situation that must be very common u2013 but I canu2019t find any clear information on how to get it done!
We frequently run SAP transactions, download the results (orders or inventory) into Excel, do some calculations and create a spreadsheet report.
I would like to automate this process using Excel VBA so that a macro will perform these steps:
1. Run our custom SAP report "YSD033" that summarizes orders using the previous day as the [From Date] parameter. (The user already has an active ECC 6 R3 session running.) If possible, can the TC be run using a specific variant "G111BIZ" ?
2. Download the list object that appears in an ALV grid as a table to an empty spreadsheet in the active workbook (export XXL list object)
3. Save the resulting workbook and close Excel.
Should the solution use u201Ccall transactionu201D or a GuiXT script?
Any help would be much appreciated, and some sample VBA code would be great!
Thanks.
Glenn
can this be done by using DOI interface?
you can upload the excel file with VBA into SAP,
then you can write code using DOI to fill in data for calculation.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Since blocked RFC security settings are preventing the solution from being installed, I am marking this question as answered.
I will post different questions about 1. how to convince the security team that it will be safe to allow the use of RFC calls, and /or 2. how to use some windows-level scripting code to run the SAP jobs.
Thanks.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hitting the IT/security wall is always hard. Workarounds are always difficult.
As far as the Windows level scripting you can start with something like this:
Set WshShell = WScript.CreateObject("WScript.Shell")
wshshell.sendkeys "%"
wshshell.sendkeys "/nYSD033"
wshshell.sendkeys ""
Since YSD033 is a custom transaction I have no idea how to continue. However, doing it without the SAP Gui will be very difficult even for standard transactions, I think.
Good Luck.
Hmm ive tried these options and ive had to use the send keys solution but im having trouble with A.) switching to the downloaded sheet from SAP if i try to call for it while its exporting the sheet shows up blank and if put in a wait function it shows up blank. the best ive been able to do is simply call for excel with appactive but this dosent always bring the downloaded spreadsheet to the front. B.) calling back to Sap regardless of what window its on so i do something else. C.) getting the code to wait until sap has loaded the report. Does anyone have any suggestions?
SendKeys ShiftKey & "", True
DoEvents
Application.Wait Now() + TimeValue("00:00:01")
SendKeys "", True DoEvents Application.Wait Now() + TimeValue("00:00:01") SendKeys "", True
DoEvents
Application.Wait Now() + TimeValue("00:00:01")
SendKeys "", True
DoEvents
AppActivate "Microsoft Excel"
'Windows("Worksheet in Basis (1)").Activate
Are you in VBA (Excel or something) or vbs?
Sometimes the sendkeys goes too fast for SAP to keep up. What I've used to slow the code down is this:
WScript.Sleep 4000
Instead of the Application.wait you used.
Also, you used 1 second, 4000 above is 4 seconds. You may have to slow it down until it works.
I have had to use both sleep and Application wait something to do with how sap exports its data to excel but it works even if your going that fast as long as you put in DoEvents the only thing i have had to increase my sleep time for is switching windows or closing boxes. Another trick is add this to the begining so you can type just "sleep 500"
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
And yes this is all done in VBA I havent messed with pushing data as i dont trust the integrity of this code.
You should be able to do that with VBS from SAP itself (and not with VBA from Excel):
Go to the script recdorder: ALT+F12 > "Script Recording and playback", press the recording (red) button
Perform all you actions: go to your YSD033 screen, pull the variant, run the report, save it in Excel, close transaction.
Then stop recording.
The script (a VBS program) will be saved in your default SAP directory (but you can change this location esaily).
To run the script either play it from SAP (ALT+F12, etc except that instead of recrding it, play it) or you can also run it from Windows (if SAP is open) by double clicking the vbs file.
Edited by: Mario Rappi on Feb 26, 2010 7:18 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Mario, Thanks for the reply but I have two hurdles:
1- the alt-F12 record option is grayed out (security) and the <record> button on GuiXT did not create a VBS.
2- I will run much additional processing with the data using VBA after the download(s) complete.
Even if a separate script ran the download, it would be cleaner with it all in one VBA package.
Also, having control over the initiation of the transaction allows me to change the parameters (company holidays, various number of days, etc).
FYI - GuiXT Version 2009 Q1 1 did create InputScript.txt, but I think it is of little use. here's a sample:
// ABAP: Variant Directory of Program YSD033
Screen SAPLSVAR.0600
Enter control="CONTAINERCTRL.GridViewCtrl" item="0000000003;VARIANT;3;1" event="3"
If the "Script Recording..." options is grayed out then they probably won't let you do anything there.
See if changing the scripting options helps, but I doubt it will (ALT+F12 > Options > Scripting make sure scripting is enabled).
See if they let you run this:
(save this as script.vbs using notepad)
********************
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
session.findById("wnd[0]/tbar[0]/okcd").text = "/nYSD033"
session.findById("wnd[0]/tbar[0]/btn[0]").press
************************
f you can, there is hope.
I am not familar with GuiXT but it apparently creates an ABAP program, as opposed to a VBS
Good suggestion, but
I get "permission denied" for SapGuiAuto.GetScriptingEngine
I also tried the method below, but received this RFC error message:
User PPPPPPP has no RFC authorization for function group SYST.
Sub LoginCheck()
If login = False Then
' Setting the necessary variables for R/3 connection
Set objBAPICortrol = CreateObject("SAP.Functions")
Set objConnection = objBAPICortrol.Connection
' Establish a connection
If objConnection.Logon(0, False) Then
login = True
MsgBox "Connection Established"
CommandButton1.Caption = "Disconnect"
End If
Else
CommandButton1.Caption = "Connect 2 SAP"
login = False
objConnection.Logoff
Set objConnection = Nothing
Set objBAPICortrol = Nothing
End If
End Sub
I was told that these kinds of authority open up too big of a window that can't be monitored adequately...
I'm considering an approach like what is below if I can't convince security to grant me permissions...
Application.Wait Now + TimeValue("00:00:01")
SendKeys EnterKey, False
Since blocked RFC security settings are preventing the solution from being installed, I am markgin this question as answered.
I will post different questions about 1. how to convince the security team that it will be safe to allow the use of RFC calls, and /or 2. how use some windows-level scripting code to run the SAP jobs.
Thanks.
Edited by: GlennWebster on Mar 1, 2010 4:34 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.