SAPGUI Parse GuiUserArea

Question

Problem!

Currently at the place that I work We are trying to perform some kind of repetitive task via automatically with the usage of SAPGui, Excel & VBA. Usually, the most of the reports that We gather from SAP's TCodes are being displayed with a GuiUserArea which is not easy, neat and quick to parse.

Solution!

Anyhow I managed to parse these kind of reports depending on certain types of requirements. So, for the first time that I tried to parse any report (shown as GuiUserArea) I've came up with the idea that it would be easy to save the report as unformated text and then go and parse it using VBA (Regexes, Splits, Text Length, ...) instead of using GuiUserArea methods and properties.

Please note that using this procedure (Saving the files) is more easy and quick to parse the information, But what would be the point of using the SAPGUI object just to save the files and not to complete more complex tasks as parsing information...

Working with the GuiUserArea I've came up with the following solution:

Sub ParseSAPGUI()
    Dim objSAPGui As Object
    Dim objApplication As Object
    Dim objConnection As Object
    Dim objSession As Object
    
    If (objSAPGui Is Nothing) Then
        Set objSAPGui = GetSAPGuiObject()
        Set objApplication = GetSAPGuiScriptEngine(objSAPGui)
    End If
    
    If (objConnection Is Nothing) Then
        Set objConnection = GetSAPGuiConnection(objApplication)
    End If
    
    If (objSession Is Nothing) Then
        Set objSession = GetSAPGuiSession(objConnection)
    End If
    
    With objSession
               
        Dim intItemsShown As Integer
        Dim intVerticalScrollEndPoint As Integer
        Dim intHorizontalScrollEndPoint As Integer
    
        ' Move to the end of the GuiUserArea
        .findById("wnd[0]/usr").HorizontalScrollbar.Position = 10000
        .findById("wnd[0]/usr").VerticalScrollbar.Position = 10000
        
        ' Store end points
        intVerticalScrollEndPoint = .findById("wnd[0]/usr").VerticalScrollbar.Position
        intHorizontalScrollEndPoint = .findById("wnd[0]/usr").HorizontalScrollbar.Position
        
        ' Move to the start of the GuiUserArea
        .findById("wnd[0]/usr").HorizontalScrollbar.Position = 0
        .findById("wnd[0]/usr").VerticalScrollbar.Position = 0
        
        ' Items per page being shown
        intItemsShown = objSession.findById("wnd[0]/usr").Children.Count - 1
        
        Dim i As Integer
        Dim n As Integer
        
        For i = 0 To intVerticalScrollEndPoint Step intItemsShown
            .findById("wnd[0]/usr").VerticalScrollbar.Position = i
            intItemsShown = objSession.findById("wnd[0]/usr").Children.Count - 1
            For n = 0 To intItemsShown
                Debug.Print .findById("wnd[0]/usr").Children.ElementAt(n).Text
            Next n
        Next i
        
    End With
End Sub
              

The code shown above works perfectly except for the following statements:

• It almost parses all kind of GuiUserArea reports, except for the ones with a wide horizontal window. I am working to fix these issue but there is a lack of documentation for the SAPGUI object.

• Slow and very slow for a big amount of data (As it is supposed to be, since We are using VBA to COM Objects). Tried to work with .NET and SAPGUI object with no sucess in order to speed up the process.

But at the end of the road, it seems that SAPGUI object was not designed for these kind of tasks.

Questions!

• Do you have any other method to try to parse GuiUserArea?
• Have you tried to use a high level programming language (or even a scripting language) in order to interact with SAP system instead of VBA?
• Do you know if there is any other way to interact with SAP system rather than SAPGUI Object (Have you tried SAP .NET Connector?)

Answer 1

Your code seems buggy and slow for the following reasons:

• intItemsShown is being set, then you use it as a step within a loop and eventually modify it within the loop.
• You have a nested loop that just does a Debug.Print which might slow down your app further
• Since you seem to be traversing a hierarchy of objects, it appears to me that it would be more appropriate to use recursive calls to
  process children, children of children... Parsing SAP output structures might work but is not clean and will probably become a maintenance nightmare in the future.

My recommended alternatives

• Create a RFC that will take your parameters and return the raw data to Excel. This link might me a good start: http://www.vbforums.com/showthread.php?337408-VB-and-SAP-Integration
• Create a web service and call it from Excel VBA
• Create a web page that returns data in HTML that excel can access as a web query. See this link as a start: http://www.techrepublic.com/article/pull-data-into-microsoft-excel-with-web-queries/.

The first option requires ABAP on the SAP side. The two other options assumes that you have a web server integrated with SAP.

For i = 0 To intVerticalScrollEndPoint Step intItemsShown ' <--intItemsShown is being used here
    .findById("wnd[0]/usr").VerticalScrollbar.Position = i
    intItemsShown = objSession.findById("wnd[0]/usr").Children.Count - 1 ' and modified here
    For n = 0 To intItemsShown ' and used here again
        Debug.Print .findById("wnd[0]/usr").Children.ElementAt(n).Text
    Next n
Next i

Answer 2

First of all, thanks for posting your question. Your efforts on trying to solve your problem is helping me with mine.

Just found out some information that might help you to handle the horizontal and the vertical scroll bars

In my case, i'm working with a PFCGGuiUserArea which is an object of GuiUserArea Class. This class, as you pointed out in you example, has this two parameters, the HorizontalScrollbar and the VerticalScrollbar. From these two parameters (they were created based on a class), you have also the minimum, maximum and pagesize parameters.

In the case of the Vertical:

VerticalScrollbar Maximum: 349   '==> Total Rows of the GuiUserArea
VerticalScrollbar Minimum: 0     '==> First Row of the GuiUserArea
VerticalScrollbar Position: 1    '==> Position of the Cursor 
VerticalScrollbar Page Size: 34  '==> Total Rows per Page

In the case of the horizontal

HorizontalScrollbar Maximum: 190      '==> Total of Columns available
HorizontalScrollbar Minimum: 0        '==> First Column Available
HorizontalScrollbar Position: 0       '==> Position of the cursor
HorizontalScrollbar Page Size: 255    '==> Total Columns per Page

So, you could improve your code pointing to this properties in order to solve the wide horizontal scrolls. This will get the exact information from the system, so you don't have to pass 1000 to the position.

Best Regards, Caio