8

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?)
Dharman
  • 30,962
  • 25
  • 85
  • 135
Eder
  • 1,874
  • 17
  • 34
  • 1
    What happens if you place a breakpoint somewhere and look through the data contained in the vba code to find what you are looking for? See [here](http://scn.sap.com/docs/DOC-39696) for a detailed explanation. I've had significant success using this approach. – enderland Jun 07 '13 at 02:18
  • 1
    Are there any ABAP programmers at the place where you work? They could wrap the repetitive task that you need to do in an RFC function or write the code and expose it as a Web Service. You can connect to SAP via .NET connector, but you will still be limited to the exposed SAP BAPi's (which is not a bad thing as those are at least well documented and supported). – Esti Jun 07 '13 at 03:08
  • 1
    What are you actually trying to accomplish, and does it require data that doesn't live in SAP? I'm not clear why you'd do this in Excel/VBA instead of ABAP with a BDC. – Bryan Cain Jun 07 '13 at 13:36
  • @Esti: Unfortunately at our facility We don't have any ABAP programmer, only at the corporate level, but they refuse to accept these kind of requests. So, I will try .NET connector... – Eder Jun 11 '13 at 14:51
  • @BryanCain: It only requires the data for fill up the TCode requirements. I haven't heard of BDC but I will try it out as soon as I have the privileges to use to ABAP and BDC. – Eder Jun 11 '13 at 15:03
  • @enderland: Actually that was how I noticed the problem with the HorizontalScrollbar and VerticalScrollbar position. – Eder Jun 11 '13 at 15:07
  • You could try SAP JCO which is the SAP Java Connector. It allows you to access tables etc through a Java API. You may however need a SAP partner number etc in order to get it. – Davos555 Jul 08 '13 at 15:12

2 Answers2

2

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

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
Tarik
  • 10,810
  • 2
  • 26
  • 40
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

Caio Jordão Calisto
  • 33
  • 4