To paste the text from SAP into excel

Question

I am still new to SAP and VBA Excel.

I want to copy the text from sap to the Excel. As it the code run, it shows an empty cell.

I also want to do for loop to read each row of the purchase order.

Please point out the mistake, i made in this code.

Sub g142()

Worksheets("RCEP").Activate

Dim SapGuiAuto
Dim SetApp
Dim Connection
Dim Session

Set SapGuiAuto = GetObject("SAPGUI")
Set SetApp = SapGuiAuto.GetScriptingEngine
Set Connection = SetApp.Children(0)
Set Session = Connection.Children(0)

Session.findById("wnd[0]/tbar[0]/okcd").Text = "va03"
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/ctxtVBAK-VBELN").Text = "" 'To delete info inside the order
Session.findById("wnd[0]/usr/txtRV45S-BSTNK").Text = Cells(2, 1).Value 'Where I put purchase order number
Session.findById("wnd[0]/usr/txtRV45S-BSTNK").SetFocus
Session.findById("wnd[0]/usr/txtRV45S-BSTNK").caretPosition = 10
Session.findById("wnd[0]/usr/btnBT_SUCH").press
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/btnBT_HEAD").press 'Display Header Details
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08").Select
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[0]/shell").selectItem "ZAV1", "Column1"
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[0]/shell").ensureVisibleHorizontalItem "ZAV1", "Column1" 'To scroll down
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[0]/shell").topNode = "ZAEH"
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[0]/shell").doubleClickItem "ZAV1", "Column1" 'Click to open G14
Cells(2, 19).Value = Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[1]/shell").Text'The text i want to copy
Session.findById("wnd[0]/tbar[0]/btn[12]").press 'To go back
Session.findById("wnd[0]/tbar[0]/btn[12]").press 'to go back

    
End Sub

I've tried various method from youtube and from this forum. Maybe, i did not understand what i tried to do to cause this code not working on my side.

Answer 1

as Storax mentioned. The code that I use is correct. Therefore, I also asked for looping each row to let SAP read the PO number then pasted into the Excel. Here is the code:

'The only error I went through that I could not exit the loop and will end up showing Run-time error '619': The control could not be found by ID 'The error want to say there is no PO number to copy

UPDATE: The line of code work now! below is the code if anyone want to use it

    Sub ToGetTXTfromG14()

Worksheets("RCEP").Activate

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

Dim SapGuiAuto
Dim SetApp
Dim Connection
Dim Session

Set SapGuiAuto = GetObject("SAPGUI")
Set SetApp = SapGuiAuto.GetScriptingEngine
Set Connection = SetApp.Children(0)
Set Session = Connection.Children(0)

Dim rng As Range
Dim actrng As Range
Dim i As Long

Set actrng = Range("A1")
Set rng = Sheets("RCEP").Range("A2")
Rowmax = Sheets("RCEP").Cells(Cells.Rows.Count, rng.Column).End(xlUp).Row

    
    Session.findById("wnd[0]/tbar[0]/okcd").Text = "/n"
    Session.findById("wnd[0]").sendVKey 0
    Session.findById("wnd[0]/tbar[0]/okcd").Text = "va03"
    Session.findById("wnd[0]").sendVKey 0
    
For i = 1 To Rowmax
   
    If IsEmpty(actrng.Offset(i, 0).Value) Or actrng.Offset(i, 0).Value = "" Then
        Exit For 
    End If
    
   
    Session.findById("wnd[0]/usr/ctxtVBAK-VBELN").Text = "" 'To delete info inside the order
    Session.findById("wnd[0]/usr/txtRV45S-BSTNK").Text = actrng.Offset(i, 0).Value  'Where I put purchase order number
    Session.findById("wnd[0]/usr/txtRV45S-BSTNK").SetFocus
    Session.findById("wnd[0]/usr/txtRV45S-BSTNK").caretPosition = 10
    Session.findById("wnd[0]/usr/btnBT_SUCH").press
    Session.findById("wnd[1]/tbar[0]/btn[0]").press
    Session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/btnBT_HEAD").press 'Display Header Details
    Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08").Select
    Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[0]/shell").selectItem "ZAV1", "Column1"
    Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[0]/shell").ensureVisibleHorizontalItem "ZAV1", "Column1" 'To scroll down
    Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[0]/shell").topNode = "ZAEH"
    Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[0]/shell").doubleClickItem "ZAV1", "Column1" 'Click to open G14
    actrng.Offset(i, 18).Value = Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[1]/shell").Text  '= actrng.Offset(i, 19).Value 'The text i want to copy Cells(2, 19).Value =
    Session.findById("wnd[0]/tbar[0]/btn[12]").press 'To go back
   
Next i

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    
End Sub