Copy KOB5 Grid to Variable and later copy to Excel

[Former Member]

Hi Experts,

I have a situation, where I go to KOB5 by inputting a Order number in SAP, and later copy the contents of a Grid to Excel. This excercise continues for Many Records(ie., Order Numbers). Is There a way around where the Order Numbers are picked from Excel and the output is pasted in Excel back.

I have written a script, it is picking up the Order numbers from Excel Sheet, but I am struck in pasting the Grid values back in Excel.

'===========Leave this code alone===================

REM Set up connection to the SAP screen:

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

REM Set up connection to the Excel data to be keyed into SAP:

Dim objExcel

Dim objSheet, intRow, i

Set objExcel = GetObject(,"Excel.Application")

Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").text = "KOB5"

session.findById("wnd[0]").sendVKey 0

For i = 2 to objSheet.UsedRange.Rows.Count 'Assuming there is a header row

'===========END Leave this code alone=====================

'===========Declair your variables used here one for every column in your spreadsheet==============================================

strOrder = Trim(CStr(objSheet.Cells(i, 1).Value))

'===========End Declair your variables==============================================

'<==Record actions in SAP using scripting tool and paste the code generated in this section=========================

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]/usr/ctxtP_AUFNR").text = strOrder

session.findById("wnd[0]/usr/ctxtP_DISVAR").setFocus

session.findById("wnd[0]/usr/ctxtP_DISVAR").caretPosition = 4

session.findById("wnd[0]").sendVKey 8

session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").setCurrentCell -1,""

Cell = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectAll

'====THIS IS WHERE I AM UNABLE TO COPY THE GRID TO VARIABLE====="

set ns1=createobject("WScript.shell")

ns1.AppActivate "Microsoft Excel"

objSheet.Cells(4, 2) = Cell

'====================================================================================>

Next

[script_man]

Hi csrame,

I have for you the following suggestions:

. . .
set GRID = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
GRID.setCurrentCell -1,""
ROW = grid.currentCellRow
CELL = GRID.getcellvalue (ROW, "COLUMN_NAME")
. . .

In the run you have by using Script Recorder to find out how the column in question is called correctly. Draw to a script, you want to sort by that column for example. Then you see in the recorded script, the column name.

Regards,

ScriptMan

[Former Member]

Hi ScriptMan,

Almost there, but with few glitches..

The script is taking only one value of the Grid, and putting it in Excel. My requirement is to have the whole Grid in Excel.

I tried For condition too, but no Success..... Hope you can help me out.

Hat's off to your contributions.

set GRID = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")

'RowCT = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").rowCount

GRID.setCurrentCell -1,""

ROW = grid.currentCellRow

For Row = 0 To RowCt-1

CELL = GRID.getcellvalue (ROW, "WKGRES")

Next

set ns1=createobject("WScript.shell")

ns1.AppActivate "Microsoft Excel"

objSheet.Cells(i, 2) = Cell

[script_man]

Hi csrame,

My suggestion to you is as follows:

. . .
set GRID = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
RowCT = GRID.rowCount

For Row = 0 To RowCt - 1
 GRID.setCurrentCell  ROW, "WKGRES"
 GRID.selectedRows = cstr(ROW)
 CELL = GRID.getcellvalue (ROW, "WKGRES")
 COLUMN = 2 + ROW
 objSheet.Cells(i, COLUMN) = CELL
Next

' Normally, the following 2 lines are not used
' set ns1=createobject("WScript.shell")
' ns1.AppActivate "Microsoft Excel"
. . .

Regards,

ScriptMan

Edited by: ScriptMan on Oct 21, 2010 5:42 PM

[script_man]

Hi csrame,

the purpose of this forum is that it presents the other users a solution.We dont know if your question has been answered.

P.S.It would also be nice if you could close a answered question. <removed by moderator>

Regards,

ScriptMan