How to scroll GUI table in VB

[former_member212650]

Hi

I'm having trouble with a GUI table automation through VB (GuiTableControl.)

What is the best way to run though each row in a table - if i want to extract all values in a table not

just those displayed on the screen.

eg 4 rows are display on the screen. But if the table contain more entries, how do I move the

table to the fifth row.

oidtable.verticalScrollbar.Position = 4 ' fifth row..

sometime works and other times crashes..

is there a better way to move through all items one by one...not just the ones displayed..

The rows collection , contains rows on the screen.

This can get rows but again it only work for the four rows displayed on the screen rows (0-3)

oidtable.rows.item(3).item(0).text

how do i get the fifths row in a table..

mike.

[stefan_schnell · Accepted solution]

Hello Mike,

I had the same problems and solved it in the same way as you.

The example is here: http://scn.sap.com/docs/DOC-26251

I use this code to actualize the grid:

'-Each 32 lines actualize the grid------------------------

  If i Mod 32 = 0 Then

    table.SetCurrentCell i, CStr(Columns(0))

  End If

Hope it helps.

Cheers

Stefan

[former_member212650 · Accepted solution]

Thanks I was asking about ,

a GUItablecontrol not a GuiGridView. The GuiGridView control has method SetCurrentCell   GUItablecontol dosen't have this method. ,

eg. There are numerous such GUItablecontrols in the 'bp' transaction (eg the id 'tab') . There are GUItablecontrols there which only display a few rows  , we need to go through all rows to see if a value is already there before inserting a new record - but how? You seem to only be able to get access to

the ones displayed.

also see this post similar question

http://scn.sap.com/message/13888580#13888580

question not answered?

Thanks for trying anyway...

[stefan_schnell · Accepted solution]

Hello mic,

sorry for this misunderstanding.

Hope the following snippet could help:

Set WshShell = WScript.CreateObject("WScript.Shell")

'Get the number of rows in the table

i = session.findById("wnd[0]/usr/tbl/TEST").RowCount

'Go to each row and read the field 0,0

For j = 1 To i

  'With Shift+Arrow Down you can scroll through the table

  WshShell.AppActivate "Title of the SAP Window"
  WshShell.SendKeys "+{DOWN}"
  WScript.Sleep 125

  'Read the content of the field 0,0, in my case a key

  fld = session.findById("wnd[0]/usr/tbl/TEST-FIELD[0,0]").Text

  'If the content of field is identical to the line before,
  'we reach the end of the entries

  If fld = vfld Then
    MsgBox CStr(j) & " entries"
    Exit For
  End If

  vfld = fld

Next

Let us know the result.

Cheers

Stefan

[former_member212650 · Accepted solution]

Thanks

I couldn' t  get the above code to work in VBA (excel) but it does work as a stand alone script (vbs).

If you can get it to work in vba (excel) that would be great. !

to get WshShell to work in vba

'set a reference to Windows Script Host Object Model

Dim WshShell As New IWshRuntimeLibrary.WshShell

WScript.Sleep 125 ' i could get to work in VBA..

however this code - is a sleep alternative..

Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)
'Use the following syntax to call the Sleep function:

Sub Sleeps(m As Long)
Sleep m End Sub

But if you can get GUITableControlit to work in vba that would be good..?

thanks again..

[stefan_schnell · Accepted solution]

Hello mic,

try this, hope it works in your case. I check it with Excel 2007 and I works fine on my system.

Option Explicit

Private Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hWnd As Long) As Long
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Test()

  Dim wsh As WshShell

  Dim gui As Object
  Dim app As GuiApplication
  Dim con As GuiConnection
  Dim ses As GuiSession
  Dim i, j As Long
  Dim fld, vfld As String
  Dim hWnd As Long
 
  Set gui = GetObject("SAPGUI")
  Set app = gui.GetScriptingEngine()

  Set wsh = CreateObject("WScript.Shell")

 
  If IsObject(app) And IsObject(wsh) Then
 
    Set con = app.Connections(0)
    Set ses = con.Sessions(0)
   
    If IsObject(con) And IsObject(ses) Then
   
      i = ses.FindById("wnd[0]/usr/tbl/TABLE").RowCount
     
      hWnd = ses.ActiveWindow.Handle
     
      For j = 1 To i
 
        SetForegroundWindow hWnd
        wsh.SendKeys "+{DOWN}"
        Sleep 250
       
        fld = ses.FindById("wnd[0]/usr/tbl/TABLE[0,0]").Text
       
        If fld = vfld Then
          MsgBox CStr(j) & " entries"
          Exit For
        End If
        vfld = fld
             
      Next
   
      Set ses = Nothing
      Set con = Nothing
    End If
   
    Set app = Nothing
  End If

End Sub

Cheers

Stefan

[former_member212650 · Accepted solution]

Hi Stefan

Yes this solution worked ! Well done you are definitely a automation guru - thanks for your help...

[Former Member · Accepted solution]

Hello Stefan,

I would like to write a vbscript code to search for entries in table.

I copied your code from previous replies but it's not working.

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
Dim WshShell
Dim WScript
Set WshShell = WScript.CreateObject("WScript.Shell")
 
'Get the number of rows in the table
i = session.findById("wnd[0]/usr/tblSAPLQPAATC_PLMK").RowCount
 
'Go to each row and read the field 0,0
For j = 1 To i
 
  'With Shift+Arrow Down you can scroll through the table
  WshShell.AppActivate "Title of the SAP Window"
  WshShell.SendKeys "+{DOWN}"
  WScript.Sleep 125
 
  'Read the content of the field 0,0, in my case a key
  fld = session.findById("wnd[0]/usr/tblSAPLQPAATC_PLMK/txtPLMKB-KURZTEXT[0,0]").Text
  'If the content of field is identical to the line before,
  'we reach the end of the entries
  If fld = vfld Then
    MsgBox CStr(j) & " entries"
    Exit For
  End If
  vfld = fld
 
Next

Kindly please help me in right direction.

Thanks!

[stefan_schnell · Accepted solution]

Hello,

tell me a little bit more about your transaction code, your program and the screen do you use. I don't know QM "Inspection characteristics". I see only the table name PLMK (Prüfplanmerkmale) and the field Kurztext (Kurztext zum Prüfmerkmal).

At first you must switch in the line

WshShell.AppActivate "Title of the SAP Window"

the string argument with your individual window title.

The example code above compares two lines and you want to search for entries in a table. That are different requirements. Think about the different approaches.

Cheers

Stefan

[Former Member · Accepted solution]

Sorry I forgot to include more information.

Program name: SAPLQPAA

Dynpro: 0150

Screen title : Display Inspection Plan: Characteristic Overview

Transaction code: QP03

In this transaction we list all the test (insp. character) that needs to be recorded to post approve OR reject (usage decision)

we have more than 200 tests listed in the inspection plans and searching for exact tests is manual work.

1) SAP in built search icon is disabled in this transaction (QP03).

2) its table and not grid view. I came to know about gridview relevant vbscript codes from you in this form and using them activley ..thanks to you. But how do we put vbscript code to search lets say inspection character from table view in QP03.

I really appreciate you taking time to answer my stupid questions.

Thank you!!

[stefan_schnell · Accepted solution]

Hello,

sorry but TAC QP03 doesn't exists on my test system. So I take TAC OAC0, it is also a table too.

To search one entry the following code, based on the code discussed here in this thread, works. I am searching for the entry CUSTSTRUCT:

'-Begin-----------------------------------------------

  '-Directives----------------------------------------
    Option Explicit

  '-Variables-----------------------------------------
    Dim SapGuiAuto, application, connection, session
    Dim WshShell, i, j, fldtxt

  '-Main----------------------------------------------
    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

    Set WshShell = CreateObject("WScript.Shell")

    i = session.findById("wnd[0]/usr/tblSAPLSCMS_CREPC_SREP").RowCount

    For j = 1 To i

      fldtxt = session.findById("wnd[0]/usr/tblSAPLSCMS_CREPC_SREP/txtI_SREP-CREP_ID[0,0]").Text

      If fldtxt = "CUSTSTRUCT" Then
        Exit For
      End If

      WshShell.AppActivate _

        "Display Content Repositories: Overview"
      WshShell.SendKeys "+{DOWN}"

    Next

    MsgBox fldtxt & " found in line " & CStr(j)

'-End-------------------------------------------------

This is the result:

Hope it helps. Let us know the results.

Cheers

Stefan

[Former Member · Accepted solution]

I'm getting an error message "Object required: WScript" for the above script and the one I found here..http://scn.sap.com/thread/3336725

I modified your previous program like this..

'-Begin-------------------------------------------------------------------

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
 
 Dim ContentToFind
ContentToFind = InputBox("Enter search text") 
'Set WShell = CreateObject("WScript.Shell")
 'Set WShell = WScript.CreateObject("WScript.Shell")
'  session.findById("wnd[0]/tbar[0]/okcd").text = "/nSU01D"
'  session.findById("wnd[0]/tbar[0]/btn[0]").press 
'  session.findById("wnd[0]/usr/ctxtUSR02-BNAME").text = "SSHUGA"
'  session.findById("wnd[0]/tbar[1]/btn[7]").press 
'  session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG").select
 
  table = "wnd[0]/usr/tblSAPLQPAATC_PLMK"
  Set tab = session.findById(table)
 
  'ContentToFind = "& Input"
  ContentToFind = "Trait - YG purity"
 
  For i = 0 To tab.Rows.Count - 1
 
    '-Search for entry in the table-------------------------------------
      If session.findById(table & "/txtPLMKB-KURZTEXT[11," & CStr(i) & _
        "]").Text = ContentToFind Then
        session.findById(table).verticalScrollbar.position = "" & CStr(i) & ""
        'session.findById("wnd[0]/usr/txtRQPAS-ENTRY_ACT").text = "" & CStr(i) & ""
        session.findById("wnd[0]").sendVKey 0
        fld = session.findById(table & "/txtPLMKB-KURZTEXT[11," & CStr(i) & _
        "]").Text
        MsgBox fld & " Found it"
        Exit For
      End If
 
    '-Send key down-----------------------------------------------------
'      WShell.AppActivate "Display User"
'      WShell.SendKeys "{DOWN}"
'      WScript.Sleep 250
 
    '-Send page down for the next rows, if the entry was not found------
      If i = tab.Rows.Count - 1 Then

'Page down is 82
        session.findById("wnd[0]").sendVKey 82
        Set tab = session.findById(table)
        i = 0
      End If
 
    '-Exit if first empty row reach-------------------------------------
      If Trim(session.findById(table & "/txtPLMKB-KURZTEXT[11," & _
        CStr(i) & "]").Text) = "" Then
        Exit For
      End If
 
  Next
 
'-End-------------------------------------------------------------------

commented out WShell and using sendVKey 82.

This script runs but is not searching for text "Trait - YG purity" but returns some random text.

I think its not scrolling window right.

Thanks a lot!

[stefan_schnell · Accepted solution]

Hello,

try this:

'-Begin-----------------------------------------

  '-Directives----------------------------------
    Option Explicit
   
  '-Variables-----------------------------------
    Dim WshShell
   
  '-Main----------------------------------------
    Set WshShell = CreateObject("WScript.Shell")
    If IsObject(WshShell) Then
      MsgBox "It works"
      Set WshShell = Nothing
    Else
      MsgBox "Can't initialize WScript.Shell"
    End If

'-End-------------------------------------------

Tell us the result.

Cheers

Stefan

[Former Member · Accepted solution]

I get a popup "It works"

Not sure why I'm getting object required: WScript error.

Thanks Stefan.

[stefan_schnell · Accepted solution]

Hello,

did you start your script via a double click on the file in the Windows Explorer or via playback in the SAP GUI for Windows? I never use the playback function. I use always the double click with the correct connection and session number (red marked).

Set connection = application.Children(0)

Set session = Connection.Children(0)

Try it and let us know the results.

Cheers

Stefan

[Former Member · Accepted solution]

My way of executing .vbs is to drag .vbs file and drop it on QP03 transaction SAP screen.

Double clicking .vbs file opens that file using VbsEdit.

Program fails even if I use play back function of the SAP GUI.

Sorry for asking this novice question but what's the meaning of 0 below here?

Set connection = application.Children(0)

Set session = Connection.Children(0)

what happens if I have 1..

Set connection = application.Children(1)

Set session = Connection.Children(1)

Thank you!

[stefan_schnell · Accepted solution]

Hello,

you can open different connections to an SAP system or to different SAP systems. Each connection has a number, e.g. you connect to system NSP and NSQ, NSP is connection 0 and NSQ is connection 1. With each connection you can open different sessions. Each session has also a number, e.g.0 for the first session, 1 for second etc. You see the session number (+ 1) in the status bar.

You need the definitve number in your code if you don't use drag'n'drop or SAP scripting recorder. On this way you identify your target connection and session where do you want to execute your SAP GUI script.

Cheers

Stefan

[Former Member · Accepted solution]

Sorry for my delayed response..as I was away for thanks giving holiday.

My QP03 screen looks like this….

I’m trying to search for text from column Short text insp. char

I modified your code to collect search text dynamically via popup.

But my search is not yielding right text or entry line.

Here is my code so far…

'-Begin-----------------------------------------------

'-Directives----------------------------------------
Option Explicit

'-Variables-----------------------------------------
Dim SapGuiAuto, application, connection, session
Dim WshShell, i, j, fldtxt
Dim ContentToFind


'-Main----------------------------------------------
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

Set WshShell = CreateObject("WScript.Shell")

    ContentToFind = InputBox("Enter search text") 

    i = session.findById("wnd[0]/usr/tblSAPLQPAATC_PLMK").RowCount

For j = 1 To i

      fldtxt = session.findById("wnd[0]/usr/tblSAPLQPAATC_PLMK/txtPLMKB-KURZTEXT[11,0]").Text

If fldtxt = ContentToFind Then
Exit For
End If

      WshShell.AppActivate _
"Txn - QP03 Display Inspection Plan: Characteristic Overview"
      WshShell.SendKeys "+{DOWN}"

Next

MsgBox fldtxt & " found in line " & CStr(j)

'-End-------------------------------------------------

[former_member212650 · Accepted solution]

Hi just an off topic question. How do paste syntax highlighted VB in color as you are doing ..I'm using excel VBA? What are you copying  from..

[Former Member · Accepted solution]

Im using Vbsedit  to run vbs code. Vbsedit automatically highlights syntax.

Thanks.

[former_member213011]

I recently faced with a similar situation with the GuiTableControl.

A colleague wanted to extract data from a table which only have 2 visible rows whereas there are 2000+ rows in total. My first thought, like yours, was to use VerticalScrollBar.Position to scroll down the table, but the script crashes after it completed the 1st loop.

After trial and error with different strategies, my VBA script finally works. The key is to re-declare the GuiTableControl object after each scroll. It seems that whenever the VerticalScrollBar position changes, the table is re-loaded is SAP and all the cells are reset in reference to the VerticalScrollBar position. This is probably why SAP throw an exception when trying to get the value from the table after the 1st loop - the GuiTableControl declared initially no longer exist in memory after the scroll.

Anyway, my solution steps are basically as follow:

1. Declare the GuiApplication, GuiConnection and GuiSession objects as usual for initialization.
2. Declare the GuiTableControl object e.g. sapTable = session.findbyid("theTableID"), then get the sapTable.VisibleRowCount and sapTable.RowCount.
3. Set the looping parameters to start from whichever rows but maximum loops should be no more than RowCount / VisibleRowCount.
4. Get the data using sapTable.GetCell(0,0).Text to maximum sapTable.GetCell( VisibleRowCount - 1, Columns.Count - 1).Text
5. Set sapTableVerticalScrollBar.Position = sapTable.VerticalScrollBar.Position + sapTable.VisibleRowCount.
6. Re-declare the sapTable object using the same "theTableID" in step 2 above.
7. Loop step 4, 5 and 6 until finish.

This way, we eliminate the need to use VBS for Sendkey and only needs VBA to do the job.

Thanks,

Sayuti

[stefan_schnell]

Hello Sayuti,

thanks for sharing this interesting information

Cheers

Stefan

[Former Member]

I created a code that reads all data from a GuiTableControl into a CSV File. Maybe this helps you to learn how to look through a Table like this:

'Variablendeklaration

Dim TableID as String

Dim oTableControl As SAPFEWSELib.GuiTableControl

Dim CSV As Object

Dim iRow As Long

Dim Rows As Long

Dim vRows As Integer

Dim iCol As Integer

Dim Cols As Integer

Dim ScrollBarPosition_o As Long

'Table ID initialisieren

TableID = "hier die ID eingeben"

'Das GuiTableControl in die Objektvariable "oTableControl" initialisieren

Set oTableControl = Session.FindById(TableID)

'Tabellengröße ermitteln

Rows = oTableControl.RowCount - 1

Cols = oTableControl.ColumnCount - 1

vRows = oTableControl.VisibleRowCount

'Initialisierung des Objekts zur Erstellung der CSV-Datei

Set CSV = _

    CreateObject("Scripting.FileSystemObject"). _

    CreateTextFile("C:\test.csv", True)

'Alle Spaltennamen in die erste Zeile der CSV-Datei schreiben

For iCol = 0 To Cols

    CSV.Write oTableControl.Columns.Item(iCol).Title & _

        IIf(iCol < Cols, ";", "")

Next

'Daten in die folgenden Zeilen bertragen

Dim Value As String

Do

    For iRow = 0 To vRows

        If Not iRow = vRows Then oCSV.WriteLine ""

        For iCol = 0 To Cols

        Err.Clear

        Value = oTableControl.GetCell(iRow, iCol).Text

            If Err.Number = 0 Then

                    If IsNumeric(Value) And Not isSAPDate(Value) Then

                Value = IIf(CDbl(Value) Mod 2 = 0, _

                                CLng(Value), CDbl(Value))

                    End If

            oCSV.Write Value & IIf(iCol < Cols, ";", "")

               End If

            Next

    Next

        'Scrollbar weitersetzen und alten Wert speichern

    ScrollBarPosition_o = oTableControl.VerticalScrollbar.Position

    oTableControl.VerticalScrollbar.Position = _

        oTableControl.VerticalScrollbar.Position + vRows

        'oTableControl neu initialisieren

    Set oTableControl = Session.FindById(TableID)

Loop While ScrollBarPosition_o < oTableControl.VerticalScrollbar.Position

CSV.Close

Regards from Regensburg,

  Max

[former_member213011]

Dear Maximilian,

This is basically how my steps work as describe earlier.

Thanks for putting in the Columns headers in the codes. I forgot to mention it in my earlier reply.

Instead of using CSV, I record the data directly in my Worksheet using Activecell and Offset. The looping technique is For...Next instead of the Do...While.

Just curious, why do you use Err.Clear in your codes? Shouldn't there be On Error Resume Next at the beginning as well?

Thanks

Sayuti

[Former Member]

I use the Exceptionhandling because sometimes some Cells can kind of be "locked". If Value = GetCell().Text is used on one of there, an exception will be thrown and Value will contain the "old" Value from the last working Cell.

Of course you need to add On Error Resume Next in the beginning. Its not necessary to Use Err.Clear and If Err.Number = 0, you can also set Value to "" after writeing it to the CSV File.

Maybe like this:

'Exception Handling

On Error Resume Next

'Variablendeklaration

Dim TableID as String

Dim oTableControl As SAPFEWSELib.GuiTableControl

Dim CSV As Object

Dim iRow As Long

Dim Rows As Long

Dim vRows As Integer

Dim iCol As Integer

Dim Cols As Integer

Dim ScrollBarPosition_o As Long

'Table ID initialisieren

TableID = "hier die ID eingeben"

'Das GuiTableControl in die Objektvariable "oTableControl" initialisieren

Set oTableControl = Session.FindById(TableID)

'Tabellengröße ermitteln

Rows = oTableControl.RowCount - 1

Cols = oTableControl.ColumnCount - 1

vRows = oTableControl.VisibleRowCount

'Initialisierung des Objekts zur Erstellung der CSV-Datei

Set CSV = _

    CreateObject("Scripting.FileSystemObject"). _

    CreateTextFile("C:\test.csv", True)

'Alle Spaltennamen in die erste Zeile der CSV-Datei schreiben

For iCol = 0 To Cols

    CSV.Write oTableControl.Columns.Item(iCol).Title & _

        IIf(iCol < Cols, ";", "")

Next

'Daten in die folgenden Zeilen bertragen

Dim Value As String

Do

    For iRow = 0 To vRows

        If Not iRow = vRows Then oCSV.WriteLine ""

        For iCol = 0 To Cols

       Value = oTableControl.GetCell(iRow, iCol).Text

        If IsNumeric(Value) And Not isSAPDate(Value) Then

          Value = IIf(CDbl(Value) Mod 2 = 0, _

                        CLng(Value), CDbl(Value))

          oCSV.Write Value & IIf(iCol < Cols, ";", "")

        End If

       Value =  ""

    Next

        'Scrollbar weitersetzen und alten Wert speichern

    ScrollBarPosition_o = oTableControl.VerticalScrollbar.Position

    oTableControl.VerticalScrollbar.Position = _

        oTableControl.VerticalScrollbar.Position + vRows

        'oTableControl neu initialisieren

    Set oTableControl = Session.FindById(TableID)

Loop While ScrollBarPosition_o <oTableControl.VerticalScrollbar.Position

CSV.Close

[Former Member]

Hello Maxmilian,

Can this code be converted to vbscript? I already have sevral vbscripts that interact with Guixt and input assistant and would like vbscript over VBA.

Thanks for your time and efforts.