Running SAP reports from Excel with BAPI

[Former Member]

Dear all,

I`m trying to run reports with SE38 directly from Excel but without sucess... The result of report should be download to a .txt file or excel.

Can anyone help?

Thanks!

[former_member264728]

I've used an SAP GUI script to logon, run a report, and within the report save it to a local drive. It requires that the PC be on, but that's it. Here's the .vbs code:

'Launch saplogon

Dim MySAPWorker

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

If Not IsObject(application) Then

Set SapGuiAuto = GetObject("SAPGUI")

Set application = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(connection) Then

'Pick system to log onto, based on SAPGUI discription

Set connection = application.OpenConnection("c) NPS [46C-PRODUCTION]")

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

'logs on as a particular user

session.findByID("wnd[0]/usr/txtRSYST-MANDT").text = "[Client]"

session.findByID("wnd[0]/usr/txtRSYST-BNAME").text = "[User Name]"

session.findByID("wnd[0]/usr/pwdRSYST-BCODE").text = "[User Password]"

session.findByID("wnd[0]/usr/txtRSYST-LANGU").setFocus

session.findByID("wnd[0]/usr/txtRSYST-LANGU").caretPosition = 0

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

'If there are multiple logons, we must process the pop-up

If session.children.count > 1 Then

session.findById("wnd[1]/usr/radMULTI_LOGON_OPT2").select

session.findById("wnd[1]/usr/radMULTI_LOGON_OPT2").setFocus

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

End If

'Run the transaction - ZDRG2 in this case

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

session.findById("wnd[0]/tbar[0]/okcd").text = "/nzdrg2"

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

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

'Use report selection variant PLANNING_BT

session.findById("wnd[1]/usr/txtV-LOW").text = "PLANNING_BT"

session.findById("wnd[1]/usr/txtV-LOW").caretPosition = 3

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

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

session.findById("wnd[0]/usr/chkP_COLOR").selected = false

session.findById("wnd[0]/usr/chkP_FILDC").selected = true

'Saves file location using a local path

session.findById("wnd[0]/usr/ctxtP_FILEC").text = "T:\PLANNING\Respool\Scripting\wkdue3.xls"

session.findById("wnd[0]/usr/ctxtP_FILEC").setFocus

session.findById("wnd[0]/usr/ctxtP_FILEC").caretPosition = 30

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

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

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

'close session

session.findById("wnd[0]/tbar[0]/okcd").text = "/nex"

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

[Former Member]

Hi Nuno,

If your reports are in ALV format.

After displaying the report go to Menu Bar ->List->export->local file->you will see 4 options in that select spread sheet radio button and click enter it will ask the destination or the path where it has to store give the path and save. You can download in .txt html and rich format too.

Cheers!!

VEnk@

[former_member181995]

After one month you land with this solution!?

[hans-dieter_loew]

Hi Nuno,

you can't call a transaction directly. You must create an RFC- Function module in that you call

CALL FUNCTION 'TH_SERVER_LIST'

TABLES

LIST = SERVER_LIST.

and

CALL FUNCTION 'THUSRINFO' DESTINATION DESTI

TABLES

USR_TABL = USR_LISTE

EXCEPTIONS

COMMUNICATION_FAILURE = 17 MESSAGE MSG

SYSTEM_FAILURE = 17 MESSAGE MSG.

See report RSUSR000

This new function module you can call via VBA from Excel.

At first you will need to logon:

Option Explicit

Public oconnect As Object

Public Function Logon(Optional systemid As String) As Boolean

Dim oConnection As Object

Logon = True

Set oconnect = CreateObject("SAP.FUNCTIONS")

Set oConnection = oconnect.connection

If Len(systemid) > 0 Then

oConnection.systemid = systemid

oConnection.autologon = True

End If

On Error GoTo installationerror

If oConnection.Logon(0, False) = False Then

MsgBox "Logon error", vbCritical, "Logon Status"

Logon = False

Else

Logon = True

Application.DisplayStatusBar = True

Application.StatusBar = "Successfully loged on"

End If

Exit Function

installationerror:

MsgBox (Err.Description)

Logon = False

End Function

After you logged on you can call your new function module:

...

o_connect.RemoveAll

Set lo_rfc_get_senarios = o_connect.Add("ISR_CUST_GROUP_GET")

' Importing Parameter

Set loScenGrp = lo_rfc_get_senarios.exports("I_ISRGROUP")

loScenGrp.value = gd_ScenGroup

' Return tables

Set lo_scenarios = lo_rfc_get_senarios.tables("ET_SCENARIO")

' lo_OutMethods -> ET_METHOD_XML

Set lo_characteristics = lo_rfc_get_senarios.tables("ET_CHARACTERISTICS")

' lo_attributes -> ET_ATTRIBUTE_XML

' Call fm ISR_CUST_GROUP_GET

result = lo_rfc_get_senarios.Call

If result = False Then

Error = 1

Exit Function

End If

In this example coding I call the function module ISR_CUST_GROUP_GET with parameter I_ISRGROUP and tables ET_SCENARIO and ET_CHARACTERISTICS.

Kind regards

Hans-Diteer

[Former Member]

Just one more sample detail:

The idea is run report RSUSR000 and have the result in .txt or .xls file.

Thanks!