Scroll GuiGridView to find the row with a specific value in a given column

josh cunningham picture josh cunningham · Aug 16, 2017 · Viewed 8.7k times · Source

I have created an Excel spreadsheet that extracts live data from a query in SAP GUI and paste that info back into Excel Extraction Pic.

From there, I'm opening transaction ME38 to update schedule lines based off the extracted data. I need to go to the "scheduled.." column (which I know to be "txtEKET-MENGE") and change the number shown to the "Qty Delivered" (which is a part of the extraction); however, I need to only do this on certain cells in the "Schedule..." column ("txtEKET-ETENR") [data listed on extraction as well].
SAP Table Pic

When it gets to "Set grid..." it ends the function and does nothing else.

Any insight would be greatly appreciated.

Here is what I've done so far.

Set xclapp = CreateObject("Excel.Application")
Set xclwbk = ThisWorkbook
Set xclsht = xclwbk.Sheets("Sheet1")
For k = 2 To ActiveCell.SpecialCells(11).Row
For j = 1 To ActiveCell.SpecialCells(11).Column
If j = 14 Then Purch = xclsht.Cells(k, j).Value
If j = 15 Then Item = xclsht.Cells(k, j).Value
If j = 16 Then SLine = xclsht.Cells(k, j).Value
If j = 8 Then PGI = xclsht.Cells(k, j).Value
Next
myTransaction = "ME38"

Session.FindById("wnd[0]/tbar[0]/okcd").Text = "/n" & myTransaction

Session.FindById("wnd[0]").sendVKey 0

On Error Resume Next
Session.FindById("wnd[0]/usr/ctxtRM06E-EVRTN").Text = Purch
Session.FindById("wnd[0]/usr/ctxtRM06E-EVRTN").caretPosition = 10
Session.FindById("wnd[0]").sendVKey 0
Session.FindById("wnd[0]/usr/txtRM06E-EBELP").Text = Item
Session.FindById("wnd[0]/usr/txtRM06E-EBELP").caretPosition = 3
Session.FindById("wnd[0]").sendVKey 0
Session.FindById("wnd[0]/tbar[1]/btn[30]").press
Session.FindById("wnd[0]/tbar[1]/btn[2]").press
Call SelectRowOnGrid
Session.FindById("wnd[0]/mbar/menu[0]/menu[0]").Select
Session.FindById("wnd[0]").Close
  On Error Resume Next

  Session.FindById("wnd[1]/usr/btnSPOP-OPTION1").press
Next
End If
If Err.Number <> 0 Then

'The Excel worksheet has e.g. 3 columns of data and an error column.

xclsht.Cells(j, 21).Value = "Here is an error."

Else

xclsht.Cells(j, 21).Value = "O.K."

End If

On Error GoTo 0

The following is the Function SelectRowOnGrid.

Function SelectRowOnGrid()
Dim grid As SAPFEWSELib.GuiTableControl
Dim columnname As SAPFEWSELib.GuiTableColumn
Dim texttofind As String
Set grid = Session.FindById("wnd[0]/usr/tblSAPMM06ETC_1117/")
Set columnname = Session.FindById("wnd[0]/usr/tblSAPMM06ETC_1117/txtEKET-  ETENR")
texttofind = xclsht.Cells(k, 16).Value

    For k = 0 To grid.RowCount - 1

If grid.GetCellValue(k, columnname) = texttofind Then
grid.SetCurrentCell
grid.DoubleClickCurrentCell
End If

    Next k

End Function

Answer

ScriptMan picture ScriptMan · Aug 24, 2017

I will give you an example of how to deal with a table in SAP. In your case you use the commands for a GRID and this is not correct.

for example:

myFile = "z:\tmp\test.xlsx"
mySheet = "Test"

Set xclApp = CreateObject("Excel.Application")
Set xclwbk = xclapp.Workbooks.Open(myFile)
set xclsht = xclwbk.Sheets(mySheet)

xclApp.Visible = True
xclapp.DisplayAlerts = false

k = 1

do

set myTable = session.findById("wnd[0]/usr/ssubITEMS:SAPLFSKB:0100/tblSAPLFSKBTABLE")

 if k = 1 then
 'rows = myTable.RowCount
  cols = myTable.Columns.Count
  vRows = myTable.VisibleRowCount
  for j = 0 to cols - 1
   xclsht.Cells(k,j + 1).Value = myTable.columns.elementAt(j).title
  next
  k = k + 1
 end if

for i = 0 to vRows - 1
l = 1
 for j = 0 to Cols - 1
  on error resume next
  myVariable = trim(myTable.GetCell(i,j).Text) 
  if err.number <> 0 then  exit for      
  on error goto 0
  if left(right(myVariable,3),1) = "," then 
   myVariable = replace(myVariable, "." , "")
   myVariable = replace(myVariable, "," , "")
   xclsht.Cells(k,l).Value = myVariable/100
  else 
   xclsht.Cells(k,l).Value = myVariable 
  end if
  l = l + 1
 next
 if err.number <> 0 then  exit for       
 k = k + 1
next
if err.number <> 0 then  exit do       
myTable.VerticalScrollbar.Position = myTable.VerticalScrollbar.Position + vRows

Loop 
xclapp.ActiveWorkbook.Save

Set xclwbk = Nothing
Set xclsheet = Nothing
set xclapp = Nothing

Regards, ScriptMan