I've tested VB script to park a document into SAP from Excel spreadsheet.
Prerequisite:
1. Dataset in Excel file
2. Create a new macro
3. Recording SAPGUI Script
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.
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
2 Comments
