-1

I am writing a VBA script to check all measuring point, who are part of the selection have inspection orders. For this I need to extract a large amount of measering point (ca. 2721) and use them as an input in another transaction.

The problem I have is: What is the most efficient way to extract / export a large amount of data from SAP in a way that I can paste them as an input in SAP?

I can make an export, but I cannot access the exported Excel-file through VBA. I can loop through the datarows and copy every single cell, but this is very slow, as shown below.

row = 1
For i = 1 To session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell").RowCount - 2
    session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell").CurrentCellRow = i    
    ThisWorkbook.Sheets("Output2").Cells(row, 1) = session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell").GetCellValue(i - 1, "TPLNR")
    ThisWorkbook.Sheets("Output2").Cells(row, 2) = session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell").GetCellValue(i - 1, "POINT")
    row = row + 1
Next
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
user12451009
  • 33
  • 3

1 Answers1

1

You should

  • use an array - instead of writing directly to the sheet
  • use with to not call session.FindByID... multiple times
Dim arrData As Variant, i As Long

With session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell")
    
    ReDim arrData(1 To .RowCount - 2, 1 To 2)
    
    For i = 1 To .RowCount - 2
        .CurrentCellRow = i
        arrData(i, 1) = .GetCellValue(i - 1, "TPLNR")
        arrData(i, 2) = .GetCellValue(i - 1, "POINT")
    Next
    
End With

With ThisWorkbook.Sheets("Output2")
    .Resize(UBound(arrData, 1), 2).Value = arrData
End With

But maybe it's worth to look into Powerquery to achieve what you want.

Ike
  • 9,580
  • 4
  • 13
  • 29