Saplicity: the Free Excel-based SAP Data Automation Suite
Loading data into SAP is a niche that many vendors try to fill with tools that are confusing, cumbersome and absurdly expensive. Some vendors, instead of including all features together, license their products for thousands or tens of thousands of dollars per user based on what features are included and then charge thousands more in recurring annual fees on top of the initial licensing fee.
The promise made by many of these vendors is to eliminate the need for expensive technical resources, but that promise is seldom kept; the customer is inevitably forced to find technical resources to support the new product. If it were the case that their products made data migration easy for non-technical resources then job search boards would not be flooded with job requisitions for "Expert in ________" or data migration roles for people that have "at least n years of experience using ________".
What if there were a way to use nothing more than Excel spreadsheets, SAPGUI and a few lines of VBA code to perform loads in SAP? How would the data migration world change if there were a way to inexpensively produce and distribute easy-to-use tools in-house using nothing but the software already installed on company-owned laptops and a bit of recycled opensource code?
Well, now there is Saplicity. The free suite of recyclable VBA code that comprises Saplicity costs nothing, requires no licensing and can be used by anybody to create or maintain vast quantities of data in SAP.
Saplicity is completely opensource and can be modified without limitation to automate data loads into any transaction. Organizations large enough to run SAP likely have internal resources that are familiar with VBA, so Saplicity creates no dependency on external resources.
Comparing Saplicity to other data automation tools
Features: | Saplicity | The others whose names shall not be mentioned here, but you know who they are. |
Forward compatible with S4. | Yes! | Probably, but may require some effort |
Works without additional software installed on SAP server by BASIS team | Yes! | For most, no. |
Works without special permissions on the SAP server | Yes! | Likely requires RFC access and permission to run many esoteric functions. |
Works without proprietary software installed on the desktop | Yes! | Does not operate without proprietary software installed |
Load tools are easily distributed and reused | Yes! | Only if other users have purchased license and paid recurring annual fees |
Automation is driven by industry-standard VBA | Yes! | Complex and unintutive proprietary language requiring training |
Makes GridView and TreeView objects completely addressable | Yes! | Does not allow direct access to complex graphical control objects |
Initial licensing cost | It's free! | Thousands of dollars (or tens of thousands) per user |
Recurring annual licensing fee | It's free! | Thousands of dollars per user for as long as it is installed |
Resources and Training
See for yourself if Saplicity is right for your business. Feel free to contact us with suggestions, requests, complaints, criticisms and ideas.
Downloads
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.
Recyclable code
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) 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
Tips and Tricks
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
Things to watch out for
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.
Credit where it is due
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.