How to scroll GUI table in VB

[Former Member]

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