Interactive and dynamic Excel VBA template - SAP GUI Scripting -

[holger_khn]

Introduction:

SAP GUI Scripting can be used for many small and intermediate batches for mass maintenance tasks (P-/Q- and D-Systems) and quality testing (Q-Systems) in SAP.

To reduce development workload or modify a lot of code for different SAP GUI recordings I saw a need for having an Excel VBA template which can cover a lot of requirements.

Features:

• Easy to use (One-Button-Solution).
• Excel functions and options can be used for data preparation and consolidation in front of starting update procedure.
• During initialization all existing and non-busy SAP sessions got displayed (UI-Listbox). So we are able to select a specific session for Excel VBA adoption.
• Data from SAP can easily imported into Excel worksheet (Data from ALV-list, fields, result list, status messages, error messages,… and so on).
• Only SAP GUI Scripting recording code need to integrate into VBA module ‘SAP_1_PROCESS’.
• More columns as data source or destination for field data can be added. Last two columns are reserved for procedure usage. This is achieved by using a data-array which got ‘Redim’ during process according to used rows and columns.
• Log-function is integrated (for SAP messages like statusbar Messages or free-text).

Prepare Excel VBA template:

1. Step: Create folder on your desktop (or somewhere else) => folder name is up to you.
2. Step: Store below text-files into this folder: Please be aware that text-file 'Modules_All.txt' need to split into four text-files named 'SAP_1_Process', 'SAP_2_Functions'. SAP_3_Public_Subs' and 'Module1'! Start and end of module specific coding is marked and must removed before saving of single text-file. First line in this files must be Attribute VB_Name = "SAP_1_Process", Attribute VB_Name = "SAP_2_Functions" and Attribute VB_Name = "SAP_3_Public_Subs". As I can´t upload more than three files you Need to split Modules_All.txt on your own and save files in folder.
   1. SAP_1_Process.txt
   2. SAP_2_Functions.txt
   3. SAP_3_Public_Subs.txt
   4. Userform1_frm.txt
   5. Userform1_frx.txt
   6. Module1.txt
3. Step: Create a new Excel workbook and store this in same folder as text-files. You can choice every name you want. My suggestion is ‘Prepare_WB.xlsx’.
4. Step: Now go into VBE of Excel ( ALT + F11) => insert Module => open text-file ‘Module1.txt’ => Select all and copy => paste into new module from Excel workbook.
5. Step: Execute macro ‘prepare_worksheet’ (Reference to sapfewse.oxs will done utomatically. This require Folder-structure ENVIRON("PROGRAMFILES") & "\SAP\FRONTEND\SAPgui\sapfewse.oxs"). If you have an different Destination please add manual.
6. Step: Remove ‘Module1’ from macro-enabled workbook and save.
7. Done

Usage of Excel VBA template (MM03_VBA_SAP_SCRIPTING_V1.xlsm):

This Excel template have for demonstration purposes SAP GUI Scripting integrated for getting MM03 data from ‘stor. loc. stck’-tab => ‘Qty. Unrestricted’ + “UoM’ + ‘Material description’ data.

1. Step: Fillin ‘Materialnumber’ + ‘Company Code’ + ‘Storage location’ combination used in your system. As error handling is integrated for ‘Company Code’ + ‘Storage location’ combination you can test this as well.
2. Step: Press button ‘Run macro ‘SAP_PROCESS’’
3. Step: All open and Scripting-enabled SAP sessions got displayed in a Userform-Listbox
4. Step: Select one session and press ‘Enter’ or Commandbutton
5. Done: Script got executed and you will get data into Excel according to your input data combination (Materialnumber/CoCode/Storage location).

Important data structure information:

All data from Excel worksheet ‘SAP_PROCESS’ got stored into data-array ‘arr_SAP_Data’. This Array is defined with same number range as our used worksheet range. Starting from row ‘2’ up to last used row and all used columns (remember that last two columns are reserved for procedure process).

arr_SAP_Data(lngCounter, (lng_Col + 0))

=> lngCounter is current executed row in worksheet. (lng_col +0) define column ‘1’

=> (lng_col +1) is second column

=> and so on.

When you add or delete columns please save workbook immediately.

Conclusion:

Feel free to use this template for your requirements by modify coding in ‘SAP_1_Process’-module. But be aware that I am not responsible for any issues, inconsistencies or other incidents which can cause by using this template. Feel free to ask if you Need any additional information. But do not expect any support for your purposes and requirements. Hope this will give you some lights how SAP GUI Scripting can make your daily tasks much smoother.