0

I'm trying to look for a specific text from my excel file to a SAP table.

I have tried below code but it gives me error "Invalid Next Control variable reference"

So far below is my code: 

set rLastRow = rSheet.Cells(rSheet.rows.Count, "AO").End(xlUp).Row
Set Table = session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")

rows = Table.RowCount - 1
cols = Table.RowCount - 1


Dim columns As Object
Set columns = Table.ColumnOrder

For i = 0 To rows

For j = 2 To rLastRow


If rSheet.Cells(j, "AO").Value = Table.GetCellValue(i, columns(3)) Then

 MsgBox "Found!" & rSheet.Cells(j, "AO").Value & Table.GetCellValue(i, columns(3)), vbOKOnly

Else

'proceed to next value to find

End If

Next i

Next j

What I'm trying to do is for each cell in range "AO" in excel, it will look for a match in my GridView (SAP: fbl3n).

Is there another way where can I loop to each cell from range (excel) and look for it in a SAP table (GridView)?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Sevpoint
  • 213
  • 1
  • 8
  • 26

2 Answers2

2

If the report is displayed as a grid, you could try the following:

Sub Test()
'
' Test Makro
'
Set SapGuiAuto = GetObject("SAPGUI")
Set SAPApplication = SapGuiAuto.GetScriptingEngine
Set Connection = SAPApplication.Children(0)
Set session = Connection.Children(0)

rLastRow = ActiveCell.SpecialCells(xlLastCell).Row
Set Table = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")

allRows = Table.RowCount - 1

Dim columns As Object
Set columns = Table.ColumnOrder

For i = 2 To rLastRow
'MsgBox Application.ActiveSheet.Cells(i, 41).Value
ActiveSheet.Cells(i, 41).Select

 For j = 0 To allRows
 'MsgBox Table.GetCellValue(j, columns(3))

  If ActiveSheet.Cells(i, 41).Value = Val(Table.GetCellValue(j, columns(3))) Then
   Table.setCurrentCell j, ""
   Table.selectedRows = j
   MsgBox "Found: " & ActiveSheet.Cells(i, 41).Value & " / " &   Table.GetCellValue(j, columns(3)), vbOKOnly
  End If

 Next
Next
'
End Sub

Regards, ScriptMan

ScriptMan
  • 1,580
  • 1
  • 9
  • 9
  • Thank you ScriptMan! I will try this. Question, is there a method in ALV GridView that I can use similar with excel Range.Find? – Sevpoint Feb 20 '18 at 12:45
  • You can find out it for yourself: http://documentation.microfocus.com/help/index.jsp?topic=%2Fcom.borland.silktest.silk4net.doc%2Flangref%2FSAP%2FGetCellValueMethodSapGridView_ref.html – ScriptMan Feb 20 '18 at 12:57
  • With the link above I have just seen that you have first to search for "gridview" and then click on "sapgridview class". – ScriptMan Feb 20 '18 at 13:34
  • Thanks a lot ScriptMan! I am now able to achieve what I need to do with the solution you provided. I also checked the documentation you gave and able to familiarize the SAP GridView methods. :) – Sevpoint Feb 21 '18 at 07:46
  • Hi ScriptMan, I would just like to ask again regarding the DoubleClick. If my data is found in the sap Grid, I would like to doubleclick it as I need to edit some text for that certain item. I tried to add the Table.DoubleClickCurrentCell, but it seems that it is not working and just continue looping. Would you mind if you can advise on this? Thank you. – Sevpoint Feb 22 '18 at 05:46
  • Hello ScriptMan, I found out that I need to change Table.setCurrentCell j, "" to sTable.CurrentCellRow = j. Thank you! – Sevpoint Feb 22 '18 at 06:11
  • Then the link to the documentation of Microfocus was the right one. ;-) – ScriptMan Feb 22 '18 at 07:04
  • The documentation link doesn't work anymore. This one currently works: [GuiGridView Object](https://help.sap.com/viewer/product/sap_gui_for_windows/latest/en-US?q=GuiGridView). – Sandra Rossi Aug 25 '21 at 15:51
0

I don't know SAP, however I would

  1. remove Set in front of rLastRow becasuse rSheet.Cells(rSheet.rows.Count, "AO").End(xlUp).Row returns a Long. The reason of the error message may be here as Set autodeclares Object that can not be used for For
  2. use Option Explicit
  3. not use rows and columns as variable name for Rows and Columns are keywords of VBA that may lead to confusions

  4. check the status after each Set whether the assignment is OK, e.g.: 

    Set columns = Table.ColumnOrder
If columns is Nothing Then <...escape from here...>
AcsErno
  • 1,597
  • 1
  • 7
  • 10