Attachment	Size
VBA_code.txt	9.94 KB

GUI Scripting With MSExcel

Are you finding your wrists getting weak after entering the same data over and over again? Does your company ever have a situation where a user wanted a simple method to enter different data into the same field for multiple materials? For those times when MM17 just doesn't work SAP has a FREE solution. The best part is that this solution can be extended to automate data entry into almost ANY screen, or combination of screens. Read on to learn an easy method to automate your data entry without buying any additional software.

WARNING!!! Mass maintenance tools such as described in this article are VERY, VERY powerful. Misue of these tools WILL corrupt your data and may cost you your job!! The author provides no warranty, either express or implied. The author shall not be held liable for anything you do with the techniques represented in this article.

Use the information in this article at your own risk!!

Overview

Mass dataloading used to consist of countless hours entering in data by hand, a slow and grueling process. Now, perform one SAP transaction, and you have a template to execute hundreds, even thousands, of SAP transactions at high speed with extreme accuracy!

Hours can be changed into minutes for data loading. In our experience, projects that would have taken 100+ hours were loaded in merely 10 hours! Scripting transforms the monotonous, time-consuming task of manual data loading to the speed, ease, and accuracy of automation.

This article includes the VBA code for MS Excel and basic instructions to start mass maintenance of data.

SAP has developed a very powerful technology that listens to all the actions that a user performs in the SAP GUI and records them as a script. The SAP GUI Scripting technology can record almost any SAP transaction, and it is shipped with SAP without additional licensing fees.

SAP GUI Scripting is shipped with SAP GUI 6.20 and later versions.However, scripting is so powerful that it is disabled by default for any given SAP system. To check if this feature is available, click on the Customizing Menu in your SAP session. If the 'Script Recording and Playback' option is enabled, then you are ready to record your keystrokes.

If this option is disabled, the administrator has to enable the support. The instructions for the administrator are in the "SAP GUI Scripting Security Settings" section of the online SAP documentation

SAP documentation maintains that SAP GUI scripting will only import and export data that is displayed on the screen. It is difficult to verify this claim because the screens flash by too rapidly for human eyes to read all of the data on the display. However, the script can read, use, update, and write the data from the spreadsheet into SAP, and vice versa. In addition, the script can read more then just the product data. The SAP session data is also available, including the client, the server, the user id, the transaction, the message, and the window title.

To use the recorded script is very easy. Simply place the needed data into an MS Excel worksheet and insert the recorded script into an Excel module along with some VBA code to loop through the data. SAP transactions now can safely be left unattended, because variable data is read and placed at high speed into the SAP transactions with surgical precision. Simply put, SAP GUI Scripting is 'Record and Play' with an attitude.

Used By Itself

GUI scripting can be used by simply recording your transaction, then entering the object to be changed as the starting point of the macro. This works, but still requires that you manually input the target that you want changed. To do this, take the following actions;

Convince your security administrator to enable Scripting at the server level
Record your keystrokes and save the script in a file with a .vbs extension.
Execute the file
Double click on the file - or –
Drag and drop the file on a SAP transaction - or –
Use the playback button within the Script Recording and Playback dialog

Saving the file with a .txt extension will disable the script from accidentally executing and allow users to safely edit the file.

Using the Spreadsheet Template

The front end that we have developed has 2 basic requirements.

A worksheet that contains the target objects and the data to be written to those target objects
A Visual Basic module that maps each column in the worksheet to a specific spot in the execution of the macro

To prepare the Excel spreadsheet , the active sheet needs to be renamed to SAP_DATA. Copy/paste the attached VBA code into a module. To insert a module, follow the Excel menu path Tools -> Macros -> Visual Basic Editor, then Insert -> Module.

Copy and paste this text into the VBA window.

Return to your SAP transaction. Select the ‘Script Recording and Playback' option and the ‘Start Recording’ option to record a transaction. You will need to specify a save path and file name for your recording either when you start recording or stop recording, depending on your GUI version.

Here are some notes from our 'Quick Start" guide.

1) Preparing a Script

Start the macro recorder dialog from the customizing menu.
a) Open SAP and in the upper right hand corner select “Script recording and playback“
b) On the Script tool bar select “RECORD”, you may want to be on the initial screen to start your recording (example ME02).
Execute a flawless transaction (Do not record any errors).
a) Run through your first material with the fields you want to change.
Stop the recorder and save the script.
a) After you have made the changes to the first material, click the “STOP” button on the “script record and playback” tool bar.
b) You will be prompted to “name” the script and specify a location to save it to. Saving the script to your desktop with a name pertaining to what you are doing is advisable. You can save it as a .txt or leave it as VB script. Some SAP versions will require the file name and path to be specified before recording the script.

2) Preparing Excel

Open a spreadsheet that contains the GUI scripting macro.
Add the variable data to the spreadsheet.
a) Input the data you need changed into the spreadsheet, for each material by row. Each column should represent a different field in SAP you want changed/ added/ deleted for each material in that row.
Do not leave any blank cells in column ‘A’. The spreadsheet uses column ‘A’ to control the number of loops.
Columns ‘A’ to ‘O’ can be used for variable inputs.
Open the Visual Basic editor.
a) Open the “visual basic editor” in your excel spreadsheet. Go to “Tools” at the top of excel, scroll down to “Macro” then over to “Visual basic editor”. Or click the “ALT” and the “F11” keys and the same time.
Open/Edit the SAP GUI Script recording.
a) Open the script you just saved, and select all the lines that begin with “Session.findbyId”.
Copy the recorded keystrokes portion of the Script.
a) The 'recorded keystrokes' are in the file you previously specified, and each line to copy begins with "Session…"
Paste the portion into the Visual Basic Macro.
a) Paste your lines of code from your recorded script to the “Module” named SAP_GUI_SCRIPT in the designated open space.
Map the script to the spreadsheet.
a) The first part of mapping the worksheet is performed automatically with Visual Basic code that reads your data from the spreadsheet.
b) The next step is to find your data in your recording. You will find your data at the end of a line in between quotation marks.
c) Remove the quotation marks surrounding your data, and replace your data with the corresponding column letter from your spreadsheet.

Example: you recorded MATERIAL_1, and the data is in column A.
FROM session.findById("wnd[0]/usr/ctxtRCUCO-MATNR").text = "MATERIAL_1"
TO session.findById("wnd[0]/usr/ctxtRCUCO-MATNR").text = A

(WARNING – all fields left with recorded input will be hard coded, and will keep this value every time you run this script).
RUN the script. You can do this from the VB editor, or close the VB editor and use alt-F8 to run it.

The script will run and check the first material in row one. If no errors were found it will ask you if you wish to continue with the rest of the spreadsheet. The script will run till completion unless an unexpected error is found. The script will generate results per line on the spreadsheet for each row.

For Best Performance

Close out any SAP sessions that you are not using - including the SAP Logon Menu. The script usually attaches to the leftmost session, but not always.
Rows and column addresses in the script begin at 0 (zero). Example – if there are five items in a list, SAP will label them 0 – 4.
You must deliberately touch every SAP data field that you want included in your script.

WARNING!!! Mass maintenance tools such as this are VERY, VERY powerful. Misue of these tools will corrupt your data. The author provides no warranty, either express or implied. The author shall not be held liable for anything you do with the techniques represented in this article.

GUI scripting with MS Excel