on 03-01-2013 2:33 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
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
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..
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
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!
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
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!!
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
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!
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
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
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!
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
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-------------------------------------------------
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:
This way, we eliminate the need to use VBS for Sendkey and only needs VBA to do the job.
Thanks,
Sayuti
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.