I keep getting bad index error for below code..
Sub GetDocumentedGoodsMovement()
Dim funcs As SAPFunctions
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet3")
Set sapConn = CreateSapFunctions()
If sapConn Is Nothing Then
Exit Sub
End If
Set objRfcFunc = sapConn.Add("RFC_READ_TABLE")
Set objQueryTab = objRfcFunc.Exports("QUERY_TABLE")
Set objDelimiter = objRfcFunc.Exports("DELIMITER")
Set objRowCount = objRfcFunc.Exports("ROWCOUNT")
objRowCount.Value = "99999999"
Set objOptTab = objRfcFunc.Tables("OPTIONS")
Set objFldTab = objRfcFunc.Tables("FIELDS")
Set objDatTab = objRfcFunc.Tables("DATA")
objQueryTab.Value = "AUFM" 'TABLE
objDelimiter.Value = "|" 'DELIMITER
objOptTab(objOptTab.RowCount, "TEXT") = "AUFNR EQ '000009999999"' AND BWART EQ '261'" 'FILTERS FOR VIEW.
objFldTab.FreeTable
objFldTab.AppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "AUFNR" 'FIELD TO DISPLAY
objFldTab.AppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "MBLNR" 'FIELD TO DISPLAY
objFldTab.AppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "MATNR" 'FIELD TO DISPLAY
objFldTab.AppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "LGORT" 'FIELD TO DISPLAY
objFldTab.RAppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "BWART" 'FIELD TO DISPLAY
objFldTab.AppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "SHKZG" 'FIELD TO DISPLAY
If objRfcFunc.Call = False Then
MsgBox objRfcFunc.Exception
End If
Dim objDatRec As Object
Dim objFldRec As Object
For Each objDatRec In objDatTab.Rows
For Each objFldRec In objFldTab.Rows
ws.Cells(objDatRec.Index + 1, objFldRec.Index) = Mid(objDatRec("WA"), objFldRec("OFFSET") + 1, objFldRec("LENGTH"))
Next
Next
End Sub
Error occurs at line:
objOptTab(objOptTab.RowCount, "TEXT") = "AUFNR EQ '000009999999"' AND BWART EQ '261'"
The error are as follows:
"Run-time error '-2147352565 (8002000b)':
Bad index
Are there any pointers as to why this error occurs? The following breakdowns are already done to try to locate the source of the error (without any luck so far):
To answer my own question, the error occured due to missing row in objOptTab
.
To fix the error, I added this row:
objOptTab.AppendRow
Complete, working code:
Sub GetDocumentedGoodsMovement()
Dim funcs As SAPFunctions
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet3")
Set sapConn = CreateSapFunctions()
If sapConn Is Nothing Then
Exit Sub
End If
Set objRfcFunc = sapConn.Add("RFC_READ_TABLE")
Set objQueryTab = objRfcFunc.Exports("QUERY_TABLE")
Set objDelimiter = objRfcFunc.Exports("DELIMITER")
Set objRowCount = objRfcFunc.Exports("ROWCOUNT")
objRowCount.Value = "99999999"
Set objOptTab = objRfcFunc.Tables("OPTIONS")
Set objFldTab = objRfcFunc.Tables("FIELDS")
Set objDatTab = objRfcFunc.Tables("DATA")
objQueryTab.Value = "AUFM" 'TABLE
objDelimiter.Value = "|" 'DELIMITER
objOptTab.AppendRow
objOptTab(objOptTab.RowCount, "TEXT") = "AUFNR EQ '000009999999' AND BWART EQ '261'" 'FILTERS FOR VIEW.
objFldTab.FreeTable
objFldTab.AppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "AUFNR" 'FIELD TO DISPLAY
objFldTab.AppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "MBLNR" 'FIELD TO DISPLAY
objFldTab.AppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "MATNR" 'FIELD TO DISPLAY
objFldTab.AppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "LGORT" 'FIELD TO DISPLAY
objFldTab.RAppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "BWART" 'FIELD TO DISPLAY
objFldTab.AppendRow
objFldTab(objFldTab.RowCount, "FIELDNAME") = "SHKZG" 'FIELD TO DISPLAY
If objRfcFunc.Call = False Then
MsgBox objRfcFunc.Exception
End If
Dim objDatRec As Object
Dim objFldRec As Object
For Each objDatRec In objDatTab.Rows
For Each objFldRec In objFldTab.Rows
ws.Cells(objDatRec.Index + 1, objFldRec.Index) = Mid(objDatRec("WA"), objFldRec("OFFSET") + 1, objFldRec("LENGTH"))
Next
Next
End Sub