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

Подписаться 9 тыс.

Просмотров 23 тыс.

50% 1

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.

Опубликовано:

30 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 98

@TheFundamentalChristian 6 месяцев назад

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 6 месяцев назад

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

@cegaraujo 11 месяцев назад

Very didatic video. Thanks a lot!

@CustAndCode 11 месяцев назад

Thanks for your feedback and thanks for watching! 🤗😀

@pritirawat2616 11 месяцев назад

This was very helpful!

@CustAndCode 11 месяцев назад

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

@covid699 3 месяца назад

Helped a ton. Thank you

@CustAndCode 3 месяца назад

You are welcome! 🤗 and thanks for watching! 🤗

@jeslaravishuda 7 месяцев назад

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

@CustAndCode 6 месяцев назад

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

@vijaysenthil7023 9 месяцев назад

Thanks for your vedio its very easy to understand

@CustAndCode 9 месяцев назад

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 6 месяцев назад

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

@CustAndCode 6 месяцев назад

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

@mrronaldino7298 10 месяцев назад

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 10 месяцев назад

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

@user-zf2iv7vb3i 8 месяцев назад

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 8 месяцев назад

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 11 месяцев назад

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 11 месяцев назад

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

@user-io8ug3pj7z 9 месяцев назад

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 9 месяцев назад

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

@TheWanderercontent 3 месяца назад

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

@CustAndCode 3 месяца назад

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 3 месяца назад

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

@CustAndCode 3 месяца назад

@@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 10 месяцев назад

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 10 месяцев назад

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

@DavidRelphWorkLife 2 месяца назад

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? 🤔

@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. 🤗

@carlaarenas6537 10 месяцев назад

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 10 месяцев назад

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" ? 🤗

@satyammalhotra6503 8 месяцев назад

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 8 месяцев назад

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

@satyammalhotra6503 8 месяцев назад

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

@user-ih5lc3nw5i 9 месяцев назад

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

@CustAndCode 9 месяцев назад

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

@nathanmartin66 9 месяцев назад

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 9 месяцев назад

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 9 месяцев назад

@@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 9 месяцев назад

@@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!

@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. 🤗😀

@ahmadghozie8686 10 месяцев назад

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 10 месяцев назад

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

@chauhannaman98 7 месяцев назад

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 6 месяцев назад

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 4 месяца назад

@@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 4 месяца назад

@@alsantour8835 you have already restarted the SAP GUI?

@alsantour8835 4 месяца назад

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

@CustAndCode 2 месяца назад

Good to hear! 🤗

@lukban1825 5 месяцев назад

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 5 месяцев назад

Thank you for your feedback! I appreciate it! 😀

@Bitza00 9 месяцев назад

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

@CustAndCode 9 месяцев назад

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

@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 5 месяцев назад

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 🤗😉

@jaybeid490 8 месяцев назад

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

@CustAndCode 8 месяцев назад

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

@jaybeid490 8 месяцев назад

@@CustAndCode thanks, let me try

@CustAndCode 8 месяцев назад

@@jaybeid490 Good luck! 🤗

@RaMeShBaBu-gf1wc 6 месяцев назад

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 6 месяцев назад

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

@RaMeShBaBu-gf1wc 6 месяцев назад

@@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 6 месяцев назад

@@RaMeShBaBu-gf1wc 🤗🤗🤗

@AmitKumar-cs2gx Месяц назад

May use it in combination with power automate

@Kruse1 5 месяцев назад

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

@CustAndCode 5 месяцев назад

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

@Kruse1 5 месяцев назад

@@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 5 месяцев назад

@@Kruse1 Then, have fun with scripting 🤗

@Kruse1 4 месяца назад

@@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 2 месяца назад

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