Question Re: Call Transaction from Excel VBA macro and download ALV list object results in Questions about SAP Websites

Call Transaction from Excel VBA macro and download ALV list object results

Former Member — Fri, 26 Feb 2010 15:52:43 GMT

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

Re: Call Transaction from Excel VBA macro and download ALV list object results

Former Member — Fri, 26 Feb 2010 18:14:57 GMT

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

Re: Call Transaction from Excel VBA macro and download ALV list object results

Former Member — Fri, 26 Feb 2010 19:25:00 GMT

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"

Re: Call Transaction from Excel VBA macro and download ALV list object results

Former Member — Fri, 26 Feb 2010 21:10:45 GMT

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

Re: Call Transaction from Excel VBA macro and download ALV list object results

Former Member — Fri, 26 Feb 2010 21:59:49 GMT

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

MsgBox "Connection Established"

CommandButton1.Caption = "Disconnect"

End If

Else

CommandButton1.Caption = "Connect 2 SAP"

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

Re: Call Transaction from Excel VBA macro and download ALV list object results

Former Member — Mon, 01 Mar 2010 15:40:43 GMT

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.

Re: Call Transaction from Excel VBA macro and download ALV list object results

Former Member — Mon, 01 Mar 2010 18:09:33 GMT

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.

Re: Call Transaction from Excel VBA macro and download ALV list object results

Former Member — Thu, 04 Nov 2010 20:36:19 GMT

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

Re: Call Transaction from Excel VBA macro and download ALV list object results

Former Member — Tue, 21 Dec 2010 04:55:10 GMT

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.

Re: Call Transaction from Excel VBA macro and download ALV list object results

Former Member — Tue, 18 Jan 2011 13:36:03 GMT

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.

Re: Call Transaction from Excel VBA macro and download ALV list object results

Former Member — Thu, 10 Feb 2011 21:13:38 GMT

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.