This will probably be the only VBA lesson I do, but I think automating SAP is significantly easier using VBA because its scripting language is VB by default. This is very useful to individuals that are using SAP.
I’ve used this to automate the pulling of data from SAP or getting screen prints which is a pretty common task for an auditor. In one of my roles, our team was responsible for pulling 250 screen prints showing pricing information within SAP. This process could take a week and an auditor would look up 250 products on a few different screens, screen-print them and send them to our external auditor. We were able to build this process to look up all of those items in a couple of hours.
The first thing you have to identify is whether or not your IT has allowed the script recording turned on. While I think it defaults to being on, I have seen it greyed out. If it is greyed out you may need to work with your IT team to turn it on.
So the first step you will want to take is the think through what you want to automate. This process we will record the steps you take to do something. Then we will put those steps into VBA in Excel to automate them.
Push the Rainbow Computer to start recording
Click Script Recording and Playback
Set where to save your recorded steps
Before you click the red button to record, change the Save To location to somewhere you want to save the recording.
Start Recording
Click on the circle to start recording the steps you want to take
Open the saved .vbs file
This is the file that SAP will create that shows the steps taken to perform automate what was performed.
You need to open the file within Notepad to be able to see the steps that were recorded. If you just double click it will actually run on its own to perform the steps you just recorded.
This Excel Workbook will be available to download from my website
This is what the steps will look like. We will copy the section below the top section and past it into VBA our Excel code.
Download my Excel Document that you can use to automate processes in SAP.
Once you open the file you will need to press Alt+F11
You will then click on the Module : SAP_To_Excel
My Excel Document has code that will launch SAP and close it after the program runs. So, you will need to put it between the two sections I comment.
Before running this program, there is a couple of items to consider before running. To avoid errors I set up the program to close all other Excel Documents and SAP instances. So make sure to save and close all other Excel Documents before running this program.
My program does give you a message box telling you this will happen before it executes so you can tell it no and it won’t run so you can close everything before it runs:
You also do need to update this item for the name of your instance of SAP. I did setup this program to run this same process on multiple instances which is why I saved this as a list, but if you just replace the name with one it will just run the one instance:
If you don’t have single sign-on for your instance of SAP and need to put a username and password you need to put your username and password into this section of the program:
If you do have single sign-on you can delete these lines because you will auto login and it will cause an error to keep them in.
The next section is where you put your recording
In the code in my Excel workbook. I do show how to take values from my sample workbook and it will loop through each row on the “Samples” tab andit will take a screen print and put it in the “ScreenPrints” tab. Based on an MM19 screen.
The last part turns the Excel DisplayAlerts back on because we turn it off earlier when to close the Excel Documents. It also closes SAP to allow us to loop through another instance of SAP. In my example, though it only is looping through one instance so it’s just going to close and end the program.
You run the program using this button:
It definitely was something I really wanted to learn when I first started at an SAP shop since a lot of time was spent pulling documents and data from SAP. I hope this inspires you to do something great.
