Reading out grid to excel

[Former Member]

Hello,

As I'm new to scripting in SAP (and programming in general) I'm trying to read out an sap grid to excel.

Below is my code which is (partly) working, however it only fills 96 lines in excel.

I tried 'SapGuiGridScrollToRow' to scroll every 90 lines read because I thought it had to do with some 'preloading' of the grid. However this function always gives me an error, so I put it in comments.

Is there a limit on the number of lines that can be read from a grid?

How can I overcome this?

Are there more efficient ways to do this?

Dim xclRow
Dim sapRow
Dim artNr

'Excel
Set xclapp = CreateObject("Excel.Application")
Set xclwbk = xclapp.Workbooks.Add()
'	With xclwbk
'        .Title = "ArtSer"
'        .SaveAs Filename = "artser.xls"
'    End With
Set objSheet = xclwbk.Sheets(1)

xclapp.Visible = true

objSheet.cells(1,1) = "Artikelnummer" 'MATNR
objSheet.cells(1,2) = "Serienummer" 'SERNR

xclRow = 2


	'session.findbyId(wnd[0]------).Value = objsheet.cells(1,1).value 'for example Never use the Range Object---

'SAP
sapRow = 0

If Not IsObject(application) Then
	Set SapGuiAuto  = GetObject("SAPGUI")
	Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
	Set connection = application.Children(0)
End If
If Not IsObject(session) Then
	Set session    = connection.Children(0)
End If
If IsObject(WScript) Then
	WScript.ConnectObject session,     "on"
	WScript.ConnectObject application, "on"
End If
session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").text = "iq09"
session.findById("wnd[0]").sendVKey 0
	'Artikelnummer Fabricom
	artNr = Inputbox("Geef een artikelnummer:","Artikelnummer")
	session.findById("wnd[0]/usr/ctxtMATNR-LOW").text = artNr
	session.findById("wnd[0]/usr/ctxtMATNR-LOW").setFocus
	'session.findById("wnd[0]/usr/txtMATNR-LOW").caretPosition = 6
	session.findById("wnd[0]").sendVKey 8

Set GRID1 = session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell")
'msgbox(GRID1.rowcount)

for sapRow = 0 to GRID1.rowCount - 1
'session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").GetCellValue(sapRow,"MATNR")<>""
objSheet.cells(xclRow,1) = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").GetCellValue(sapRow,"MATNR")
objSheet.cells(xclRow,2) = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").GetCellValue(sapRow,"SERNR")
'sapRow = sapRow+1
xclRow = xclRow+1

'If sapRow Mod 90 Then
'	SapGuiGridScrollToRow "wnd[0]/usr/cntlGRID1/shellcont/shell", sapRow
'End If

next

MsgBox("Done")

[script_man · Accepted solution]

Hi Tim,

welcome to the forum. As a beginner, you do have an amazing amount. I have a suggestion for you. You can try the following command:

. . .
xclRow = xclRow+1
'new command
GRID1.setCurrentCell xclRow, "MATNR"
next
. . .

Regards,

ScriptMan

[Former Member]

Hi,

I first looked through the forum which was very helpful to get me on the way.

Your suggestion put me in the right direction.

I adapted it as follows:

'Scroll cells
If  sapRow<GRID1.rowCount - 1 Then
	GRID1.setCurrentCell sapRow+1, "MATNR"
End If

As my xclRow variable starts at row 2 for excel, the readout in SAP ended before the end as the SAP grid starts at row 0.

I also first got a 'Frontend error' at the end because it tried to set the new current cell to a non-existing one, hence the if-statement.

Thanks,

Tim

[script_man]

Hi,

Sorry. I have swapped xclRow and sapRow. You have solved it but perfect.

Regards,

ScriptMan

[Former Member]

For reference the complete and working script:

Dim xclRow
Dim sapRow
Dim artNr
'Excel
Set xclapp = CreateObject("Excel.Application")
Set xclwbk = xclapp.Workbooks.Add()
' With xclwbk
'        .Title = "ArtSer"
'        .SaveAs Filename = "matser.xls"
'    End With
Set objSheet = xclwbk.Sheets(1)
xclapp.Visible = true
objSheet.cells(1,1) = "Materialnumber" 'MATNR
objSheet.cells(1,2) = "Serial number" 'SERNR
xclRow = 2

 'session.findbyId(wnd[0]------).Value = objsheet.cells(1,1).value 'for example Never use the Range Object---
'SAP
sapRow = 0
If Not IsObject(application) Then
 Set SapGuiAuto  = GetObject("SAPGUI")
 Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
 Set connection = application.Children(0)
End If
If Not IsObject(session) Then
 Set session    = connection.Children(0)
End If
If IsObject(WScript) Then
 WScript.ConnectObject session,    "on"
 WScript.ConnectObject application, "on"
End If
'session.findById("wnd[0]").maximize
session.findById("wnd[0]").iconify
session.findById("wnd[0]/tbar[0]/okcd").text = "iq09" 'Use transaction IQ09
session.findById("wnd[0]").sendVKey 0
 artNr = Inputbox("Enter Material Number (Serial):","Materialnumber")
 session.findById("wnd[0]/usr/ctxtMATNR-LOW").text = artNr
 session.findById("wnd[0]/usr/ctxtMATNR-LOW").setFocus
 session.findById("wnd[0]").sendVKey 8
Set GRID1 = session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell")
'msgbox(GRID1.rowcount)
for sapRow = 0 to GRID1.rowCount - 1
'session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").GetCellValue(sapRow,"MATNR")<>""
'objSheet.cells(xclRow,1) = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").GetCellValue(sapRow,"MATNR")
objSheet.cells(xclRow,1) = GRID1.GetCellValue(sapRow,"MATNR")
'objSheet.cells(xclRow,2) = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").GetCellValue(sapRow,"SERNR")
objSheet.cells(xclRow,2) = GRID1.GetCellValue(sapRow,"SERNR")
'sapRow = sapRow+1
xclRow = xclRow+1
'Scroll cells
If  sapRow Mod 90 And sapRow<GRID1.rowCount - 1 Then
 GRID1.setCurrentCell sapRow+1, "MATNR"
End If
'If sapRow Mod 90 Then
' SapGuiGridScrollToRow "wnd[0]/usr/cntlGRID1/shellcont/shell", sapRow
'End If
next
MsgBox("Done")