Question Re: How to scroll GUI table in VB in Additional Q&A

How to scroll GUI table in VB

Former Member — Fri, 01 Mar 2013 02:33:53 GMT

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.

Re: How to scroll GUI table in VB

Stefan-Schnell — Fri, 01 Mar 2013 11:08:18 GMT

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

Re: How to scroll GUI table in VB

Former Member — Mon, 04 Mar 2013 03:35:30 GMT

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...

Re: How to scroll GUI table in VB

Stefan-Schnell — Mon, 04 Mar 2013 16:42:11 GMT

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

Let us know the result.

Cheers

Stefan

Re: How to scroll GUI table in VB

Former Member — Tue, 05 Mar 2013 03:40:51 GMT

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..

Re: How to scroll GUI table in VB

Stefan-Schnell — Tue, 05 Mar 2013 13:57:56 GMT

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

Re: How to scroll GUI table in VB

Former Member — Thu, 07 Mar 2013 23:47:41 GMT

Hi Stefan

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

Re: How to scroll GUI table in VB

Former Member — Sun, 17 Nov 2013 02:58:57 GMT

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:

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

Re: How to scroll GUI table in VB

Stefan-Schnell — Sun, 17 Nov 2013 08:45:06 GMT

Hello Sayuti,

thanks for sharing this interesting information

Cheers

Stefan

Re: How to scroll GUI table in VB

Former Member — Mon, 18 Nov 2013 21:24:44 GMT

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.

Kindly please help me in right direction.

Thanks!

Re: How to scroll GUI table in VB

Stefan-Schnell — Tue, 19 Nov 2013 07:02:03 GMT

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

Re: How to scroll GUI table in VB

Former Member — Tue, 19 Nov 2013 07:47:41 GMT

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, ";", "")

'Daten in die folgenden Zeilen bertragen

Dim Value As String

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

'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

Re: How to scroll GUI table in VB

Former Member — Tue, 19 Nov 2013 09:32:07 GMT

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

Re: How to scroll GUI table in VB

Former Member — Tue, 19 Nov 2013 12:28:58 GMT

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, ";", "")

'Daten in die folgenden Zeilen bertragen

Dim Value As String

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 = ""

'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

Re: How to scroll GUI table in VB

Former Member — Tue, 19 Nov 2013 15:05:56 GMT

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!!

Re: How to scroll GUI table in VB

Former Member — Wed, 20 Nov 2013 15:09:56 GMT

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.

Re: How to scroll GUI table in VB

Stefan-Schnell — Wed, 20 Nov 2013 21:04:27 GMT

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}"

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

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

This is the result:

Hope it helps. Let us know the results.

Cheers

Stefan

Re: How to scroll GUI table in VB

Former Member — Wed, 20 Nov 2013 22:43:59 GMT

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!

Re: How to scroll GUI table in VB

Stefan-Schnell — Fri, 22 Nov 2013 09:58:27 GMT

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

Re: How to scroll GUI table in VB

Former Member — Fri, 22 Nov 2013 15:24:02 GMT

I get a popup "It works"

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

Thanks Stefan.