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

Textfile_before_Split.png

Textfile_after_Split.png

Folder_Prepare.png

After_Prepare.png

VBE_Reference.png

VBA_sapfewse.ocx.png

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).

WS_SAP_PROCESS.png

SAP_SESSIONS.png

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.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Günter Wulf

    Interesting, I had more or less the same issue. I also created something similar for my use as well, though I slighlty used another approach.

    • I implemented a class in VB in order to have “easier” access

    WIth FindSession you just loop through all existing SapSessions and find the “correct” one.

    (0) 
    1. Holger Köhn Post author

      Hello Günter.

      I tought about an class in the beginning as well. But process in modules seems like much faster. I currently working on a class as my Team mates more and more have a need for. However, have my function modules cleanup from unneccessary code snippet duplicates.

      But still my class require ~800 ms for getting available sessions displayed in listbox. Calling same functions from a normal module take ~350 ms.

      (0) 
      1. Günter Wulf

        Hello Holger,

        I did not consider performance as most of the execution time is used in the scripting part, to be more precise, the “actions” in SAP take longer than any part of the VB scripting. But anyway, an interersting aspect, I never thought about it.

        (0) 
  2. Pablo Duart

    Hello Holger,

    I’m very interested in taking a look at your files, but I cannot find the way to download them.

    I’ve checked the post several times but can’t find a link. Have I missed something?

    Thank you,

    Pablo

    (0) 

Leave a Reply