Automate Data Extraction with SAP GUI Scripting & Excel Macro VBA [english]

In today's video I will show you how to use SAP GUI Script and Excel Macros, that is Visual Basic for Applications, in short (VBA). This can be very useful if you regularly evaluate data from the SAP system and want to process it in an Excel.
In the video today, I select data from the SFLIGHT table and put it into an Excel. I extend the generated script with variables that can be entered into the Excel. A button is then created in Excel that can be used to start the script.
#SAPGUIScript #Excel #automation
Inhalt:
00:00 Intro
00:50 Activate GUI Scripting
01:49 Record Script
04:20 Test Script
06:06 Extend Macro
08:05 Create Button
09:35 Create Variables
13:03 Test Script
14:02 Outro
▬▬ Literature 📚 ▬▬▬▬▬▬▬▬▬▬▬▬▬
▶ ABAP: An Introduction 2020 amzn.to/45CLTqk
▶ Complete ABAP 2023 amzn.to/45D5UNM
▶ ABAP to the Future 2022 amzn.to/42cgWGs
▶ Clean ABAP 2022 amzn.to/3KAqmow
▬▬ My Hardware 💻 ▬▬▬▬▬▬▬▬▬▬▬▬▬
▶ Microphone: amzn.to/3zj2UIz
▶ Headphone: amzn.to/3GUBRFg
▬▬ My Software (free) 💾 ▬▬▬▬▬▬▬▬▬▬▬
▶ Video Recorder: bit.ly/678fgh6
▶ Video Editor: bit.ly/38Rj9lb
▶ Thumbnail: bit.ly/CustAndCodeThumbnail
▶ Gamma AI: bit.ly/3nsdvgr
▬▬ Further Links 🔗 ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
⭐Become my subscriber: bit.ly/CustAndCodeSub
🎬All videos in English: bit.ly/CustAndCodeENG
☕Buy me a Coffee 😀: bit.ly/3dih2cl
*The links are affiliate links. There are no additional costs.
Microsoft excel icons created by Pixel perfect - Flaticon
www.flaticon.com/free-icons/m...
Sap icons created by Freepik - Flaticon
www.flaticon.com/free-icons/sap
SAP® and SAP GUI Scripting are registered trademarks of SAP AG.

[@cegaraujo]

Very didatic video. Thanks a lot!

[@CustAndCode]

Thanks for your feedback and thanks for watching! 🤗😀

[@jeslaravishuda]

Thank you so much, I learnt how to download a report that takes 1 hour of my time.

[@CustAndCode]

I'm glad you liked it. And thank you for watching! 🤗

[@pritirawat2616]

This was very helpful!

[@CustAndCode]

I'm glad you liked it. And thank you for watching! 😀🤗

[@covid699]

Helped a ton. Thank you

[@CustAndCode]

You are welcome! 🤗 and thanks for watching! 🤗

[@TheFundamentalChristian]

This is great stuff! I look forward to utilizing this code into updating 100s of lines of balances from SAP into Excel. Right now the process is done manually and it takes my team many hours.

[@CustAndCode]

Great to hear! Hope you can save some time with this! 🤗

[@vijaysenthil7023]

Thanks for your vedio its very easy to understand

[@CustAndCode]

I'm glad you liked it. And thank you for watching! 😃

[@user-oo1wh9jy8y]

wow tahnk you , you are a hero :D

[@CustAndCode]

Yeahh! thank you! 🤗 I always enjoy reading feedback like this very much 😀

[@kapibara2440]

Fantastic video! Thank you sir 😊

[@CustAndCode]

You are welcome! Thanks for your feedback! 🤗

[@kapibara2440]

​@@CustAndCode I just realized I have no Access to Rz11 in my SAP, in my organization. I Wonder if there is a way to get there and enable script writing? Maybe an ADP ticket will help.

[@CustAndCode]

@@kapibara2440 RZ11 is normaly limited to Basis Administrators because it is used to access the system profile maintenance screen. This transaction allows system administrators to view and modify various parameters and settings that control the behavior and configuration of the SAP system. Perhaps sap basis can temporarily set the setting to true? 🤗

[@furkandirik2491]

Awesome

[@CustAndCode]

Thank you very much! I'm glad! 🤗😀

[@juliusowan671]

Too beautiful! Thank you for sharing this. Is there a way to automate a change of date

[@CustAndCode]

You mean export the same data in different excel sheets with different date parameter? 🤔

[@mrronaldino7298]

Thank you so much this will help me a lot as data analyst. However I am not authorized to use tcode RZ11. I will definitely raise a ticket for this for automation purposes!

[@CustAndCode]

I'm glad you liked it. And thank you for watching! 😀🤗

[@user-zf2iv7vb3i]

Thank you for your video. That was very helpful. Can I ask you a question? Is there a way to download all the files attached to the Sap accounting slip by using Guiscript? I don't know how to download the whole thing because the number of attachments is different for each slip.

[@CustAndCode]

I am not sure if SAP GUI Script the right solution for this. 🤔 I think I don't know too much about the SAP accounting slip. 🙄

[@Karimsalah92]

thanks the video is very helpful i did it in a few minutes, but Sap needs to be opened, can you please support on how to embed in the VBA Code to open SAP automatically? and also how to make it extract automatically at a certain timing

[@CustAndCode]

I will check how to open the connection automatically. But I am not sure if it works at a certain timing. For this I would do it in ABAP and I would work here with Jobs. 🤗

[@TheWanderercontent]

That was great !! How would you adapt the script to pull the table each month ? Like a monthly report

[@CustAndCode]

For a monthly report I would implement it in ABAP. If you want to do it with VBA you have to call it manually every month. 🤗

[@TheWanderercontent]

@@CustAndCode thanks for the information ! do you have a video on that ?

[@CustAndCode]

@@TheWanderercontent Unfortunately not, I would use a ABAP Report for that. This repord I would call every month, or I would plan it as a job. 🤗

[@surajkumarmahto8631]

Hi thanks for this its very helpful ....just one query that i wanted to extract data in same. Excel file in which i have created button pls.. help in this

[@CustAndCode]

That could be difficult, because the file is already open... 🤔

[@DavidRelphWorkLife]

great thanks for the code can you read mutil codes from a range in xL say from A1 to A5 thanks

[@CustAndCode]

You mean to use it as an input for a SAP selection? 🤔

[@carlaarenas6537]

Hi Sir do you have a video that I can follow regarding for example I need to create Info record using transaction code ME11 then I will create Macros and I will run using that. I believe I need to create some headers right?

[@CustAndCode]

mhm I am not sure if there is an advantage to do this with a script, because you have every time different input fields? What do you mean with "to create some headers" ? 🤗

[@user-io8ug3pj7z]

awesome stuff man. How do I export SAP data/excel to the Macro enabled spreadsheet. In a way the spreadsheet becomes an app for data extraction...

[@CustAndCode]

I am not sure if this is possible, because you have to write the data in an already open excel sheet 🤔

[@nathanmartin66]

Tanks for the video it Washington vers helpful!! I just have à question : how do I do to work on a Excel sheet after closing the SAP part ? My code "Application.ScreenUpdating = False" doesn't work...

[@CustAndCode]

I am not sure if I understand you right? You can normly work after the SAP script stuff. What do you want to do? 🤔

[@nathanmartin66]

@@CustAndCode after the SAP script, some functions don't work like workbooks.close ... it was the case for application.screenupdating=false until il create a second application : dim application2 which i use in my SAP script part

[@CustAndCode]

@@nathanmartin66 Yes, workbooks.close was not working for me as well. I wanted to analyze that, but haven't the time yet. 🙄 Thanks for your feedback!

[@chauhannaman98]

Thank you for the video sir! I have a query. At 02:56 when after selecting the option from Available formats, you select XLSX format. I did the same but the dialog box for Directory and Filename doesn't come up. File Explorer comes up to save the file. This moves the handle out of SAP's scope and that can't be included in the scripting. Is there any settings I need to tune in my SAP GUI app? I can share further info over the email if you need. Thank you!

[@CustAndCode]

Thank you! Make sure your scripting option for microsoft windows dialog is unchecked. This will prompt the SAP save as window instead of the windows file explorer. 🤗

[@alsantour8835]

@@CustAndCode I tried this and it still uses the Windows Dialogue even though it is unchecked! using 7700, i tried searching online and people have the same problem.. .any advice?

[@CustAndCode]

@@alsantour8835 you have already restarted the SAP GUI?

[@alsantour8835]

@@CustAndCode yes , I eventually got it to work! Thanks!!

[@CustAndCode]

Good to hear! 🤗

[@user-de2mc5jq8q]

Thank you for your video.what if l want to write a code to close excel file after export it??? Please your support will be appreciated

[@CustAndCode]

I also wanted to test this in my example, unfortunately closing did not work. I will have a closer look. 🤗😀

[@satyammalhotra6503]

Hi Sir, Hope you are doing good. Could you please help me with any formula/vba code for taking dynamic date. Means i always have to extract the data for open line items appearing in end of the month. Your help would be highly appreciated. Many thanks for the video and sharing knowledge with us😊

[@CustAndCode]

Thank you! 🤗 What is your usecase? You can pass the date in the excelfile? Then the date is a parameter for ABAP. 🤗

[@satyammalhotra6503]

@@CustAndCode Can you help me with any modifications I need to do in vba code which we copied from sap script. Yeah! If I enter the date in excel and vba code take that date and pull the report for that respective date.

[@dineshshelke631]

Thanks for making this video I need your help where can we connect?

[@CustAndCode]

Unfortunately, I cannot provide individual support here. Write your question in the comments. Maybe I can help. 🤗

[@user-ih5lc3nw5i]

Use full video, one question "Can we use VBA (macros) to upload file from excel to SAP" ?

[@CustAndCode]

No, for this you should use ABAP because you have to process the data from the Excel in SAP. I have already uploaded a video where I show this. 🤗

[@ahmadghozie8686]

Hi Sir, I just try to follow your video. After I create the Macro, and Run the Macro, it didn't work. There's a notif on Microsfot Visual Basic dialog box said that " Run-Time error '619' : The Control Could not be found in id". please help thank you

[@CustAndCode]

Have you an open connection to the SAP GUI? Try its again with an open SAP GUI window. 🤗

[@Bitza00]

Hi, getting a runtime error 614, any help please?

[@CustAndCode]

When you use the script you need an open SAP GUI connection to the system. Maybe this is the problem. 🤔

[@lukban1825]

Keep in mind that only the last declared variable (session) was actually declared as Object. The rest is Variant in this case (default one)

[@CustAndCode]

Thank you for your feedback! I appreciate it! 😀

[@jaybeid490]

Hi, please show how to extract commodity code from Md04 using vba in excel

[@CustAndCode]

Unfortunately I don't have access to this SAP module. But the technique is the same which I have showed in the video 🤗

[@jaybeid490]

@@CustAndCode thanks, let me try

[@CustAndCode]

@@jaybeid490 Good luck! 🤗

[@RaMeShBaBu-gf1wc]

Hi, once open the Excel file from SAP then I want to close by using VBA code. Could you please let me know how to do because I have download more than 30 files. So each workbook need to close automatically. Please help on this

[@CustAndCode]

I have tested several suggestions from the internet but unfortunately none of them have been successful. 🙄

[@RaMeShBaBu-gf1wc]

@@CustAndCode thanks for confirmation even I have checked 2 days not working but finally I have informed to user you have to close manually after downloaded 30 files.

[@CustAndCode]

@@RaMeShBaBu-gf1wc 🤗🤗🤗

[@AmitKumar-cs2gx]

May use it in combination with power automate

[@Kruse1]

Unfortunately the record button is greyed out for me and not authorised to use RZ11.

[@CustAndCode]

Yes, this depends on your security policy or your SAP BASIS Team. 🙄

[@Kruse1]

@@CustAndCode ..however the playback button is not greyed out and it lets me load a vbs file so hopefully that means I can write the code manually and run it in Excel.

[@CustAndCode]

@@Kruse1 Then, have fun with scripting 🤗

[@Kruse1]

@@CustAndCodewell, I did have fun programming three standard transactions by copying your code and modifying screen field names which I found by clicking on the element and pressing F1 for help which had the technical information ...only one sticking point though. There is a custom "z" transaction we use, I have found and used about ten screen fields but one gives 'control not found by id" error even though I found and copied it the same way as the others 🤔

[@CustAndCode]

I think I can not help you with custom things. 🤔

[@mohammadsuheb.l9872]

How to set the date from and to?

[@CustAndCode]

The same way how I set the carrid. Do it the same way like the variable carrid. 🤗

[@mohammadsuheb.l9872]

In my SAP I have to regularly use the date for example from 07/24/2023 - To 07/23/2023 to pull the report. So how do I do this in vba

[@CustAndCode]

@@mohammadsuheb.l9872 would you you like to start your report from excel? and would you like to pass your date values from an excelcell? 🤔

[@mohammadsuheb.l9872]

Yes, I would like start from excel the report and pass the date as well from excel cell

[@CustAndCode]

@@mohammadsuheb.l9872 Then you have to do the same like I did with the carrid. Define the variable. Read the value from the excel to the variable abd use the value in your script. Follow the steps like I did for the carrid. It is totally the same step. 🤗

[@samuvelrajan8857]

copy paste function is not working while recording scripts

[@CustAndCode]

But that works, have just tested it. 🤗

[@samuvelrajan8857]

Actually it works, but I just created a script without VB codes. So once we copied a data, it was stored in the script file. So while running second time the script is not copy new data instead it take the value from stored data which copied previously. I want It should copy the particular data everytime when it's running Kindly help me on this. It will be great initiative of my process

[@CustAndCode]

@@samuvelrajan8857 Ok, I understand. I don't think that this is possible with scripting. What you need ist dynamic selection, the script can only static selection. 🤔

[@samuvelrajan8857]

@@CustAndCode can I have your email id, so that I can send my script through email

[@CustAndCode]

@@samuvelrajan8857 Unfortunately, i cannot provide an indvidual suport here. I am also not an expert in SAP GUI scripting. 😐

[@xikingxblack7687]

Thanks for this video! Today i made a similar macro for a transaction i need. But i would like to improve the macro a bit. I want it to check, whether SAP is already open, if not it should open SAP and log in with my username and password, if its already open, i should just start doing the steps Do you know how i could do this?

[@CustAndCode]

When it is possible to open the SAP GUI automatically it is necesarry to write your password plain in the macro. Thats not a good idea. But I don't know if that's even possible. If you find a solution I would be happy if you would share it. 🤗

[@xikingxblack7687]

@@CustAndCode wir können auf deutsch schreiben 😄 habe erst im Nachhinein gesehen dass du ein deutscher Kanal bist Eine Möglichkeit gibt es bestimmt. Frage ist wie aufwändig und wie gut sie verhebt. Mit der Eingabe des Passwords ins Makro mache ich mir keine Gedanken, da es lokal bei mir abgelegt wäre und nur ich es für wiederkehrende Auswertungen verwenden würde

[@CustAndCode]

Man könnte natürlich auch eine Variable hinterlegen. Dann müsste man halt das Passwort jedes mal eingeben aber das muss jeder selber entscheiden ich biete nur die technische Lösung 🤗😉