I am a newbie in VBA & SAP GUI Scirpting. I need help implementing the Code into the recorded VBScript.
I have an Excel Sheet where the Data is imported from TM03 into the Excel Sheet using the Referenze Number. This part is working fine.
The Code:
Public Sub RunGUIScript(currentline As Integer)
Dim W_Ret As Boolean
W_Ret = Attach_Session
If Not W_Ret Then
Exit Sub
End If
On Error GoTo myerr
objSess.FindById("wnd[0]").maximize
objSess.FindById("wnd[0]/tbar[0]/okcd").Text = "/ntm03" 'TM03
objSess.FindById("wnd[0]").SendVKey 0
objSess.FindById("wnd[0]/usr/ctxtVTMFHA-BUKRS").Text = "0050" ' Company Code
objSess.FindById("wnd[0]/usr/ctxtVTMFHA-RFHA").Text = Cells(currentline, 1).Value ' Referenze Nummer
objSess.FindById("wnd[0]").SendVKey 0
Cells(currentline, 6).Value = objSess.FindById("wnd[0]/usr/tabsTS00/tabpBASE/ssubTS00_SUB:SAPLTM00:1203/ctxtVTMFHAZU-XVTRAB").Text ' Abschlussdatum
Cells(currentline, 9).Value = objSess.FindById("wnd[0]/usr/tabsTS00/tabpBASE/ssubTS00_SUB:SAPLTM00:1203/txtVTMFHA-KONTRH").Text ' Geschäftspartner
Cells(currentline, 11).Value = objSess.FindById("wnd[0]/usr/tabsTS00/tabpBASE/ssubTS00_SUB:SAPLTM00:1203/txtVTMHPTBWG-XZBETR").Text ' Zahlungsbetrag
Cells(currentline, 16).Value = objSess.FindById("wnd[0]/usr/tabsTS00/tabpBASE/ssubTS00_SUB:SAPLTM00:1203/subDATES:SAPLTM00:0011/ctxtVTMFHAZU-XBLFZ").Text 'Laufzeit Beginn
Cells(currentline, 17).Value = objSess.FindById("wnd[0]/usr/tabsTS00/tabpBASE/ssubTS00_SUB:SAPLTM00:1203/subDATES:SAPLTM00:0011/ctxtVTMFHAZU-XELFZ").Text 'Laufzeit Ende
Cells(currentline, 19).Value = objSess.FindById("wnd[0]/usr/tabsTS00/tabpBASE/ssubTS00_SUB:SAPLTM00:1203/txtVTMHPTBWG-XNWHR").Text ' Nominalbetrag
' Setting the line status to completed
Cells(currentline, 2).Value = 1
Exit Sub
myerr:
' Some error occured
' Setting the line status to Failed
Cells(currentline, 2).Value = 2
End Sub
Sub StartExtract()
Dim currentline As Integer
' This is the system to connect to
W_System = "Z2L100"
' We start looking for order numbers from line 8 in the sheet
currentline = 8
While Cells(currentline, 1).Value <> ""
' Only process this line if the status is "to be processed"
If Cells(currentline, 2).Value = 0 Then
' Run the actual GUI script
RunGUIScript currentline
End If
' move to the next line
currentline = currentline + 1
Wend
' Update the current date and time
Cells(2, 3).Value = Now()
objSess.EndTransaction
End Sub
As you can see as the Status is Completed and if there is no more Referenze Number to search for it Stops. Thats Ok.
The second Step is to take the Referenze Number (here I have to concatenate a ASTERIX to the Referenze Number - example [*12345] & the Closing Date paste it into SAP search criteria, opens and geting the Data from GridView and paste it back into the sheet.
Here ist the SAP GUI Scripting generated Code:
objSess.FindById("wnd[0]/tbar[0]/okcd").Text = "/nse16"
objSess.FindById("wnd[0]").SendVKey 0
objSess.FindById("wnd[0]/usr/ctxtDATABROWSE-TABLENAME").Text = "VTBFHAZU"
objSess.FindById("wnd[0]").SendVKey 0
objSess.FindById("wnd[0]").SendVKey 17
objSess.FindById("wnd[1]/usr/txtENAME-LOW").Text = ""
objSess.FindById("wnd[1]/usr/txtENAME-LOW").SetFocus
objSess.FindById("wnd[1]/usr/txtENAME-LOW").caretPosition = 0
objSess.FindById("wnd[1]/tbar[0]/btn[8]").Press
objSess.FindById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").CurrentCellRow = 2
objSess.FindById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").SelectedRows = "2"
objSess.FindById("wnd[1]/tbar[0]/btn[2]").Press
objSess.FindById("wnd[0]/usr/ctxtI4-LOW").Text = "06.08.2018" ' CLOSING DATE
objSess.FindById("wnd[0]/usr/ctxtI4-LOW").SetFocus
objSess.FindById("wnd[0]/usr/ctxtI4-LOW").caretPosition = 10
objSess.FindById("wnd[0]").SendVKey 0
objSess.FindById("wnd[0]/usr/txtI14-LOW").Text = "*25750" ' ASTERIX (*) & REFERENZE NUMBER from Excel
objSess.FindById("wnd[0]/usr/txtI14-LOW").SetFocus
objSess.FindById("wnd[0]/usr/txtI14-LOW").caretPosition = 6
objSess.FindById("wnd[0]").SendVKey 0
objSess.FindById("wnd[0]").SendVKey 8
' Store The GridView As Local Object
Set GridView = objSess.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell")
' Extract The Column Titles
For i = 0 To GridView.ColumnCount - 1
Cells(9, i + 1).Value = GridView.GetColumnTitles(GridView.ColumnOrder(i))(0)
Next i
' Extract The Content
For i = 0 To GridView.ColumnCount - 1
For j = 0 To GridView.RowCount - 1
Cells(10 + j, i + 1).Value = GridView.GetCellValue(j, GridView.ColumnOrder(i))
Next j
Next i
This Code extracts me the Header name, adittional 2 rows with 63 columns. Actually i dont need all of the data. From the GridView I ONLY need:
row 1 column 3 row 1 column 37 and row 2 column 3 row 2 column 37
pasted back into the active excel sheet row. (see the attached picture)
Then the next referenze nummber.
The Excel Sheet:
Excel-Where the data should be imported/pasted from the GridView
It should not be complicated for someone who has programming skills. I am hoping that someone will help me on this.
I didnt wrote the code above. I used the code and implemented it with my SAP GUI Recording and referenzing to the cells into excel. The goal here is to implement all in one vba module.
Thanks in advance ! Since this is my first post i hope you wont go harsh on me :) I´ll stay here to learn with the experienced ones. Thank you for sharing your knowledge! Sharing is caring!
Cheers.
