Saplicity: the Free Excel-based SAP Data Automation Suite

Enjoy this series of videos that demonstrates how to use Saplicity and explains how it uses generic VBA code to access the addressable objects in an SAP session to automatically affect data loads. Most of the videos document the creation of the tools below in order to show how to create new ones, how to customize existing ones and how to use SAP recordings with Saplicity.

See for yourself if Saplicity is right for your business. Feel free to contact us with suggestions, requests, complaints, criticisms and ideas.

Tools designed to complete many tasks like updating and extending Master Data are available as canned spreadsheets below and can be used with no programming at all. Each tool comes with an "Instructions" tab that explains how to use the tool, so getting up-to-speed only takes a few minutes.

Material Master Tools

_MaterialMasterPlantExtend.xlsm
_MaterialMasterPlantExtendWithDonors.xlsm
_MaterialMasterPlantUpdate.xlsm
_MaterialMasterSalesAreaExtend.xlsm
_MaterialMasterSalesAreaUpdate.xlsm
_MaterialMasterSalesTextLoad.xlsm
_MaterialMasterPurchaseOrderTextLoad.xlsm
_MaterialMasterDescriptionsUpdate.xlsm
_MaterialMasterAddInspectionSetup.xlsm
_MaterialMasterUpdateCharacteristics.xlsm
_MaterialMasterWarehouseExtensionCreate.xlsm
_MaterialMasterPlantMM17Updater.xlsm
_BOM_DeletePlantAssignments.xlsm Watch this be made.

Customer Master Tools

_CustomerMasterLoads.xlsm
_CustomerMasterCompanyCodeUpdate.xlsm
_CustomerMasterSalesAreaCreate.xlsm
_CustomerMasterSalesAreaUpdate.xlsm
_CustomerMasterSalesAreaTexts.xlsm
_CustomerMasterPartnerFunctions.xlsm
_CustomerMasterClassification.xlsm

Classifications and Characteristics

_CL20N_ClassificationLoad.xlsm
This tool calls the CL20N transaction to apply changes to Classifications and Characteristics on objects in SAP. Unlike the Customer Master and Material Master tools above that call XD02 and MM02 respectively, this tool calls the CL20N transaction to add or update Classification and Characteristic data on any object that can be modified using CL20N.

Custom Reporting Tools

_SE16_Extractor.xlsm
Use this tool to automate extracts from the SE16 transaction and then to knit those data into custom reports. Like all Saplicity tools, it requires no developer key or special access and, if the user has access to the SE16 transaction, he can run the tool to generate the custom report whenever he likes. It comes with an example of how to generate a custom report with Material Master Classification extract.

Esoteric Examples of Recyclable Code

_BD87_RemoveEANs.xlsm
This tool launches BD87 and drills down through the levels of the TreeView to find IDOCs that failed because the target system lacks the EAN range of the source system; the sandbox system lacked the ranges for EANs that the production server had and writing this tool took less time than manually repairing hundreds of IDOCs and was easier than coaxing the BASIS team into updating the config in order to accommodate the incoming Material Masters. Once the IDOC numbers are teased out of the GridView, the tool then automatically recreates each of the IDOCs in WE19 by using the original as a template and then stripping the EAN data off of the MARA, MARM and MEAN records.

The source code is a well-documented example of how to handle TreeView and GridView objects and can be recycled to auto-repair any type of IDOC. The WE19 interface looiks like a hierarchical GridView, but is really only GuiLabel objects spread over the usr area and is particularly cumbersome; it was solved by analyzing recordings made during development.

Connect to an SAP Session
Copy these functions and the supplementary functions below to use with a VBS recording created using SAPGUI. For help on how this works, be sure to watch this how-to video for help on using VBS recordings from SAP with Saplicity.

Sub SapConn()
    Dim session As Object
    Dim nCurrent As Long
    Dim strRange As String

    Set session = ConnectToSAP()
    If session Is Nothing Then
        'The connection was unsuccessful.
        Exit Sub
    End If

    For nCurrent = 2 To 1048576
        strRange = "B" & nCurrent
        If Range(strRange).Value <> "" Then
            'Bail if the return code is already populated.
            strRange = "A" & nCurrent
            If Range(strRange).Value = "" Then
                Range(strRange).Show
                If DoSomething(nCurrent, session) Then
                    Range("B" & nCurrent & ":" & "D" & nCurrent).Interior.Color = RGB(50, 205, 50)
                Else
                    Range("B" & nCurrent & ":" & "D" & nCurrent).Interior.Color = RGB(200, 0, 0)
                End If
            End If
        End If
    Next
    retval = MsgBox("The run has completed.", vbOKOnly, "Finished")
    Set session = Nothing
End Sub

Function ConnectToSAP() As Object
    Dim strRange As String
    Dim retval As Variant, nCurSec As Integer
    Dim Appl As Object
    Dim Connection As Object
    Dim session As Object
    Dim WshShell As Object
    Dim SapGui As Object

    Dim nCurrent As Long

    'Of course change for your file directory
    Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", 4
    Set WshShell = CreateObject("WScript.Shell")

    Do Until WshShell.AppActivate("SAP Logon ")
        retval = WaitFor(1)
    Loop

    strRange = "A1"

    '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    '*                                                                       *
    '*  The following code has been enriched to check for errors caused by   *
    '*  scripting being disabled on the server and for the server name being *
    '*  incorrect in cell A1.  It notifies the user when these issue stop    *
    '*  the code from successfully connecting to the target server.          *
    '*                                                                       *
    '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    Set SapGui = GetObject("SAPGUI")
    Application.StatusBar = "Connecting to the SAPGUI interface..."
    Set Appl = SapGui.GetScriptingEngine
    Application.StatusBar = "Connecting to the " & Range(strRange).Value & " server..."
    On Error GoTo ErrorHandler
    'Try to open the server in SAPGUI.
    Set Connection = Appl.Openconnection(Range(strRange).Value, True)
    GoTo SuccessfulConnect
ErrorHandler:
    retval = MsgBox("The SAPGUI client does not know the " & Range(strRange).Value & " server.  Please ensure that the value in cell A1 is copied verbatim from SAPGUI.", vbOKOnly, "Finished")
    Exit Function
SuccessfulConnect:
    If Connection.Children.Count = 1 Then
        'Scripting is enabled on the server.  Proceed as usual.
        Set session = Connection.Children(0)
        Application.StatusBar = "Please log into SAP."
    Else
        'Let the user know that scripting is disabled.
        retval = MsgBox("Scripting is disabled on the " & Range(strRange).Value & " server.", vbOKOnly, "Finished")
        Exit Function
    End If

    If False Then 'Change to True if you want to embed your own credentials.
        'Leave this false if you use Single Sign On (SSO)
        'session.findbyid("wnd[0]/usr/txtRSYST-MANDT").Text = "100"
        session.findbyid("wnd[0]/usr/txtRSYST-BNAME").Text = "" ' "username"
        session.findbyid("wnd[0]/usr/pwdRSYST-BCODE").Text = "" ' "password"
        session.findbyid("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"
        session.findbyid("wnd[0]").sendVKey 0 'ENTER
        'session.findbyid("wnd[0]").maximize
    End If
    
    If false then ' session.Children.Count > 1 Then
        'This might be a message from the admins about upcoming maintenance, or
        'it might be the "License Information for Multiple Logon" message.  Here
        'we check the text of the second window to see which it is.
        If session.findbyid("wnd[1]").Text = "License Information for Multiple Logon" Then
            'It is, in fact, the Multiple Logon message.  To back out select OPT3
            'for "Terminate this logon" and then throw an Exit Function.  To proceed with
            'multiple logon (which is great for speeding up MASS loads), select OPT2
            'for "Continue with this logon, without ending any other logons...".
            session.findbyid("wnd[1]/usr/radMULTI_LOGON_OPT2").Select
            'session.findbyid("wnd[1]/usr/radMULTI_LOGON_OPT2").SetFocus 'Not necessary.
            session.findbyid("wnd[1]/tbar[0]/btn[0]").press
            retval = WaitFor(0.5)
        End If
    End If

    'Wait for the SAP Easy Access user screen to appear.  This allows the user
    'to correct login details or to choose whether to continue when already
    'logged in at another session.
    Do Until InStr(session.findById("wnd[0]").Text, "SAP Easy Access") > 0
        'retval = WaitFor(1)
        Application.Wait DateAdd("s", 1, Now) ' wait for 1 second.
    Loop
    
    'Clear the StatusBar
    Application.StatusBar = False

    Set WshShell = Nothing

    Set ConnectToSAP = session
End Function

Function DoSomething(nCurrent as long,byref session as object) as Boolean 

    '**** Put the code from the recording here. ****

    'Populate the status column with the results and then return a True.
    Range("A" & nCurrent).Value = session.findById("wnd[0]/sbar").Text
    DoSomething = True
End Function

Function WaitFor(nSeconds As Single) As Boolean
    'This function just pauses the system briefly.
    Static nOneSec As Long
    Dim nCurSec As Integer, retval As Variant, nCurrent As Long
    If nOneSec = 0 Then
        nCurSec = Second(Now())
        Do While nCurSec = Second(Now())
        Loop
        nCurSec = Second(Now())
        Do While nCurSec = Second(Now())
            nOneSec = nOneSec + 1
            retval = DoEvents()
        Loop
    End If
    
    nCurrent = nSeconds * nOneSec
    'nCurrent = 0
    Do While nCurrent > 0
        nCurrent = nCurrent - 1
        retval = DoEvents()
    Loop
End Function

Function FindExcelCell(nColumn As Long, nRow As Long) As String
    Dim nPower1 As Long, nPower2 As Long
    nPower2 = 0
    If nColumn > 26 Then
        nPower2 = Int((nColumn - 1) / 26)
    End If
    nPower1 = nColumn - (26 * nPower2)
    If nPower2 > 0 Then
        FindExcelCell = Chr(64 + nPower2) & Chr(64 + nPower1) & nRow
    Else
        FindExcelCell = Chr(64 + nPower1) & nRow
    End If
End Function

Alternative method for connecting to SAPGUI

Some admins may want to first make their users logon to SAP and then run the VBA code with the opened connection. With confidence that a user can be trained to open just one SAP session and have just one window open in it then this code will serve that purpose; just replace the code above with this and test to ensure that it works.

    strRange = "A1"  'This is moot now, but used here as a placeholder.

    Set SapGui = GetObject("SAPGUI")
    Set Appl = SapGui.GetScriptingEngine
    Set Connection = Appl.Children(0)
    Set session = Connection.Children(0)

Wrapper for Quickly Working with Material Master Recordings

When producing tools for use with VBS recordings, this snippet can be used to quiickly launch the MM02 transaction, select the appropriate views, populate one or two fields and finally click the save button. This shaves seconds off of using the "_PlantUpdate.xlsm" file above and requires very little customization to work.

Start by replacing the DoSomething function in the code above with the UpdateMaterial function. The return value of the UpdateMaterial function is a boolean and the code automatically populates the A column with the outcome reported by the transaction.

Sub SapConn()
    Dim session As Object
    Dim nCurrent As Long
    Dim strRange As String

    Set session = ConnectToSAP()

    For nCurrent = 2 To 1048576
        strRange = "B" & nCurrent
        If Range(strRange).Value <> "" Then
            'Bail if the return code is already populated.
            strRange = "A" & nCurrent
            If Range(strRange).Value = "" Then
                Range(strRange).Show
                If UpdateMaterial(nCurrent, session) Then
                    Range("B" & nCurrent & ":" & "D" & nCurrent).Interior.Color = RGB(50, 205, 50)
                Else
                    Range("B" & nCurrent & ":" & "D" & nCurrent).Interior.Color = RGB(200, 0, 0)
                End If
            End If
        End If
    Next
    retval = MsgBox("The run has completed.", vbOKOnly, "Finished")
    Set session = Nothing
End Sub

Next, doctor up the code with the VBA code from the recording. If the values are on the Plant or Valuation Area then be sure to populate the RMMG1-WERKS field in the Organization Levels window, but if the fields are on the Sales Area then be sure to populate the RMMG1-VKORG and RMMG1-VTWEG.

Dont' forget to provide the description of the view(s) (from the "Select views" window) on which the field exists to ensure that tab is shown while the recording is running. Finally, include the source code from the recording that touches the specific field(s). Code that focuses the cursor on a field or positions the carat in the field is unnecessary and will only slow down the load; it can be remarked out.

Function UpdateMaterial(nCurrent As Long, ByRef session As Object) As Boolean
    UpdateMaterial = False
    Dim retval As Variant, nCurSec As Integer, nWait As Long, nCurrentField As Long, nCurrentView As Integer
    Dim strViews As String, strTabNames, strRange As String, strMATNR As String, strWERKS As String, strPSTAT As String
    Dim strVKORG As String, strVTWEG As String
    
    'Ensure that the current working cell is visible in Excel.
    strRange = "A" & nCurrent
    Range(strRange).Show
    
    
    'We know ahead of time which view(s) to open.
    strViews = "Accounting 1,Accounting 2"
    
    session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmm02"
    session.findById("wnd[0]/tbar[0]/btn[0]").press 'ENTER
    
    'Now wait for the Material Master change screen
    Do While InStr(session.findbyid("wnd[0]").Text, "Change Material (Initial Screen)") < 1
        retval = WaitFor(1)
    Loop

    retval = WaitFor(0.1)
    

    'Application.Wait Now + TimeValue("0:00:03")
    strRange = "B" & nCurrent
    strMATNR = Trim(Range(strRange).Value)
    session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = strMATNR
    'session.findById("wnd[0]/tbar[0]/btn[0]").press 'ENTER
    session.findById("wnd[0]/tbar[1]/btn[5]").press  'Press the "Select Views" button

    Do While session.findById("wnd[0]/sbar").messagetype = "W"
        'SAP tells us that the Industry sector and Material Type are auto-populated for us.  Yay!
        session.findById("wnd[0]/tbar[0]/btn[0]").press 'ENTER
        retval = WaitFor(0.2)
    Loop
    If session.findById("wnd[0]/sbar").messagetype = "E" Then
        'Something has gone terribly wrong--probably unallowed Material Number
        strRange = "A" & nCurrent 'Log goes here.
        Range(strRange).Value = session.findById("wnd[0]/sbar").Text
        Exit Function 'Return value set already to false above.
    End If
    

    Dim objChild As Object
    nCurrentView = 0
    For Each objChild In session.findById("wnd[1]").Children(1).Children(0).Children
        If InStr(strViews, objChild.Text) Then
            session.findById("wnd[1]").Children(1).Children(0).Rows(nCurrentView).Selected = True
        Else
            session.findById("wnd[1]").Children(1).Children(0).Rows(nCurrentView).Selected = False
        End If
        nCurrentView = nCurrentView + 1
    Next
    
    'Now, Page Down and tick the other Views . . .
    session.findById("wnd[1]").Children(1).Children(0).VerticalScrollBar.Position = session.findById("wnd[1]").Children(1).Children(0).Children.Count

    nCurrentView = 0
    For Each objChild In session.findById("wnd[1]").Children(1).Children(0).Children
        If InStr(strViews, objChild.Text) Then
            session.findById("wnd[1]").Children(1).Children(0).Rows(nCurrentView).Selected = True
        Else
            session.findById("wnd[1]").Children(1).Children(0).Rows(nCurrentView).Selected = False
        End If
        nCurrentView = nCurrentView + 1
    Next

    'Press the "Org. Values" button after the views are selected.
    'session.findById("wnd[1]/tbar[0]/btn[0]").press  'Press Enter.
    session.findById("wnd[1]/tbar[0]/btn[6]").press  'Press the "Org. Levels" button.

    Do While Not session.findById("wnd[1]").Text <> "Organization Levels"
        retval = WaitFor(1)
    Loop
    
    'Now populate the Organization Levels
    strRange = "C" & nCurrent
    strWERKS = Trim(Range(strRange).Value)
    'strRange = "C" & nCurrent
    'strVKORG = Trim(Range(strRange).Value)
    'strRange = "D" & nCurrent
    'strVTWEG = Trim(Range(strRange).Value)
    'Step through the objects on the "Select Views" window and populate those that exist.
    For Each objChild In session.findById("wnd[1]/usr").Children
        If objChild.changeable = True Then
            If objChild.Name = "RMMG1-WERKS" Then
                objChild.Text = strWERKS
            End If
            'If objChild.Name = "RMMG1-VKORG" Then
            '    objChild.Text = strVKORG
            'End If
            'If objChild.Name = "RMMG1-VTWEG" Then
            '    objChild.Text = strVTWEG
            'End If
        End If
    Next
    session.findById("wnd[1]/tbar[0]/btn[0]").press

    'There should be only 1 child under "session" now.  If there are three then this Material Master view is already maintained in the Plant.
    If session.Children.Count = 3 Then
        'Copy text from Popup Dialog to the log for user review.
        strRange = "A" & nCurrent 'The Plant
        'Range(strRange).Interior.Color = RGB(200, 0, 0)
        Range(strRange).Value = session.findById("wnd[2]").PopupDialogText
        retval = WaitFor(1)
        session.findById("wnd[2]/tbar[0]/btn[0]").press 'Click OK
        retval = WaitFor(1)
        session.findById("wnd[1]/tbar[0]/btn[12]").press 'Close Org Levle(s) window
        Exit Function 'Return value set already to false above.
    End If
    
    'Wait for the Material Master to appear in the title of the window.
    Do While InStr(session.findById("wnd[0]").Text, Right(strMATNR, 4)) < 1
        retval = WaitFor(1)
    Loop

    '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    '*                                                                                     *
    '*  This is where the snippet that specifically touches the field can be pasted.  The  *
    '*  recording does all of the programming so no further explanation is necessary, but  *
    '*  feel free to remark out code that focuses on fields or postions the caret.         *
    '*                                                                                     *
    '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP27/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2803/txtMBEW-BWPEI").Text = _
  Range("D" & nCurrent).Value
'session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP27/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2803/txtMBEW-BWPEI").SetFocus
'session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP27/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2803/txtMBEW-BWPEI").caretPosition = 5
session.findById("wnd[0]/tbar[0]/btn[11]").press  'Save
    
    
    '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    '*  Watch for custom user-exit.  It doesn't throw an error in the main window, but *
    '*  simply pops up and complains that Plant-specific status change is not allowed. *
    '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    If session.Children.Count > 1 Then
        'Yup, it threw the error.
        strRange = "A" & nCurrent 'The Plant
        'Range(strRange).Interior.Color = RGB(200, 0, 0)
        'Get the error message from the title of the popup instead of the status bar.
        Range(strRange).Value = session.findById("wnd[1]").Text
        
        'No need to close this Pop-up. The system puts /nMM02 in the OK Code field and moves on.
        Exit Function 'Return value set already to false above.
    End If
    
    strRange = "A" & nCurrent
    If session.findById("wnd[0]/sbar").Text = "" Then
        Range(strRange).Value = "No changes made?"
    Else
        Range(strRange).Value = session.findById("wnd[0]/sbar").Text
    End If
    UpdateMaterial = True

End Function

Anybody reading this most likely already understands Object Oriented Programming well enough, but this brief primer is provided here just the same. It is important to understand the underlying basics of VBA and SAPGUI before proceeding.

Objects in programming languages have methods and properties. These are inherited from a Class when the object is declared in a program.

A method is, in effect, a function inbuilt to the object that performs some task and often returns a value. An object's method can be called to cause it to perform some action or to retrieve internal data that is not available in an exposed property.

A property is a variable inbuilt to the object that is often addressable for the purpose of being written or read. Some properties are hidden internally and cannot be accessed at all or can only be accessed for reading and writing through a method.

A property in an object can be a numeric, string, array or any type of variable. It can even be another object and, in SAPGUI, often is. Some objects in SAPGUI are designed to contain other objects--objects like GuiSimpleContainer, GuiScrollContainer, GuiComponentCollection, GuiTab and GuiTabStrip are examples of classes that can be used to declare objects that are designed to hold other objects.

The objects that contain other objects tend to have a property called Children, but can also have Rows, Entries or Items. Usually, it is possible to declare a variable in VBA as an Object and then step through the objects in the container by using a For . . . Each loop.

Understanding the Changeable property

A common property in SAPGUI objects is Changeable. If its boolean value is set to True then the pertenent data of the object can be changed. For example, if a GuiTextField object is Changeable then its text can be changed and if a GuiCheckBox object is Changeable then it can be checked or unchecked.

VBA will throw a very ambiguous error when attempting to change the value of an object in SAPGUI if the Changeable property is set to False. The "Dealing with Check Boxes" code below includes an example of how to check for this.

Dealing with Check Boxes

Unlike the GuiTextField and GuiCTextField objects, the GuiCheckBox object has a Selected property. This boolean value can be either True or False and is readable and writeable directly.

                            'strRange is set declared in the code above and is an Excel cell  like "D8". 
                            'objChild is obtained from a For Each from the Children of a container object above.
                            strObjChild1Type = objChild1.Type
                            If objChild1.Changeable = True Then
                                If strObjChild1Type = "GuiCheckBox" Then
                                    If UCase(Trim(Range(strRange).Value)) = "X" Then
                                        objChild1.Selected = True
                                    Else
                                        objChild1.Selected = False
                                    End If
                                End If

Dealing with drop-down menus

The GuiComboBox object has a list of Entries, each of which is an object with three properties: Value (description), Pos (position) and its own Key. Setting the Key property of the GuiComboBox to the value of an Entry's Key causes that Entry to be selected in the dropdown.

In the example below, the source data has a SPRAS column with the value "ES". The Entry with a value of "Spanish" in the dropdown menu has a Key of "ES", so it is the one that is selected by the code.

Technically, the Key property of the GuiComboBox can be written directly, but this loop prevents the code from attempting to assign a Key that is not available in the list--for example, a blank value. The code does not check to see if the value was set, but that can be accomplished easily enough with a boolean variable that is set to False at the beginning of the loop, set to True when assigning the Key to the GuiComboBox and then checked at the end of the loop.

                                ElseIf strObjChild1Type = "GuiComboBox" Then
                                    'This is a dropdown menu and the value goes into the Key variable.
                                    strRange = strRange & ""
                                    
                                    For Each objChild2 In objChild1.Entries
                                        If objChild2.Key = Trim(Range(strRange).Value) Then
                                            objChild1.Key = Trim(Range(strRange).Value)
                                            Exit For
                                        End If
                                    Next

Dealing with text fields

The GuiTextField and GuiCTextField objects are mostly simple, but have a limitation: MaxLength. If the text being passed into the field is too long then the VBA code will throw an ambiguous error, so a check is performed and a popup window informs the user that the data will not fit in the field.

If the source data is too long to fit in the field then the code below checks to see if there are trailing spaces in the data. If there are trailing spaces then the code removes that and then checks the length again.

In the event that the source data ends up being too long then the code below leaves that field blank after warning the user. For Customer Master data, this is likely the NAME1 field or the STRAS field which tend to be mandatory and, when left blank, cause the transaction to throw and error which allows the user to correct the data and then perform a delta load using the corrected data.

                                Else
                                    If Len(Range(strRange).Value) > objChild1.maxlength Then
                                        If Right(Range(strRange).Value, 1) = " " Then
                                            'Trailing space!!!! Happens a lot.  Let's clear it from the source data...
                                            Range(strRange).Value = Trim(Range(strRange).Value)
                                        End If
                                    End If
                                    If Len(Range(strRange).Value) > objChild1.maxlength Then
                                        retval = MsgBox("The field value " & Range(strRange).Value & " for field " & strFieldName & " exceeds the " & objChild1.maxlength & " characters.", vbOKOnly, "Field value too long!")
                                    Else
                                        objChild1.Text = Range(strRange).Value
                                    End If
                                End If

Dealing with the Material Master Select Views(s) popup

SAPGUI utilizes the GuiTableControl to hold rows in a Rows property. Each of these rows is an object in its own right and has its own Children property that can have multiple objects including text fields, buttons, dropdowns and checkboxes.

The GuiTableControl can, based on the setting in its RowSelectMode property, on the left side is a button that is not an object in the row, but rather a way to select the row. In the case of the GuiTableControl in the Material Master Select View(s) popup, the RowSelectMode is set to "MULTIPLE_INTERVAL_SELECTION" so that it is possible to select more than one of the rows. Additionally, each row has just one object in it that is a GuiTextField with plain-text name of the view.

Each Row has a boolean Selected property that is set to True when the user clicks the button on the left of the row and then set to False. Because that property is directly accessible in the addressable object, it can be set by the VBA code and the SAPGUI interface will color the row accordingly.

This function is recycled frequently in the Material Master tools from the Downloads section. It accepts the session that was created when the user logged into SAP as an object, the Material Master number, a string containing the letters that SAP uses in the PSTAT field to tell which views are used and finally a string containing the transaction code which should probably be MM01 or MM02 based on the desired action.

The code is simple enough and well documented, but a little explaination will not hurt. The first step is to build a comma-delimited string with the plain-text names of the views and then populate that with the views specified in strPSTAT.

The second step is to launch the transaction by putting it in the "okcd" textbox at the top of the window and pressing the Enter Key. The Material Master number is typed into the text box provided and, instead of pressing the Enter Key, the "Select View(s)" button is pressed to ensure that the "Select View(s)" popup is shown when the user has saved his desired views and ticked the "View selection only on request" checkbox (image above).

The code then presses the Enter Key for each warning and returns an error code if one is thrown. Next, the code loops through each row in the GuiTableControl and, if the name of the view is in the strViews string, slects them or unselects them. It pages down and performs the loop again in the event that there are more than the 17 maximum rows allowed in this popup.

Finally, the code checks to ensure that at least one row has been selected and then presses the "Org. Levels" button instead of the Enter Key to ensure that the "Organiazational Levels" pops up despite the user ticking the "Org. levels/profiles only on request" checkbox. If no Sales Area, Plant or Warehouse view is selected then pressing the "Org. Levels" button will cause the transaction to go directly to the first view selected which would probably be "Basic data 1", "Basic data 2" or "Classification". If the transaction is MM02 and the Material Master is not extended to a desired view then it will not appear in the rows and the code will press the "Cancel" button if no rows are selected.

Function OpenMaterial(ByRef session As Object, strMATNR As String, strPSTAT As String, strTCODE As String) As String
    '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    '*                                                                                                       *
    '*  This function returns a zero-length string when successful.  Any other string is an error message.   *
    '*                                                                                                       *
    '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    Dim strViews As String, nViewsSelected As Integer
    'strViews = "Sales: General/Plant Data,Purchasing,MRP 1,MRP 2,MRP 3,MRP 4,Forecasting,Work Scheduling,General Plant Data / Storage 1,General Plant Data / Storage 2,Quality Management,Accounting 1,Accounting 2,Costing 1,Costing 2"
    'strViews = "Sales: General/Plant Data,Purchasing,MRP 1,MRP 2,MRP 3,MRP 4,Forecasting,Work Scheduling,General Plant Data / Storage 1,General Plant Data / Storage 2,Quality Management"
    OpenMaterial = ""
    strViews = ""
    nViewsSelected = 0
    If InStr(strPSTAT, "V") Then
        strViews = strViews & "Sales: General/Plant Data,"
    End If
    If InStr(strPSTAT, "E") Then
        'MRP Type provided, so create the MRP Views.
        strViews = strViews & "Purchasing,"
    End If
    If InStr(strPSTAT, "D") Then
        'MRP Type provided, so create the MRP Views.
        strViews = strViews & "MRP 1,MRP 2,MRP 3,MRP 4,"
    End If
    If InStr(strPSTAT, "P") Then
        'MRP Type provided, so create the MRP Views.
        strViews = strViews & "Forecasting,"
    End If
    If InStr(strPSTAT, "A") Then
        'MRP Type provided, so create the MRP Views.
        strViews = strViews & "Work Scheduling,"
    End If
    If InStr(strPSTAT, "L") Then
        'MRP Type provided, so create the MRP Views.
        strViews = strViews & "General Plant Data / Storage 1,General Plant Data / Storage 2,"
    End If
    If InStr(strPSTAT, "B") Then
        'MRP Type provided, so create the MRP Views.
        strViews = strViews & "Accounting 1,Accounting 2,"
    End If
    If InStr(strPSTAT, "G") Then
        'MRP Type provided, so create the MRP Views.
        strViews = strViews & "Costing 1,Costing 2,"
    End If
    If InStr(strPSTAT, "Q") Then
        'MRP Type provided, so create the MRP Views.
        strViews = strViews & "Quality Management,"
    End If
    
    session.findById("wnd[0]/tbar[0]/okcd").Text = strTCODE 'MM02
    session.findById("wnd[0]/tbar[0]/btn[0]").press 'ENTER
    
    retval = WaitFor(0.1)

    'Application.Wait Now + TimeValue("0:00:03")
    session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = strMATNR
    'session.findById("wnd[0]/tbar[0]/btn[0]").press 'ENTER
    session.findById("wnd[0]/tbar[1]/btn[5]").press 'Select Views
    
    Do While session.findById("wnd[0]/sbar").messagetype = "W"
        'SAP tells us that the Industry sector and Material Type are auto-populated for us.  Yay!
        session.findById("wnd[0]/tbar[0]/btn[0]").press 'ENTER
        retval = WaitFor(0.5)
    Loop
    If session.findById("wnd[0]/sbar").messagetype = "E" Then
        'Something has gone terribly wrong--probably unallowed Material Number
        OpenMaterial = session.findById("wnd[0]/sbar").Text
        retval = WaitFor(2) 'Let the message be seen.
        Exit Function 'Return value set already to false above.
    End If
    
    '
    Dim objChild As Object
    nCurrentView = 0
    For Each objChild In session.findById("wnd[1]").Children(1).Children(0).Children
        If InStr(strViews, objChild.Text) Then
            session.findById("wnd[1]").Children(1).Children(0).Rows(nCurrentView).Selected = True
            nViewsSelected = nViewsSelected + 1
        Else
            session.findById("wnd[1]").Children(1).Children(0).Rows(nCurrentView).Selected = False
        End If
        nCurrentView = nCurrentView + 1
    Next
    
    'Now, Page Down and tick the other Views . . .
    session.findById("wnd[1]").Children(1).Children(0).VerticalScrollBar.Position = session.findById("wnd[1]").Children(1).Children(0).Children.Count

    nCurrentView = 0
    For Each objChild In session.findById("wnd[1]").Children(1).Children(0).Children
        If InStr(strViews, objChild.Text) Then
            session.findById("wnd[1]").Children(1).Children(0).Rows(nCurrentView).Selected = True
            nViewsSelected = nViewsSelected + 1
        Else
            session.findById("wnd[1]").Children(1).Children(0).Rows(nCurrentView).Selected = False
        End If
        nCurrentView = nCurrentView + 1
    Next

    If nViewsSelected = 0 Then
        'No views were selected.  Just close the "Select View(s)" popup and report this.
        OpenMaterial = "No views selected."
        session.findById("wnd[1]/tbar[0]/btn[12]").press 'Cancel button
        Exit Function
    End If

    'Press Enter after the views are selected.
    'session.findById("wnd[1]/tbar[0]/btn[0]").press  'Press Enter
    session.findById("wnd[1]/tbar[0]/btn[6]").press   'Org Levels button
    
End Function

Handling TreeView objects

At first glance, the TreeView object can seem a little daunting. It isn't nearly so complex as it seems.

The sample code below was written to tease out a report generated by the OH11 transaction and presented in a TreeView object. It is slightly simplified when converting from TreeView to Excel, but still contains all of the pertenent information.

The code simply steps through each Node and then through each of the Items in those Nodes. The text is knit together and separated by spaces using a simple concatenation and put into the appropriate cells based on the nCurrentOutputRow variable.

Despite having no columns, the TreeView object has column headers stored as Items in the ColumnOrder property that match the headers at the top of the TreeView. In this case, there are just two and their titles are added as headers at the top of the report when processing each Node.

In order to declare the objTree object using the GuiTree Class it is required to include the SAP GUI Scripting API library as a reference in the VBA editor. See the Things to watch out for section below for instructions on how to add it as a Reference.

Finally, when setting the object from the session.FindById function, be sure to drill down through the children until reaching the one that ends with "/shell". A safe way to ensure that this is done correctly is to poke around in the TreeView while creating a recording; a video explaining how to create a recording is available in the "Resources and Training" section above.

    Dim objTree As GuiTree, nCurrentOutputRow As Long, nCurrentName As Integer
    Dim strToolTip As String, strItem As String, nCurrentNode As Long

            Set objTree = session.FindById("wnd[0]/usr/shell")
            For nCurrentNode = 0 To objTree.GetAllNodeKeys.Count - 1
                strItem = objTree.GetAllNodeKeys.Item(nCurrentNode)
                'Populate the return values into the spreadsheet...
                nCurrentOutputRow = nCurrentOutputRow + 1
                wsOutput.Range("A" & nCurrentOutputRow & ":F" & nCurrentOutputRow).NumberFormat = "@"  'Text
                wsOutput.Range("A" & nCurrentOutputRow & ":D" & nCurrentOutputRow).Value = Range("B" & nCurrent & ":E" & nCurrent).Value
                wsOutput.Range("E" & nCurrentOutputRow).Value = objTree.GetNodeTextByKey(strItem)
                wsOutput.Range("E1").Value = objTree.ColumnOrder.Item(0)
                For nCurrentName = 2 To 10
                    If objTree.ColumnOrder.Count >= nCurrentName Then
                        wsOutput.Range("F1").Value = objTree.ColumnOrder.Item(nCurrentName - 1)
                    End If
                    If objTree.GetItemText(strItem, Right("000000" & nCurrentName, 6)) <> "" Then
                        wsOutput.Range("F" & nCurrentOutputRow).Value = wsOutput.Range("F" & nCurrentOutputRow).Value & _
                         objTree.GetItemText(strItem, Right("000000" & nCurrentName, 6)) & " "
                    End If
                Next
                'Remove the last space that we added.
                wsOutput.Range("F" & nCurrentOutputRow).Value = Trim(wsOutput.Range("F" & nCurrentOutputRow).Value)
                If objTree.IsFolder(strItem) Then
                    wsOutput.Range("E" & nCurrentOutputRow).Font.Bold = True
                    objTree.expandNode strItem
                End If
                retval = DoEvents
            Next

Handling GridView objects

This sample code comes from the SE16 Extractor tool from the "Downloads" section above. It starts by declaring the objGrid object using the GuiGridView class. See "Things to watch out for" section below for instructions on how to add the SAP GUI Scripting API library to the References in VBA.

Once the objGrid is set to the GridView object from SAPGUI, it teases out the column headers from the ColumnOrder property and adds them to the header row of the output worksheet. It sets those cells on the first row to bold so that it's clear that these are headers.

Next, it steps through each cell in the grid using the GetCellValue method. This method calls for two parameters: the row number (starting at zero and going to RowCount-1) and the name of the column as a string. The developers certainly had some reason for using these two parameters instead of the typical X and Y coordinates, but it isn't clear what that reason is.

The return value from the GetCellValue is a string that might contain a numeric or date value. In order to prevent Excel from handling these values in the way it seems fit, the format of the target cell is set to "Text" before it is populated to ensure that leading zeros are not lost and that values that might be dates are not converted to dates.

The SE16 transaction, in order to save memory and bandwidth, only populates the rows near the viewable area with values. The cells above and below the viewable area on screan exist, but they are mostly empty. It is necessary to step through the rows during the extraction using the FirstVisibleRow property of the GridView. Shifting the view downward every 15th row has proven to be effective at causing SAPGUI to retrieve into memory the values of the cells such that no blanks are populated in the spreadsheet.

    Dim objGrid As GuiGridView
    Set objGrid = session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell")

    For nCurrentColumn = 1 To objGrid.ColumnCount
        wsWorkSheet.Range(FindExcelCell(nCurrentColumn, 1)).NumberFormat = "@"
        wsWorkSheet.Range(FindExcelCell(nCurrentColumn, 1)).Value = objGrid.ColumnOrder.Item(nCurrentColumn - 1)
        wsWorkSheet.Range(FindExcelCell(nCurrentColumn, 1)).Font.Bold = True
    Next
    nRowCount = objGrid.RowCount
    For nCurrentRow = 1 To nRowCount
        For nCurrentColumn = 1 To objGrid.ColumnCount
            wsWorkSheet.Range(FindExcelCell(nCurrentColumn, nCurrentRow + nStartRow)).NumberFormat = "@"
            wsWorkSheet.Range(FindExcelCell(nCurrentColumn, nCurrentRow + nStartRow)).Value = Trim(objGrid.GetCellValue(nCurrentRow - 1, objGrid.ColumnOrder.Item(nCurrentColumn - 1)))
        Next
        If nCurrentRow / 15 = Int(nCurrentRow / 15) Then
            If nCurSec <> Second(Now) Then
                Application.StatusBar = "Getting data... " & Int(100 * (nCurrentRow / nRowCount)) & "% completed."
                nCurSec = Second(Now)
                wsWorkSheet.Range(FindExcelCell(1, nCurrentRow + 1)).Show
                retval = DoEvents
                nCurSec = Second(Now)
            End If
            '***   The following line shifts the Grid in order to populate lower rows.  SAP only
            '***   populates visible rows and then some below to conserve memory and bandwidth.
            objGrid.FirstVisibleRow = nCurrentRow - 1  'The -1 protects us from "Unspecified error" caused by trying to scroll too far.
        End If
    Next

Handling GuiTableControl Objects

Many SAP transactions utilize Tables and looping through the Rows in them has, in this programmer's experience, been the most effective method of working with them. Each GuiTableControl has a property called "VerticalScrollbar" and, because it is an object, the VerticalScrollbar has its own properties.

Two of the properties of the VerticalScrollbar are the Position and Maximum. By setting the Position property to an integer between zero and the Maximum property, the GuiTableControl object can be made to scroll up and down.

The VerticalScrollbar has a "PageSize" property that indicates the number of rows displayed on the screen. By adding the integer value from PageSize to the current Position value, the GuiTableControl is made to scroll down an entire page.

The GuiTableControl object has a "Rows" property that contains the "GuiTableRow" objects that are currently displayed on the screen. It does not contain the rows that are off screen.

It is possible to quickly loop through the Rows on screen and then scroll to the next page of a GuiTableControl with this sample code. This tends to be much faster than scrolling one record at a time and then working with the first Row in each loop.

dim objRow as Object, nMaximum as Long, nPosition as Long, nPageSize as Long

Do While True
    For Each objRow In session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").Rows
        If objRow.Item(1).Text = strMATNR Then
            'Perform the action desired here.
            'Consider and "Exit For" at this point if anything on the screen has changed because the For-Next loop
            'relies on objects that may no longer exist in memory.  Consider an "Exit Do" if the expectation is to
            'work on only one row in the GuiTableControl object.
        End If
    Next
    nMaximum = session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").VerticalScrollbar.Maximum
    nPosition = session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").VerticalScrollbar.Position
    nPageSize = session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").VerticalScrollbar.PageSize
    If nPosition = nMaximum Then
        'We reached the end of the available rows.
        Exit Do
    End If
    DoEvents  'Prevent the code from hanging just in case.
    session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").VerticalScrollbar.Position = nPosition + nPageSize
Loop

Another alternative is to scroll down one row at a time and interrogate the first Row displayed. This is a little slower, but speed often isn't an issue when automation is saving human time.

Dim objRow As Object, nMaximum As Long, nPosition As Long, nPageSize As Long

Do While True
    Set objRow = session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").Rows(0)
        If objRow.Item(1).Text = strMATNR Then
            'Perform the action desired here.
            'Consider and "Exit For" at this point if anything on the screen has changed because the For-Next loop
            'relies on objects that may no longer exist in memory.  Consider an "Exit Do" if the expectation is to
            'work on only one row in the GuiTableControl object.
        End If
    Next
    nMaximum = session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").VerticalScrollbar.Maximum
    nPosition = session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").VerticalScrollbar.Position
    nPageSize = session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").VerticalScrollbar.PageSize
    If nPosition = nMaximum Then
        'We reached the end of the available rows.
        Exit Do
    End If
    DoEvents  'Prevent the code from hanging just in case.
    session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").VerticalScrollbar.Position = nPosition + 1
Loop

Finally, the rows in a GuiTableControl objects have their own individual IDs that can be used to interrogate the data therein. In this example, the code looks to see if the second row on the screen is empty and exits the Do when the first empty row is encountered.

    Do While True
        If session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/txtRC29P-POSNR[0,0]").Text = Range("F" & nCurrent).Value Then
            session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/txtRC29P-POSNR[0,0]").SetFocus
            session.findById("wnd[0]").sendVKey 2  'Press F2
            'The code above moves from Items in a BOM into the details screen for this BOM Item.  The action is taken therein and then 
            'the code presses F3 to return to the Item list and exits the Do.

            session.findById("wnd[0]").sendVKey 3  'Press F3 to get out of Item.
            Exit Do
        End If

        If session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/ctxtRC29P-IDNRK[2,1]").Text = "" Then
            'The next line is blank.  Just exit the loop.
            Exit Do
        End If
        
        'Now we scroll down to the next line.
        nPosition = 1 + session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").VerticalScrollbar.Position
        session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT").VerticalScrollbar.Position = nPosition
        DoEvents  'Prevent the code from hanging just in case.
    Loop

The IDs of the Items in the Rows of a GuiTableControl can be found in the debugger or in a recording. All three of these methods work fine.



Handling the Status Bar

The line at the bottom of the SAP GUI screen has a wealth of information that can be used to check for issues during the processing of data in SAP. In addition to the name of the server, the transaction and whether Insert is on or off, the Status Bar tells the user when there is an issue by showing a messange and additionally what type of message is being presented.

When the MessageType property is "E" then the message is an error and the processing should stop. And example of MessageType "E" is SAP explaining that the Material Master does not exist at the beginning of the MM02 transaction.

The MessageType "W" represents a warning, but processing can proceed. The warning message can usually be cleared by calling the SendVKEY 0 from the main window (session.findbyid("wnd[0]").sendVKey 0). A good example of a warning message is SAP explaining that a Customer Master has been flagged for deletion at the beginning of the XD02 transaction.

MessageType "S" is just a simple statement that doesn't necessarily need to be cleared, but can also signal an error. This image is a great example of a "S" message thrown when no views are selected in the "Select View(s)" popup of MM02. It doesn't register as an error message, but prevents the transaction from proceeding.

The message from the Status Bar can be captured and reported to the user or requestor with this snippet of code. It shows how to check for error messages and how to report the message from SAP to the user.

    If session.findById("wnd[0]/sbar").messagetype = "E" Then
        'Something has gone terribly wrong--probably unallowed Material Number
        strRange = "A" & nCurrent 'Log goes here.
        Range(strRange).Value = session.findById("wnd[0]/sbar").Text
        Exit Function 'Return value set already to false above.
    End If

The tool crashes when Excel focuses on another open spreadsheet.

A single instance of EXCEL.EXE only focuses on one spreadsheet at a time and the VBA running in Excel only reads and writes data into the currently focused spreadsheet. To get around this, always open a spreadsheet with VBA to run in its own instance.

Immediately after double-clicking on a spreadsheet in Windows Explorer or in an email program, hold down the "Alt" key next to the spacebar. When Excel launches to open the spreadsheet, it automatically checks to see if there is an existing instance of itself running in memory and opens the spreadsheet in that existing instance instead of starting a new instance except when holding down the Alt key. In that case, Excel will ask if the spreadsheet is to be opened in a new instance and clicking the "Yes" button will have the spreadsheet running in its own instance.

A quick peek in Task Manager will show an EXCEL.EXE running for every instance. Opening another spreadsheet in an existing instance of Excel that is processing VBA will cause that running code to crash, so training users to hold the Alt key to prevent this conflict or telling them to open no additional spreadsheets while the code runs is important.

The software throws a mysterious error after SAP warns about a script.

If, after okaying the script to connect to SAP GUI and then okaying the connection to the system, the software throws this ambiguous error then check to ensure that the value in the A1 cell (next to the button) is verbatim to the Name from SAP GUI.

It is not enough to type the three-character code for the SAP server--it must be the whole Name from SAPGUI. To be certain that the names are verbatim, consider right-clicking on the server in SAP GUI, clicking Properties and then copy-pasting the Name into cell A1. The VBA code does not connect directly to the SAP server; it connects to SAPGUI and uses the Name of the server to direct the connection from SAPGUI to the server.

The Connection has no sessions in it.

If the VBA code throws an error at the line below or the "Script Recording and Playback..." menu item is greyed out like in the image on the right then likely, the SAP server has scripting at the frontend disabled. Scripting can be turned on in transaction RZ11 by setting the sapgui/user_scripting parameter to True. Stefan Schnell goes into more depth in this article.

    Set SapGui = GetObject("SAPGUI")
    Set Appl = SapGui.GetScriptingEngine
    Set Connection = Appl.Openconnection(Range(strRange).Value, True)
    Set session = Connection.Children(0)

The text of the window has Server and Instance sometimes.

The code below previously checked to see if the title of the window was equal to "Change Material (Initial Screen)"; the system on which it was written did not include the System ID and MANDT value in the title of every window, but one user's system changed it to "Q01(1)/100 Change Material (Initial Screen)". The code was changed to check if the text was in the title because some clients have this "feature" turned on.

    'Now wait for the Material Master change screen
    Do While InStr(session.findbyid("wnd[0]").Text, "Change Material (Initial Screen)") < 1
        retval = WaitFor(1)
    Loop

Turning it off is easy enough as changing this Registry entry to 0. It is documented better on SAP's blog site and on SAP's Answer site.

HKCU\Software\SAP\SAPGUI Front\SAP Frontend Server\Admiistration\ShowAdditionalTitleInfo 

The Sapgui Component could not be instantiated.

There is a good chance that this is caused by a lack of network connectivity to the SAP server. Try connecting to the VPN if necessary and retrying.

The methods for the SAP GUI Scripting API do not appear in the Object Browser

Poking around in the objects using the debugger is a great way to understand how the properties in the objects work and using the SAP GUI to create VBS files will expose a few of the methods, but to unleash the true potential of the API, it is necessary to see all the methods associated with each object. In the references, add in the SAP GUI Scripiting API library and then these objects will appear in the Object Browser (video link in the Credits section below).




Microsoft Excel is waiting for another application to complete an OLE action

When a transaction runs for more than some number of seconds, the OLE connection times out and Excel throws a warning. If nobody is watching the program run then this warning sits open and SAP eventually ends the session by logging out the user.

Put this code at the very top of the VBA code. It calls the OLE library later.

Private Declare Function _
    CoRegisterMessageFilter Lib "OLE32.DLL" _
    (ByVal lFilterIn As Long, _
    ByRef lPreviousFilter) As Long

Put this code directly before the code that causes the timeout. For example, in MM17, pressing the Save button will often cause it to run longer than thirty seconds.

    Dim lMsgFilter As Long
    ''' Remove the message filter before calling Reflections.
    CoRegisterMessageFilter 0&, lMsgFilter

Put this code directly after the troublesome code that causes the timeout. If the server is disconnected for any reason, it helps to have this timeout available to the code.

    ''' Restore the message filter after calling Reflections.
    CoRegisterMessageFilter lMsgFilter, lMsgFilter

Check the Credits section below for the origin of this simple, but effective code. This solution was written by a very competent programmer.

Naturally, this code doesn't spontaneously exist from a vacuum. The ability to launch the SAPGUI program from a VBA shell and connect to it as an object has existed for over a decade and has been described and expounded upon by hundreds of other programmers, but nobody was making this public knowlege; this programmer was outraged to discover this existed only after 18 years of doing SAP Data Migration using LSMW.

Saplicity is simply a way of wrapping this existing ability in recyclable code, creating training tutorials and canning reusable tools and then finally branding it all to make it easy-to-share from a centralized source. Sending colleagues a single web link is much easier than sending a dozen links to articles and videos in order to get them familiarized with the VBA scripting knowlege used to perform automation within SAP with nothing other than Excel and SAPGUI.

With all that said, the inspirations for Saplicity are listed here as they are encountered. It's impossible to tell from where the code in the ConnectToSAP() function above first appeared, but the rest of the inspirational content is credited here with humble thanks.

• Csongor Varga's great video on Reading GridView Control was a huge help. His simple explaination of how to find available methods in SAPGUI objects was a quantum leap for optimizing new code.
• Darren Bartrup-Cook's clever code eliminates OLE timeouts when processing long-running transactions in SAP. Check out this link for all of the details and for the necessity that mothered this simple and effective solution.