Skip to Content

I’ve tested VB script to park a document into SAP from Excel spreadsheet.

Prerequisite:

1. Dataset in Excel file

ExcelData.GIF

2. Create a new macro

ExcelMacro.GIF

3. Recording SAPGUI Script

SAPGUIRecordingWindow.GIF

With the red ‘record script’ button, you can get the script.

and then..

– open the macro file

– put the “set session” from open source

– add the recording

so this below is the full script on VBA

————————————————————————————————————————————————————-

Sub ParkDoc()

‘ ParkDoc Macro

‘ Park with F-65 in SAP

‘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

Set SapGuiAuto = GetObject(“SAPGUI”)  ‘Get the SAP GUI Scripting object

Set SAPApp = SapGuiAuto.GetScriptingEngine ‘Get the currently running SAP GUI

Set SAPCon = SAPApp.Children(0) ‘Get the first system that is currently connected

Set session = SAPCon.Children(0) ‘Get the first session (window) on that connection

‘  Start the transaction to view a table

session.StartTransaction “F-65”

‘session.findById(“wnd[0]”).maximize

session.findById(“wnd[0]/tbar[0]/okcd”).Text = “/nF-65”

session.findById(“wnd[0]”).sendVKey 0

session.findById(“wnd[0]/usr/ctxtBKPF-BLDAT”).Text = Sheets(“sheet1”).Cells(6, 2).Value ‘Document date

session.findById(“wnd[0]/usr/ctxtBKPF-BUDAT”).Text = Sheets(“sheet1”).Cells(7, 2).Value ‘Posting date

session.findById(“wnd[0]/usr/ctxtBKPF-BLART”).Text = Sheets(“sheet1”).Cells(1, 2).Value ‘Document type

session.findById(“wnd[0]/usr/ctxtBKPF-BUKRS”).Text = Sheets(“sheet1”).Cells(4, 2).Value ‘company code

session.findById(“wnd[0]/usr/ctxtBKPF-WAERS”).Text = Sheets(“sheet1”).Cells(5, 2).Value ‘Currency

session.findById(“wnd[0]/usr/txtBKPF-XBLNR”).Text = Sheets(“sheet1”).Cells(3, 2).Value ‘Reference

session.findById(“wnd[0]/usr/txtBKPF-BKTXT”).Text = Sheets(“sheet1”).Cells(2, 2).Value ‘Header text

Dim i As Integer

i = 10

Dim CC

Dim DUEDATE

Dim CURRENTA_CCOUNT

Do While Sheets(“Sheet1”).Cells(i, 2).Value <> “”

session.findById(“wnd[0]/usr/ctxtRF05V-NEWBS”).Text = Sheets(“sheet1”).Cells(i, 2).Value ‘Posting key

session.findById(“wnd[0]/usr/ctxtRF05V-NEWKO”).Text = Sheets(“sheet1”).Cells(i, 3).Value ‘Account

CURRENTA_CCOUNT = Sheets(“sheet1”).Cells(i, 3).Value

session.findById(“wnd[0]/usr/ctxtRF05V-NEWUM”).Text = Sheets(“sheet1”).Cells(i, 4).Value ‘Special G/L

session.findById(“wnd[0]/usr/ctxtRF05V-NEWKO”).SetFocus

session.findById(“wnd[0]”).sendVKey 0

session.findById(“wnd[0]/usr/txtBSEG-WRBTR”).Text = Sheets(“sheet1”).Cells(i, 5).Value ‘Amount

session.findById(“wnd[0]”).sendVKey 0

DUEDATE = Sheets(“sheet1”).Cells(i, 6).Value

If DUEDATE <> “” Then

session.findById(“wnd[0]/usr/ctxtBSEG-ZFBDT”).Text = Sheets(“sheet1”).Cells(i, 6).Value ‘Due On date

End If

CC = Sheets(“sheet1”).Cells(i, 7).Value

If CC <> “” Then

If CURRENT_ACCOUNT = 773610 Then

session.findById(“wnd[0]/usr/subBLOCK:SAPLKACB:1006/ctxtCOBL-KOSTL”).Text = Sheets(“sheet1”).Cells(i, 7).Value ‘Cost Center

ElseIf CURRENT_ACCOUNT = 458711 Then

session.findById(“wnd[0]/usr/subBLOCK:SAPLKACB:1014/ctxtCOBL-KOSTL”).Text = Sheets(“sheet1”).Cells(i, 7).Value ‘Cost Center

End If

End If

session.findById(“wnd[0]/usr/ctxtBSEG-SGTXT”).Text = Sheets(“sheet1”).Cells(i, 8).Value ‘Lineitem Text

i = i + 1

Loop

session.findById(“wnd[0]”).sendVKey 0

session.findById(“wnd[0]/mbar/menu[0]/menu[5]”).Select

End Sub

——————————————————————————————————————————————————————

Execute:

On your excel file, open the Macro window and run as below.

MacroRun.GIF

With the step into’ and debugging mode,

you can do it seeing the each value for each field in the SAP screen

Epilogue:

Depending on the SAP screen field control, you need to put a what-if logic sometimes though.

but this works very well and so powerful and flexible if you are understanding a little bit on VBA

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. bernard boils

    Hi, I’ve tested VB Script. I recorded SAP GUI Script but I was unable to complete it to collect SAP data from MARC Table.

    Could you help me

    (0) 
    1. SUNHO Lee

      Did you find a point which makes you stop? Did you get any message from it?

      you could debug seeing both SAP screen and VB script screen.

      (0) 

Leave a Reply