Copy / Pasting Data From Excel to SAP software

Question

I currently have a process where I am generating demands in SAP software, and ordering the components for those demands from the subsequent warehouses. Right now I have the data coming into a shared spreadsheet and then one by one (line by line) I am copying the material number and quantity into a transaction to generate the orders (what we call TR's). I am trying to find a way to add to my current VBA/GUI to do the copy and paste part of the process for me.

Thanks!

(My code is pasted below, the last line opens the transaction and sets the requester in where I want to paste the materials.)

Sub BPORDER()
Dim OrderID As String
If Not IsObject(app) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set app = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = app.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 app, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/ncoois"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/txtV-LOW").Text = "004 BPS"
session.findById("wnd[1]/usr/ctxtENVIR-LOW").Text = ""
session.findById("wnd[1]/usr/txtENAME-LOW").Text = ""
session.findById("wnd[1]/usr/txtAENAME-LOW").Text = ""
session.findById("wnd[1]/usr/txtMLANGU-LOW").Text = ""
session.findById("wnd[1]/usr/txtV-LOW").caretPosition = 7
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[1]").sendVKey 8
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlGRID_0100/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlGRID_0100/shellcont/shell").selectContextMenuItem "&PC"
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press

Sheets.Add(After:=Sheets("Part List")).Name = "PARTDATA"
ActiveSheet.Name = "PARTDATA"

Worksheets("PARTDATA").Range("A1").Select
ActiveSheet.PasteSpecial

Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
        :=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array _
        (6, 1), Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True
    ActiveWindow.SmallScroll Down:=-21
    
Rows("1:7").Select
    Selection.delete Shift:=xlUp
    Columns("A:A").Select
    Selection.delete Shift:=xlToLeft
    
Range("A1:B1500").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Part List").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False

Application.DisplayAlerts = False
Sheets("PARTDATA").delete
Application.DisplayAlerts = True

Range("A2:A1500").Select
    Application.CutCopyMode = False
    Selection.Copy
Sheets("Daily Part REQ").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
Range("B3").Select
Application.CutCopyMode = False
ActiveSheet.Range("Table1[#All]").RemoveDuplicates Columns:=1, Header:= _
        xlYes
    
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/ncoois"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/txtV-LOW").Text = "004 EW BPS"
session.findById("wnd[1]/usr/ctxtENVIR-LOW").Text = ""
session.findById("wnd[1]/usr/txtENAME-LOW").Text = ""
session.findById("wnd[1]/usr/txtAENAME-LOW").Text = ""
session.findById("wnd[1]/usr/txtMLANGU-LOW").Text = ""
session.findById("wnd[1]/usr/txtV-LOW").caretPosition = 10
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlGRID_0100/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlGRID_0100/shellcont/shell").selectContextMenuItem "&PC"
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press

Sheets.Add(After:=Sheets("Part List")).Name = "PARTDATA"
ActiveSheet.Name = "PARTDATA"

Worksheets("PARTDATA").Range("A1").Select
ActiveSheet.PasteSpecial

Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
        :=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array _
        (6, 1), Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True
    ActiveWindow.SmallScroll Down:=-21
    
Rows("1:7").Select
    Selection.delete Shift:=xlUp
    Columns("A:A").Select
    Selection.delete Shift:=xlToLeft
    
Range("A1:B1500").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Part List").Select
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False

Application.DisplayAlerts = False
Sheets("PARTDATA").delete
Application.DisplayAlerts = True

Range("D3:D1500").Select
    Application.CutCopyMode = False
    Selection.Copy
Sheets("Daily Part REQ").Select
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
Range("F3").Select
Application.CutCopyMode = False
ActiveSheet.Range("Table14[#All]").RemoveDuplicates Columns:=1, Header:= _
        xlYes
    
    
OrderID = Workbooks("Block Pick Order NEW.xlsm").Worksheets("Daily Part REQ").Range("Z2")

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nzpp_req"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/ctxtENVIR-LOW").Text = ""
session.findById("wnd[1]/usr/txtENAME-LOW").Text = ""
session.findById("wnd[1]/usr/txtAENAME-LOW").Text = ""
session.findById("wnd[1]/usr/txtMLANGU-LOW").Text = ""
session.findById("wnd[1]/usr/txtV-LOW").Text = "004 BP"
session.findById("wnd[1]/usr/txtV-LOW").caretPosition = 6
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[1]").sendVKey 8
session.findById("wnd[0]").sendVKey 8
session.findById("wnd[0]/usr/tabsRF_REQTAB/tabpMATREQ/ssubSUB:ZPPR5580_RF_REQUEST:0110/txtX_MATREQ-REQ_NAME").Text = OrderID
session.findById("wnd[0]/usr/tabsRF_REQTAB/tabpMATREQ/ssubSUB:ZPPR5580_RF_REQUEST:0110/txtX_MATREQ-REQ_NAME").SetFocus
session.findById("wnd[0]/usr/tabsRF_REQTAB/tabpMATREQ/ssubSUB:ZPPR5580_RF_REQUEST:0110/txtX_MATREQ-REQ_NAME").caretPosition = 7
End Sub

Nothing really tried yet just tests and questions

VBA can get text from the clipboard. One example [here](https://stackoverflow.com/questions/63425010/how-to-paste-values-from-the-clipboard-to-sap-gui). — Sandra Rossi, Feb 13 '23 at 17:31

Copy / Pasting Data From Excel to SAP software

0 Answers0