Skip to Content
0
Apr 21, 2020 at 10:21 AM

Most efficient way to read data from a gridview using scripting

186 Views

Hi there,

I have been trying to wrap my head around reading data from a gridview in SAP, especially when the data comes from a table for which we set multiple parameters. Everything is done via vbscript. My question are:

  1. What is the most efficient way to go through the dataset of a gridview? All the tutorials I have come across show looping through cells one by one. Is there a way to copy the whole dataset at one go to an array?
  2. What about populating multi select fields like the one from the picture? In the code below the picture I will show you I do it one value at a time. How to populate it, for instance, with data stored in an array or maybe in a file?

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]/tbar[0]/okcd").text = "se16n"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtGD-TAB").text = "/opt/vim_1head"
session.findById("wnd[0]/usr/txtGD-MAX_LINES").text = ""
session.findById("wnd[0]/tbar[1]/btn[18]").press
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,1]").setFocus
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,1]").press

'- This is where I start to populate multi select field. What I wanted to copy data from an array? How to do that?

session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,0]").text = "1477880"
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,1]").text = "1477699"
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,2]").text = "1477879"
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,3]").text = "1477785"
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,4]").text = "1477779"

'- Every other 5th line I need to press F7 to allow for further additions

session.findById("wnd[1]").sendVKey 7
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,5]").text = "1477711"
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,6]").text = "1477797"
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,7]").text = "1477881"
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,4]").text = "1477882"
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,5]").text = "1477883"
session.findById("wnd[1]").sendVKey 7
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,3]").text = "1477784"
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,4]").text = "1477785"
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[1]").sendVKey 8
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/chkGS_SELFIELDS-MARK[5,1]").selected = true
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/chkGS_SELFIELDS-MARK[5,1]").setFocus


session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/chkGS_SELFIELDS-MARK[5,10]").selected = true
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/chkGS_SELFIELDS-MARK[5,10]").setFocus


session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/chkGS_SELFIELDS-MARK[5,13]").selected = true
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/chkGS_SELFIELDS-MARK[5,13]").setFocus
session.findById("wnd[0]").sendVKey 8

Set GridView = session.findById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell")
GridView.selectAll

'- I will write down the results in json format
myFile = "C:\Users\Jakub.Konstantynowic\Documents\vim_1head.json"

Set fso = CreateObject("Scripting.FileSystemObject")   
Set file = fso.CreateTextFile(myFile, True, True)

file.WriteLine "["

'- In order to read the gridview I do it using two loops: one for rows and one for columns. Is there a better way to do that?

For i = 0 To GridView.RowCount - 1
    
    eachCellValue = "{" & vbCrLf

    For j = 0 To GridView.ColumnCount - 1
        If j < GridView.ColumnCount - 1 Then
            eachCellValue = eachCellValue & Chr(34) & GridView.GetColumnTitles(GridView.ColumnOrder(j))(0) & Chr(34) & ":" & Chr(34) & GridView.GetCellValue(i, GridView.ColumnOrder(j)) & Chr(34) & "," & vbCrLf
        Else
            eachCellValue = eachCellValue & Chr(34) & GridView.GetColumnTitles(GridView.ColumnOrder(j))(0) & Chr(34) & ":" & Chr(34) & GridView.GetCellValue(i, GridView.ColumnOrder(j)) & Chr(34) & vbCrLf & "}," & vbCrLf
        End If
    Next
    
    file.WriteLine eachCellValue
    wscript.echo "Record " & (i + 1) & " z " & GridView.RowCount
Next


eachCellValue = Mid(eachCellValue, 1, Len(eachCellValue) - 1) & "]"


file.WriteLine eachCellValue
file.Close
msgbox "Finished"

Any help would be greatly appreciated. Thanks a lot in advance and best regards to all of you. Stay healthy.

Attachments

sap.jpg (79.0 kB)