Hi there, I recently had to make a quick SAP automated process and thought that it could be interesting to present the test version here to give you a concrete
example of what you could do with the different notions that we approach in the tutorials up until now. In this sense, it will not be a tutorial per se but more a quick presentation of the code
to give you ideas on what you can achieve with vba.
Problematic to be solved : Some colleagues of mine are actually doing a lot of retrieve from SAP manually in order to updates some reports that needs to be update
on a monthly basis in Teams.
This task being heavily time consuming, the aim here was to create a customize ribbon with dedicated controls allowing the end user to easily retrieve the form that
he needs from SAP and update the values that need to be update.
Proposed solution :
Given that in the current process, the user needs to retrieve different forms categorize in different divisions, the idea here is to use comboBox controls in order
to allow the user to select and extract easily the SAP forms that he wants and add a button control allowing the user to refresh the target values.
You can find the full code here for the
vba code module and the customUI.xml file and a dummy file for the Corporate Affairs case here.
=> I commented extensively the code so I'll let you read it directly on github as I don't think that there is really a need to add anything to it.
Also here is the step by step process for the end user to give you a more comprehensive view on what is actually done here :
Process :
1. Open a new session in SAP
2. Open the Test.xlsx in Teams
3. Go into File/Save As and download locally the file
4. Open the downloaded file and save it in the location of your choice on your computer
5. Go into the developer tab in excel and click on Add-Ins. There in the window popping up choose browse and select the xlam file that was sent to
you with this mail.
Note : If the developer tab is not present in your excel follow the following procedure :
Go into File => Options => Customize Ribbon
There under Customize the Ribbon and under Main Tabs, select the developer check box
6. Select the SAP Extract tab
7. Select the drop down box of your liking and click on the Cost Center that you want to extract from SAP
8. Save under the name of your choice the Text_extract.xlsx file newly created and close it
9. Go back into the downloaded excel files from Teams and in the SAP extract tab click on the Update CC Values button. A window will pop up to
determine the source file for the update, choose your previous extract file and wait for the macro to run.
10. Once it’s done copy and paste the updated Actuals cost column from the excel file in Teams.
