CODEWITHSUNDEEP
vbaexcelsap-gui
Sevpoint
Sevpoint

Reputation: 213

Find each cell value of a range in SAP table (gridview)

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)?

Upvotes: 0

Views: 8379

Answers (2)

ScriptMan
ScriptMan

Reputation: 1625

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

Upvotes: 2

AcsErno
AcsErno

Reputation: 1615

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...>

Upvotes: 0

Related Questions