I just launched "SAP GUI Scripting: Understanding the Basics" ebook! If you are interested to learn more, check it out in the link below: joelting.com/sap-ebook/
Hi Joel, I am now able to get the macro to work. I want to convey my sincere gratitude for your videos. Your videos will help countless professionals enrich their skills and to be more productive in their work. Keep up the GREAT WORK.
Hi Joel, this video is a goldmine of information! The only one that clearly explains the steps. Please keep posting new videos on SAP automation and SAP data extraction. Is it possible to automatically upload the extracted multiple Excel files to a SharePoint folder?
Hi Shakeel, in my opinion, the simplest way is to sync your SharePoint folder to your local drive. Then, when you export and save the spreadsheet in the folder, it will be uploaded automatically to SharePoint.
Sir! You just saved me a lot of time. I needed this code: ValorBase = ActiveWorkbook.ActiveSheet.Range("F3").Value It was driving me crazy figuring out how to create this function in order to paste it into SAP. Anyway, thank you very very much!
Man, I wish I would have found this when you put it out 2 years ago. Great video. My only constructive feedback would be the volume. I had both my computer and YT volume on max, and it made it better, but I had to turn on CC to really follow you. Still, great job, and keep it up.
Thanks for your feedback! I have since upgraded my recording device. Hopefully my future videos will provide a better experience to you and everyone else. Thanks again!
HI Joel, You have really solved my problem , i really like the way you make difficult task easy enough for new comers to understand, i would appreciate if you can also explain how we can run multiple VB scripts for download various reports from SAP in one single click, i believe it will a piece of cake for you as usual .
Hi Mah H, Thanks for the kind words, glad that you find it helpful. As for your question about various reports in 1 go, you can simply string all the steps together. For example, right after the first report have been generated, continue the script to navigate to the 2nd tcode using "/n *your t-code*" in the navigation bar. I will put this in the list of new videos that I will be working on. Thanks for the suggestion! :)
Hi Joel! Is there any possibility to have this code run every workday of the week, excluding weekends, and at a certain hour, let's say 7:20 AM? Thank you!
Hello! Yes, it's possible to achieve this. You can schedule it locally using Windows Task Scheduler, which can run a VBS file that launches the workbook and macro. Another alternative is using Power Automate. If you have access to both Power Automate Desktop and Cloud, you can create a scheduled cloud flow to trigger a Power Automate Desktop (PAD) flow that open the Excel workbook and execute the macro.
Thanks Joel! Your video is straight forward and explained easily. I am unable to find the "SAP GUI Scripting API" reference noted in your video at 2:56mins, so do you know where or how I can obtain please? Cheers heaps!
Hey N, those variations are different. You can use the browse button and search for sapfewse.ocx in your SAP directory. I made a step by step video on how to enable it that you may refer to: czcams.com/video/7Rxh10Kt5v4/video.html Hopefully it will work for you.
Thanks for the great video :). Do you know how to change that excel does not start automatically when exporting data ? I ask because I extract a lot of data from SAP and every time I have to close the window with excel.
Hi Kamil, I think this is also one of the issues that I'm facing. Excel will automatically start after extraction. However, something interesting that I noticed is that if I string all the reports together when extracting multiple reports, Excel will only open once at the end and the Excel opened will only be the last spreadsheet that you are exporting. So, you can try it out and don't let it deter you from developing something. Meanwhile, I'll still be looking for a workaround. I'll let you know if I found one.
I am new to VBA and this was EXTREMELY helpful and easy to understand! I have one situation where I would like to use this tool to pull invoices out of SAP. The only problem I am having here is that the invoice opens in a PDF viewer (Edge) that is outside of SAP. Do you have any suggestions on how to add Edge as part of the VBA script?
Hi there. I wanted to clarify, is your invoice a file in the attachment list? You double click on it and it opens outside? Specifically, what Tcode are you using?
Hi Joel, Thank you for the amazing video 🙏 Question: Is it possible to run this process automatically without opening the excel sheet and clicking the start button?
Hi there, it's possible using Windows Task Scheduler to automatically open an Excel file, run it’s VBA code, save the file, and finally close the Excel file. You can try and Google "Automatically Run Excel VBA Macros" where you can find article from TheSpreadsheetGuru on this. You also need to incorporate code which helps you to login SAP. This will requires you to store your SAP user ID and password somewhere (which may pose security risk). Personally, I would not recommend doing this, unless you are clear with the risk involved.
Thank you Joel for this video. I just have one question if you could help please. Is there a way I can automate this report to be uploaded to sharepoint everyday at a fixed time? or Should I be logged in to SAP to get this done? Thank you.
Hi MrGautam92, it really depends on how this is being setup. If your company is not using Single Sign On in SAP, you can use Azure Key Vault to store your username and password, schedule a daily flow in Power Automate to run the automation. This should work as long as the laptop is on. However, if your company is using Single Sign On, most likely you need to log into SAP every time before you run your script. In that case, you would not be able to schedule it to run at a specific time daily. As of now, I am not able to find a workaround for cases where single sign on is being used.
Hi there. One workaround I found is that you can export the table in "Text with tabs" format as local file, then use VBA to read the file, paste all the data in Excel and process them into table format, and then save it as an Excel in the end. The code will be more complicated but if having the file opened at the end annoys you, this is one way to correct it.
Hi, Thanks for the amazing video. I would like to know how to loop this macro while it selects a new cell each loop. Maybe a next video? Greetings from The Netherlands!
Thank you so much for the explanation Joel😁 I do have a query- I would like to extract data from SAP but for current dates. For example, if I run the macro today it should pick up today's date tomorrow then tomorrow's date and so on. Could you please help me with this. Thank you!!
Hi. You can just declare a date variable, and then assign it to today's date. Then just pass the variable into the script where you want to input the date. The following code will return the today's date in VBA: Dim dtToday dtToday = Date
Hi there, it really depends on how you can to set this up. It depends on how SAP takes in the values. If it's a list, you would be better off having your value in list format already. If you need to split your value with comma, that can be done with VBA as well before passing the values into the script.
hi joel this is a great tutorial. may I ask, if the Script Recording and Playback option is not available, what should I do? do I need to raise a ticket with our IT to have access with this? or is there a simple way to enable it? hope you answer my question! thank you!
Hi there. Sry for the late reply. If Script Recording and Playback option is not available, it is most likely that it has been disabled in the server. Only way to have it enabled is via IT or your SAP administrator.
First I want to thank you a lot for the video. It really helped me :) and I really appreciate that you read and answer the comments. But I have another problem, maybe you can help me. I want to open a excel file in SAP. Here I have to select various options (country, year, sales, costs…) via macros. How can I automate that with vba? Because the screen recording of SAP doesn’t record what I’m selecting in the excel file…. Thank you again :)
Hi Gruber, thanks for the kind words. For automation via VBA, yes you can. Screen recording for SAP only record what is happening in SAP GUI. Anything outside of it will not be captured. To be more flexible about how you can retrieve information in Excel, you need to have a basic understanding of VBA. For example, if you want to get a value in cell A1, you can get it by using the following code: Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1").Value
Hi, there's a workaround I found, but it involves quitting the whole Excel application. You can add the following line at the end of your script. Application.Quit You can refer to this video where I briefly touched on this. czcams.com/video/G5qc2gcpyW4/video.html
Hi.. thanks for the excellent video.. I need to extract data for each month. So how should I make sure the dates are input. I need both ways. Macro to automatically change dates and also option to input the date( start date and end date).
Hi there. To ensure you have the dates needed for your data extraction, you can implement a check within your VBA script. Here's a general approach: In your VBA code, use conditional statements to check if the date cells are empty or contain valid dates. If the date values are missing or invalid, then determine the date automatically. You can define how your date is being determined here (ie one day prior or today etc) before running the script. If the date values have already been input, the script can continue with the data extraction using the user defined paramters.
I am not able to download the invoice from VF03 in scripting because scripting is only recording till PDF! after that script is not recording how to resolve this?
Hi Joel, When I try to save, it brings up Windows save box instead of SAP save box. Is there a setting I have to select to use SAP save options instead of Windows? The script record does not capture what is done in Windows Save. Thanks
Hey there. You could try checking in your Options to see if the "Show native Microsoft Windows Dialog" option is unticked. Another option is to export your data/table using "Text with Tabs" and process the text with VBA into tables in Excel. Usually exporting with "Text with Tabs" will always brings up the SAP save box.
Hi Folks, I am trying to determine if I have a list of parts in Excel is there a way to automate the SAP process of getting the SAP MM03 Standard Cost and Price Unit out? Rather than doing it individually for each item?
Hi there, yes it's possible. Just do a loop, for each row, get the part number and plant (if it's different) in Excel, navigate to MM03, run your script where it can input your part number to reach the standard cost page, then at the page, get the value using the script and input it as a cell value in Excel.
Hello Joel, this video is amazing! However I do not understand how to choose the between TEST and PRODUCTION environments of SAP, since I have access to both for work. Which specification is needed in the code? Thanks!
Hello Niccolò, Thanks! For the choice of Test and Production, are those 2 SAP sessions being accessed both at the same time? If it's not, then just make sure the right one is being open at the time of running the script. If there are 2 sessions and you would like to differentiate, checks can be done by going through objGui.Children object (checking the system name of the session) and identifying the correct sessions to connect to.
Hi Joel, I'm new to all of this and I have a question. I can't find the SAP GUI Scripting API in the references for the VBA tools to click on it. I don't know why it's not appearing like it does in the video.
Hi Pnorby, I'm not familiar with the RFC call function though. I did some search about it, seems like it's part of ABAP. I only have access to SAP GUI, which I use VBA to interact with.
Two problems as I am totally new to the VBA. Could not make this work as I understand I would need to change something in the templet - but what? Also the .ocx file does not exist in the SAP folders on my device. Not sure if this is a nice to have or is required to make it work. Either way, I could not make this work for me.
Hi there, the ocx file is required for VBA to understand the language. If it's not on your device, it may be that you are running SAP on Citrix or on virtual environment. If it's installed locally, usually you will be able to find it in the folder itself. You can try to check it on Windows Explorer.
Hello Joel, thank you very much for the video! It has helped me a lot!! I wanted to ask you, is it possible that I saved my script with the "save as" window, since as you know, it stops recording and does not record when I save the file. I need to save it that way so that the file is not modified once I download it
Hi there. I'm glad it helped you in some ways. The reason it's stop recording is because it's no longer part of SAP GUI already. There's some workaround using key strokes but sometimes it will break. Can you check if the settings "Show native Microsoft Windows dialog" under "Accessibility & Scripting" have been unticked. If it is already unticked, then the next alternative would be to try to export it in text format, then read the text and pass the data into Excel. Usually exporting in text allows SAP to continue the save process within the SAP GUI environment.
I used to have a custom desktop app that would scan SAP window for all Object tree structure and you can write a code without needing recording macro using better methods described in SAP scripting help
Hi there, 1 way is to have 2 SAP sessions opened. then run the execute button on each session one line after the other, that way, it would be as if they were ran at about the same time.
Hi Joel, thanks and your video is very helpful to automate SAP with excel. Just wondering if you have conducted courses in Singapore? I am keen to learn more macro for SAP automation to solve the problem in my department. Look forward to hear from you, thanks.
Hi there! Thanks for enquiring. Currently I have yet to conduct any formal courses in Singapore. I have plans for an online course about SAP GUI scripting in the nearest future. If you have some specific question that you would like to ask or you would like to have some private tutoring or onsite training, feel free to reach out to me via email.
Hey, usually, this file is installed together with SAP. There are some cases where they access SAP via Citrix. In that case, you probably do not have the file on your computer. Can you try navigating to the file path and see if there's any sign of the OCX file in Windows Explorer?
Hi, Your tutorial is very helpful. I have an issue while running this one. Public objGui As GuiApplication is showing as an error. Its saying Compiled error. user defined type - not defined. Could you please solve this issue ?
@@JoelTing Yes, i did. I have used SAP GUI Scripting for posting few journal entries before. Now i have given ' Public objGui As Object '. Now its showing a run time error 619- application defined or object defined error. Could you please solve this ?
Hi Ahmed Yahya, I'm not familiar with the read_text function module. I did some search about it, seems like it's part of ABAP. The scripts that I developed usually is just to replace the manual steps performed with SAP GUI.
Hi. Are you able to browse it in your SAP installation folder? You can try to use the browse button and search for sapfewse.ocx in your SAP directory. I made a step by step video on how to enable it that you may refer to: czcams.com/video/7Rxh10Kt5v4/video.html Hopefully it will work for you.
Hi Muhammad Danish, thanks for the video idea. Unfortunately, I do not have access to all the different modules to make videos relating to them at the moment.
This doesn't work for me because my SAP opens up the windows file browser when I try to export to excel. It doesn't give the option to name my file in SAP.
Hi Joel, I tried using your code template, but when I run the code, I get the error message "syntax error". Any idea on how to solve this problem? :D Thanks a lot!
Hi there. Are you able to pinpoint which line is causing the error message? Also make sure that SAP GUI Scripting API have already been enabled under "Reference". If this library is not enabled, it will gives syntax error.
@@JoelTing Hey, thanks for your feedback. The SAP GUI Scripting API is enabled. The line which causes the error seems to be: Set objGui = SapGuiAuto.GetScriptingEngine The error message is saying: Runtime error "-2147221020 (800401e4)" automating error invalid syntax Greetings! :)
@@seducative8796 I managed to reproduce the error when I don't have SAP logon open. But I think that's probably not your case. Are you connecting to SAP via citrix?
@@JoelTing Yes I can start citrix workspace and start SAP Logon from there. Normally I directly open SAP Logon without going to citrix workspace first. Does this make any difference? Greetings! :)
@@seducative8796 Not really. Usually it's when using citrix, the connection is not able to be made. But since you are going directly, that should not be the cause. Maybe you can send me an email and let's have a 5 minute quick call to see if this can be resolved or it's a company's policy matter.
Hi Joel, thanks for your video. Please tell me how do i make schedule of this VBA scripting so that it will get executed automatically at a certain frequency without any manual interference
Hi there. 1 option is to setup Power Automate to trigger the flow and have the flow run the macro in your Excel. However, I do not really recommend this as the automation usually do not covers the logging in of SAP. Having automation for SAP logon may open up to security risk as you need to have your username and password stored securely.
Hi there. Glad that it is working for you. Yes, it is possible. You can try to read the newly exported workbook, then copy all the data into one of the worksheet in your current workbook using vba.
Great video! I have a quick question though. I don't want to run a report but I just wanted to capture the screen shot. Let's say I run a T Code that contains "Header", "Operations" and "BOM". I just wanted to capture the screen shot of these three areas. Do you think it's possible? Thanks.
Hi there, you can capture screenshot using "keybd_event vbKeySnapshot, 1, 0, 0" to save this in your clipboard, then "ActiveSheet.Paste" to paste the screenshot in the desired location. My thought is once you are at the screen that you want to capture in SAP, run the keybd_event code, then paste it somewhere and proceed to the next steps.
Hi there, may I know which Tcode that is giving you this result? Also, can you check if the option "Show native Microsoft Windows dialogs" is unticked under Options > Accessibility & Scripting > Scripting?
How do you use this with SAP BI ( online)? We have a broadcaster function on our company SAP. To extract data to be used in Excel, we use a hyperlink which takes us to a prompt window and from there we choose the month for which we want to extract data for and the query presents itself in table format online. Then we export it to excel and work with it in excel. My question is how to automate this? How to get multiple reports set on broadcaster with dynamic prompt? Is there a way?
Hi Tara, SAP Scripting that is shown in this video only applicable for SAP GUI. For SAP that works using a browser, you may want to explore web browser automation with Power Automate Desktop or Python or UI Path.
Very good demo 🙏👍👍 I wonder if we can use the same method to extract a report that runs in background and edit “Excel in place” ? I tried several times but I couldn’t save it automatically as you did here. Thanks again
@Fady Anwar I believe this is due to the format that you have selected to export the spreadsheet. From my understanding, this happens when you are exporting in "Excel (In Existing XXL Format)". In your case, since there's no prompt for you to select, it may be that you have ticked the "Always Use Selected Format" option. To ensure that SAP prompts you to select the format "Excel (in Office XLSX Format)", you can try right click anywhere on the table in SAP you are trying to export and the prompt should be there. This time, change it to exporting in XLSX format instead.
Do you mean copy and pasting cell values from Excel to SAP GUI? For this video, I just store it as a variable and input them into the field. Let me know if you need further explanation on this.
This worked for me! :) my tcode only allows one year at a time. How do I get it to run the same report a second time with a different year nonstop. I know I can press the button twice and do the years separately but is there a way to loop the script but with different parameter second time?
Hi Vi Ha, you can contain your recorded SAP script in a loop, repeating the steps and inputting the list of years one by one during every run. I'll be working on a video soon explaining the concept in probably a few days. I'll let you know once the video is published.
Hello Joel, thanks for video. Not so many people are giving lessons on Sap automation. Great job! I have a question, how can I automate the weekly extraction (i.e from last week Monday till this week Monday) that I do every Monday.
Hi Gio. Thanks! If you would like to automate, one of the way to do it is to use the task scheduler, where you can set it up to run Excel and run the specified SAP Script every Monday. For the SAP Script, you just need to push in the latest date with the Now() formula and the start date to be 7 days/8 days less using the DateAdd method. Then, you will be able to run the report with the date parameters that you require.
@@JoelTing Thanks for coming back to me. Task scheduler part is clear. Im not sure about the SAP part. Could please somehow show it? If we take the steps as you showed in this video instead of Path and Country name I can have dates there. One will be always Monday and the other Monday - 7 days. Is this what you mean?
@@giogobronidze7788 That's one way to do it, to have the field prefilled. Another way to go about is just input the date based on your automation schedule. Let’s say your task is scheduled to run on 17th Jan, which will generate report from 11th Jan to 16th Jan. What I can try is to input the fields using Date() function, which will return the date of the day itself. objSess.findById("wnd[0]/usr/ctxtDateFrom").Text = Date() - 7 objSess.findById("wnd[0]/usr/ctxtDateTo").Text = Date() - 1 If at the time of running the script, it is on 17th Jan, the above 2 lines will be filled with 11th Jan and 16th Jan respectively.
Hi there. Usually this file is installed together with other SAP software. May I know if you are accessing SAP via citrix? Is the directory "C:\Program Files (x86)\SAP\FrontEnd\SAPgui" available in your computer?
Hi there, unfortunately, as of now, I'm not able to stop the downloaded file from opening automatically. If really needed, I would just close the whole excel instance with macro at the end.
Hello Joel, thank you for the tutorial. Can you help me, please? My Save as dialog box is not the same as yours (mine opens Windows Save as dialog box). The script didn't record that part.
Hi, can you try unchecking the "Show native MS Windows dialogs" option in the SAPGUI options > Accessibility & Scripting > Scripting? This may bypass the save as dialog box
Hi Gaz 5, you can try using the browse button and search for sapfewse.ocx in your SAP directory. I made a step by step video on how to enable it that you may refer to: czcams.com/video/7Rxh10Kt5v4/video.html Hopefully it will work for you.
I just launched "SAP GUI Scripting: Understanding the Basics" ebook! If you are interested to learn more, check it out in the link below:
joelting.com/sap-ebook/
Hi Joel, I am now able to get the macro to work.
I want to convey my sincere gratitude for your videos. Your videos will help countless professionals enrich their skills and to be more productive in their work. Keep up the GREAT WORK.
Awesome! Glad to hear that you get to make it work 😀
With these kinda videos you know that the word ''genious'' is badly used sometimes. Thank you, I hope to be able to do it myself :D
Thanks for your kind words! 😊
I made it for the 1st time. Thank you. 😊
Amazing Tutorial, simple explaining a complex matter.
Hope it was helpful!
it's my first time creating VBA and your tutorial works wonder, huge thanks!
Glad it helped!
Hi Joel, this video is a goldmine of information! The only one that clearly explains the steps. Please keep posting new videos on SAP automation and SAP data extraction. Is it possible to automatically upload the extracted multiple Excel files to a SharePoint folder?
Hi Shakeel, in my opinion, the simplest way is to sync your SharePoint folder to your local drive. Then, when you export and save the spreadsheet in the folder, it will be uploaded automatically to SharePoint.
Sir! You just saved me a lot of time. I needed this code: ValorBase = ActiveWorkbook.ActiveSheet.Range("F3").Value
It was driving me crazy figuring out how to create this function in order to paste it into SAP. Anyway, thank you very very much!
I gave this video a like, but I wish i could give it a LOVE!!! This is amazing, THANK YOU!
Thanks for the kind word Jessenia!
Hi Joel, Awesome presentation. The information provided in the video is fabulous. Thank you!
Man, I wish I would have found this when you put it out 2 years ago. Great video. My only constructive feedback would be the volume. I had both my computer and YT volume on max, and it made it better, but I had to turn on CC to really follow you. Still, great job, and keep it up.
Thanks for your feedback! I have since upgraded my recording device. Hopefully my future videos will provide a better experience to you and everyone else. Thanks again!
Hi Joel, thanks for your video. I was looking for Excel VBA for beginners and I found your video which is very useful and easy to understand.
Glad it was helpful!
HI Joel, You have really solved my problem , i really like the way you make difficult task easy enough for new comers to understand, i would appreciate if you can also explain how we can run multiple VB scripts for download various reports from SAP in one single click, i believe it will a piece of cake for you as usual .
Hi Mah H,
Thanks for the kind words, glad that you find it helpful. As for your question about various reports in 1 go, you can simply string all the steps together. For example, right after the first report have been generated, continue the script to navigate to the 2nd tcode using "/n *your t-code*" in the navigation bar.
I will put this in the list of new videos that I will be working on. Thanks for the suggestion! :)
Hi Brother!!!
You are truly amazing! For distribute such rich knowledge in a simple and right way...
Congratulations!!!
Thanks for the kind word!
Hi Joel, this is a great video. Thank you for sharing your valuable expertise.
Hello Joel,
Thank you so much for this lesson. I was able to use it. You have my love and respect. God bless you!!.
What a great Video
The explanations are so clear. Thank you very much!!
Excellent video. Very interesting option and I will be looking for ways to apply this to my day to day work. Thanks
It's exactly what I need. Very helpful. Thank you.
I'm extremely grateful for you. Thank you very much.
Exactly what I needed!!!
Hello Joel, Thank you so much , this video is very amazing and helpful, i am very grateful for your great work.
Thank you so much! I think I might be able to solve my problem now.
What an amazing video that was! Thank you so much!
Great job as always Joel. Keep up the good work!
Thanks again!
Hi Joel, when i run the VBA script. it throw run time error (Method Item of object "ISapCollectionTarget" failed), any suggestion? Thanks.
Hi there, can you share which line of code is giving you the error?
@@JoelTing after i click 'debug', it highlight this line: Set session = objConn.Children(0)
Thanks for good video. Very clear & useful.
Awesome! well explained
Hi Joel! Is there any possibility to have this code run every workday of the week, excluding weekends, and at a certain hour, let's say 7:20 AM? Thank you!
Hello! Yes, it's possible to achieve this. You can schedule it locally using Windows Task Scheduler, which can run a VBS file that launches the workbook and macro. Another alternative is using Power Automate. If you have access to both Power Automate Desktop and Cloud, you can create a scheduled cloud flow to trigger a Power Automate Desktop (PAD) flow that open the Excel workbook and execute the macro.
Very Very useful. Thanks a lot
Thanks Joel! Your video is straight forward and explained easily. I am unable to find the "SAP GUI Scripting API" reference noted in your video at 2:56mins, so do you know where or how I can obtain please? Cheers heaps!
I do have variations of the references being "SAPGUI ApiHooker", "SAPGUI LSAP 1.0 Type Library", etc. Are these the same Joel?
Hey N, those variations are different. You can use the browse button and search for sapfewse.ocx in your SAP directory.
I made a step by step video on how to enable it that you may refer to:
czcams.com/video/7Rxh10Kt5v4/video.html
Hopefully it will work for you.
@@JoelTing thank you so much Joel! I will give it a try when i am back in the office next week. Fingers crossed and appreciate your help!
amazing, it´s incredible
That is what i was looking for ¬¬¬¬ Thanks!!!!
Thank you, thanks a TON.
Interesting ................thanks for sharing
Amazing
Fantastic video! ❤
You are the best!
Thanks for the great video :). Do you know how to change that excel does not start automatically when exporting data ? I ask because I extract a lot of data from SAP and every time I have to close the window with excel.
Hi Kamil, I think this is also one of the issues that I'm facing. Excel will automatically start after extraction. However, something interesting that I noticed is that if I string all the reports together when extracting multiple reports, Excel will only open once at the end and the Excel opened will only be the last spreadsheet that you are exporting. So, you can try it out and don't let it deter you from developing something.
Meanwhile, I'll still be looking for a workaround. I'll let you know if I found one.
excellent video
You are truly Amazing
I am new to VBA and this was EXTREMELY helpful and easy to understand! I have one situation where I would like to use this tool to pull invoices out of SAP. The only problem I am having here is that the invoice opens in a PDF viewer (Edge) that is outside of SAP. Do you have any suggestions on how to add Edge as part of the VBA script?
Hi there. I wanted to clarify, is your invoice a file in the attachment list? You double click on it and it opens outside? Specifically, what Tcode are you using?
@@JoelTing Exactly! I am using FB03. It is an invoice in the attachment list and then I double click the PDF, which then opens in Edge.
Hi Joel,
Thank you for the amazing video 🙏
Question: Is it possible to run this process automatically without opening the excel sheet and clicking the start button?
Hi there, it's possible using Windows Task Scheduler to automatically open an Excel file, run it’s VBA code, save the file, and finally close the Excel file. You can try and Google "Automatically Run Excel VBA Macros" where you can find article from TheSpreadsheetGuru on this. You also need to incorporate code which helps you to login SAP. This will requires you to store your SAP user ID and password somewhere (which may pose security risk). Personally, I would not recommend doing this, unless you are clear with the risk involved.
Thank you Joel for this video. I just have one question if you could help please.
Is there a way I can automate this report to be uploaded to sharepoint everyday at a fixed time? or Should I be logged in to SAP to get this done?
Thank you.
Hi MrGautam92, it really depends on how this is being setup. If your company is not using Single Sign On in SAP, you can use Azure Key Vault to store your username and password, schedule a daily flow in Power Automate to run the automation. This should work as long as the laptop is on.
However, if your company is using Single Sign On, most likely you need to log into SAP every time before you run your script. In that case, you would not be able to schedule it to run at a specific time daily. As of now, I am not able to find a workaround for cases where single sign on is being used.
The problem we stil have here is that the export excel file cannot be closed using VBA
Hi there. One workaround I found is that you can export the table in "Text with tabs" format as local file, then use VBA to read the file, paste all the data in Excel and process them into table format, and then save it as an Excel in the end. The code will be more complicated but if having the file opened at the end annoys you, this is one way to correct it.
Great video - thank you :)
Hi, Thanks for the amazing video. I would like to know how to loop this macro while it selects a new cell each loop. Maybe a next video? Greetings from The Netherlands!
Hi Bart de Vries, let me see how I can incorporate this in my future videos. Thanks for the suggestion!
Awesome
Thank you so much for the explanation Joel😁
I do have a query- I would like to extract data from SAP but for current dates. For example, if I run the macro today it should pick up today's date tomorrow then tomorrow's date and so on. Could you please help me with this. Thank you!!
Hi. You can just declare a date variable, and then assign it to today's date. Then just pass the variable into the script where you want to input the date. The following code will return the today's date in VBA:
Dim dtToday
dtToday = Date
Thank you Joel , this is really useful. can you tell me how to give more than one value as input . Can we give with comma separated ?
Hi there, it really depends on how you can to set this up. It depends on how SAP takes in the values. If it's a list, you would be better off having your value in list format already. If you need to split your value with comma, that can be done with VBA as well before passing the values into the script.
Informative video
hi joel this is a great tutorial. may I ask, if the Script Recording and Playback option is not available, what should I do? do I need to raise a ticket with our IT to have access with this? or is there a simple way to enable it? hope you answer my question! thank you!
Hi there. Sry for the late reply. If Script Recording and Playback option is not available, it is most likely that it has been disabled in the server. Only way to have it enabled is via IT or your SAP administrator.
Thank you so much 😍
First I want to thank you a lot for the video. It really helped me :) and I really appreciate that you read and answer the comments.
But I have another problem, maybe you can help me. I want to open a excel file in SAP. Here I have to select various options (country, year, sales, costs…) via macros. How can I automate that with vba? Because the screen recording of SAP doesn’t record what I’m selecting in the excel file….
Thank you again :)
Hi Gruber, thanks for the kind words. For automation via VBA, yes you can. Screen recording for SAP only record what is happening in SAP GUI. Anything outside of it will not be captured.
To be more flexible about how you can retrieve information in Excel, you need to have a basic understanding of VBA. For example, if you want to get a value in cell A1, you can get it by using the following code:
Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1").Value
Thank you very much for this video its help me alot .but there is one problem ,is there a way to close excel file after export from SAP
Hi, there's a workaround I found, but it involves quitting the whole Excel application. You can add the following line at the end of your script.
Application.Quit
You can refer to this video where I briefly touched on this.
czcams.com/video/G5qc2gcpyW4/video.html
Thanks a lot for sharing such a wonderful session. Will the coding part be the same for all the table data extracts?
Hi, the process will be similar. You need to record the script from your side, then use that script as a base in VBA and modify base on your needs.
@@JoelTing Thanks for the update. I'm ZERO in coding part. But I'll surely check with my ABAP counterpart to give a try
Hi.. thanks for the excellent video.. I need to extract data for each month. So how should I make sure the dates are input. I need both ways. Macro to automatically change dates and also option to input the date( start date and end date).
Hi there. To ensure you have the dates needed for your data extraction, you can implement a check within your VBA script.
Here's a general approach:
In your VBA code, use conditional statements to check if the date cells are empty or contain valid dates. If the date values are missing or invalid, then determine the date automatically. You can define how your date is being determined here (ie one day prior or today etc) before running the script. If the date values have already been input, the script can continue with the data extraction using the user defined paramters.
I am not able to download the invoice from VF03 in scripting because scripting is only recording till PDF! after that script is not recording how to resolve this?
Hello! You can try and check if this video is applicable for your case to extract PDF with VBA.
czcams.com/video/OG54SWpPfDs/video.html
Hi Joel, When I try to save, it brings up Windows save box instead of SAP save box. Is there a setting I have to select to use SAP save options instead of Windows? The script record does not capture what is done in Windows Save. Thanks
Hey there. You could try checking in your Options to see if the "Show native Microsoft Windows Dialog" option is unticked. Another option is to export your data/table using "Text with Tabs" and process the text with VBA into tables in Excel. Usually exporting with "Text with Tabs" will always brings up the SAP save box.
Error shown : User-defined type not defined
How we solve this error
Hi there, have you added SAP GUI Scripting API under reference in the VBA editor?
Hi Folks, I am trying to determine if I have a list of parts in Excel is there a way to automate the SAP process of getting the SAP MM03 Standard Cost and Price Unit out? Rather than doing it individually for each item?
Hi there, yes it's possible. Just do a loop, for each row, get the part number and plant (if it's different) in Excel, navigate to MM03, run your script where it can input your part number to reach the standard cost page, then at the page, get the value using the script and input it as a cell value in Excel.
Nice video
You are a genius thank you so much
Hello Joel, this video is amazing! However I do not understand how to choose the between TEST and PRODUCTION environments of SAP, since I have access to both for work. Which specification is needed in the code?
Thanks!
Hello Niccolò,
Thanks! For the choice of Test and Production, are those 2 SAP sessions being accessed both at the same time?
If it's not, then just make sure the right one is being open at the time of running the script. If there are 2 sessions and you would like to differentiate, checks can be done by going through objGui.Children object (checking the system name of the session) and identifying the correct sessions to connect to.
Thank you!
Hi Joel, I'm new to all of this and I have a question. I can't find the SAP GUI Scripting API in the references for the VBA tools to click on it. I don't know why it's not appearing like it does in the video.
Hi Lucas, have you tried "Browse" to locate it? I covered the steps in more details in my other video.
czcams.com/video/7Rxh10Kt5v4/video.html
@@JoelTing That worked like a charm! Thanks a bunch for your help. Take care!
Thanks
Thank you!
Great video! Loved it!
Could you also do one with an RFC call function please?
Hi Pnorby, I'm not familiar with the RFC call function though. I did some search about it, seems like it's part of ABAP. I only have access to SAP GUI, which I use VBA to interact with.
Two problems as I am totally new to the VBA. Could not make this work as I understand I would need to change something in the templet - but what? Also the .ocx file does not exist in the SAP folders on my device. Not sure if this is a nice to have or is required to make it work. Either way, I could not make this work for me.
Hi there, the ocx file is required for VBA to understand the language. If it's not on your device, it may be that you are running SAP on Citrix or on virtual environment. If it's installed locally, usually you will be able to find it in the folder itself. You can try to check it on Windows Explorer.
Hello Joel, thank you very much for the video! It has helped me a lot!! I wanted to ask you, is it possible that I saved my script with the "save as" window, since as you know, it stops recording and does not record when I save the file. I need to save it that way so that the file is not modified once I download it
Hi there. I'm glad it helped you in some ways. The reason it's stop recording is because it's no longer part of SAP GUI already. There's some workaround using key strokes but sometimes it will break. Can you check if the settings "Show native Microsoft Windows dialog" under "Accessibility & Scripting" have been unticked. If it is already unticked, then the next alternative would be to try to export it in text format, then read the text and pass the data into Excel. Usually exporting in text allows SAP to continue the save process within the SAP GUI environment.
I used to have a custom desktop app that would scan SAP window for all Object tree structure and you can write a code without needing recording macro using better methods described in SAP scripting help
Interesting. What is the custom desktop app? Is it the built in Inspect tool in Windows?
@@JoelTing that one used to work till 2011 I think. Then Microsoft phased it out. I'll try to find the name of the tool. It was 10 years ago so ..
@Joel, lovely videos. What if I have to run more than one tcodes at a time where there should not be timing differences. How can I go about this?
Hi there, 1 way is to have 2 SAP sessions opened. then run the execute button on each session one line after the other, that way, it would be as if they were ran at about the same time.
Hi Joel, thanks and your video is very helpful to automate SAP with excel. Just wondering if you have conducted courses in Singapore? I am keen to learn more macro for SAP automation to solve the problem in my department.
Look forward to hear from you, thanks.
Hi there! Thanks for enquiring. Currently I have yet to conduct any formal courses in Singapore. I have plans for an online course about SAP GUI scripting in the nearest future. If you have some specific question that you would like to ask or you would like to have some private tutoring or onsite training, feel free to reach out to me via email.
Dear joel,
Could you please creat video how to creat vba SAP AS01 Create Asset Master Record all data from excel file
Thank you
Let me see what I can do. Thanks for the suggestion!
Hey,
I do not have the the SAP GUI API option available in Excel. I also cannot find the ocx file. Is there something i am missing?
Hey, usually, this file is installed together with SAP. There are some cases where they access SAP via Citrix. In that case, you probably do not have the file on your computer. Can you try navigating to the file path and see if there's any sign of the OCX file in Windows Explorer?
Hi, Your tutorial is very helpful. I have an issue while running this one.
Public objGui As GuiApplication is showing as an error. Its saying Compiled error. user defined type - not defined. Could you please solve this issue ?
Hello! Have you enabled "SAP GUI Scripting API" under References?
@@JoelTing Yes, i did. I have used SAP GUI Scripting for posting few journal entries before. Now i have given ' Public objGui As Object '. Now its showing a run time error 619- application defined or object defined error. Could you please solve this ?
great explanation, thanks a lot. would you please explain to to extract long texts from read_text function module to excel ?
Hi Ahmed Yahya, I'm not familiar with the read_text function module. I did some search about it, seems like it's part of ABAP. The scripts that I developed usually is just to replace the manual steps performed with SAP GUI.
Hello ! Thank you for this awesome video ! Guys could you tell me which version of SAP is it ? HANA or FICO ?
Hi there! The version shown here does not contain any of the modules. Just a bare SAP built for ABAP developer.
Hi, I have a question?, I can't refer to SAP GUI Scripting API in excel cause I don't have it, How can I add it
Hi. Are you able to browse it in your SAP installation folder? You can try to use the browse button and search for sapfewse.ocx in your SAP directory.
I made a step by step video on how to enable it that you may refer to:
czcams.com/video/7Rxh10Kt5v4/video.html
Hopefully it will work for you.
Hi Joel, thanks for this great knowledge sharing. Does Epicor have the same feature to record script like this in SAP?
Hello! I have not used Epicor before. So I'm probably not the person to advise you on this.
Please make a video on how to change a vendor master specific Field in mass through vb script in SAP
Thanks
Hi Muhammad Danish, thanks for the video idea. Unfortunately, I do not have access to all the different modules to make videos relating to them at the moment.
@@JoelTing we will wait for this thanks 👍
This doesn't work for me because my SAP opens up the windows file browser when I try to export to excel. It doesn't give the option to name my file in SAP.
Hi there. Do you have "Show native Microsoft Windows dialogs" enabled in your option?
Hi, muchas gracias me funcionó a la perfección
Thank you for the kind words!
Hi Joel,
I tried using your code template, but when I run the code, I get the error message "syntax error".
Any idea on how to solve this problem? :D
Thanks a lot!
Hi there. Are you able to pinpoint which line is causing the error message? Also make sure that SAP GUI Scripting API have already been enabled under "Reference". If this library is not enabled, it will gives syntax error.
@@JoelTing Hey, thanks for your feedback.
The SAP GUI Scripting API is enabled.
The line which causes the error seems to be:
Set objGui = SapGuiAuto.GetScriptingEngine
The error message is saying: Runtime error "-2147221020 (800401e4)"
automating error
invalid syntax
Greetings! :)
@@seducative8796 I managed to reproduce the error when I don't have SAP logon open. But I think that's probably not your case. Are you connecting to SAP via citrix?
@@JoelTing Yes I can start citrix workspace and start SAP Logon from there. Normally I directly open SAP Logon without going to citrix workspace first.
Does this make any difference?
Greetings! :)
@@seducative8796 Not really. Usually it's when using citrix, the connection is not able to be made. But since you are going directly, that should not be the cause. Maybe you can send me an email and let's have a 5 minute quick call to see if this can be resolved or it's a company's policy matter.
Hi Joel, thanks for your video. Please tell me how do i make schedule of this VBA scripting so that it will get executed automatically at a certain frequency without any manual interference
Hi there. 1 option is to setup Power Automate to trigger the flow and have the flow run the macro in your Excel. However, I do not really recommend this as the automation usually do not covers the logging in of SAP. Having automation for SAP logon may open up to security risk as you need to have your username and password stored securely.
Hi Joel thanks for the video! is working! Is it possible to run the button and have the data downloaded in the same excel?
Hi there. Glad that it is working for you. Yes, it is possible. You can try to read the newly exported workbook, then copy all the data into one of the worksheet in your current workbook using vba.
Great video! I have a quick question though. I don't want to run a report but I just wanted to capture the screen shot. Let's say I run a T Code that contains "Header", "Operations" and "BOM". I just wanted to capture the screen shot of these three areas. Do you think it's possible? Thanks.
Hi there, you can capture screenshot using "keybd_event vbKeySnapshot, 1, 0, 0" to save this in your clipboard, then "ActiveSheet.Paste" to paste the screenshot in the desired location. My thought is once you are at the screen that you want to capture in SAP, run the keybd_event code, then paste it somewhere and proceed to the next steps.
Hi there, I just released a video for taking SAP Screenshot with VBA.
czcams.com/video/lFLIwcRi0_U/video.html
@@JoelTing Excellent! Thank you so much!
Awesome bro
The file path to save the Excel is not getting recorded how to change the dialogue box so that it gets recorded by macro, please help
Hi there, may I know which Tcode that is giving you this result? Also, can you check if the option "Show native Microsoft Windows dialogs" is unticked under Options > Accessibility & Scripting > Scripting?
How do you use this with SAP BI ( online)? We have a broadcaster function on our company SAP. To extract data to be used in Excel, we use a hyperlink which takes us to a prompt window and from there we choose the month for which we want to extract data for and the query presents itself in table format online. Then we export it to excel and work with it in excel. My question is how to automate this? How to get multiple reports set on broadcaster with dynamic prompt? Is there a way?
Hi Tara, SAP Scripting that is shown in this video only applicable for SAP GUI.
For SAP that works using a browser, you may want to explore web browser automation with Power Automate Desktop or Python or UI Path.
Very good demo 🙏👍👍
I wonder if we can use the same method to extract a report that runs in background and edit “Excel in place” ? I tried several times but I couldn’t save it automatically as you did here.
Thanks again
Hi Fady.
Do you mean when you are exporting the spreadsheet, Excel will start on it's own, displaying the export and you have to save it manually?
@@JoelTing exactly. The gui script runs as it should and stops at the file save step when using edit excel in place. Is there a work around for this?
@Fady Anwar
I believe this is due to the format that you have selected to export the spreadsheet. From my understanding, this happens when you are exporting in "Excel (In Existing XXL Format)". In your case, since there's no prompt for you to select, it may be that you have ticked the "Always Use Selected Format" option.
To ensure that SAP prompts you to select the format "Excel (in Office XLSX Format)", you can try right click anywhere on the table in SAP you are trying to export and the prompt should be there. This time, change it to exporting in XLSX format instead.
Thank you for this video where did you get that copy paste one could you explain us it's very helpful
Do you mean copy and pasting cell values from Excel to SAP GUI? For this video, I just store it as a variable and input them into the field. Let me know if you need further explanation on this.
This is great
This worked for me! :) my tcode only allows one year at a time. How do I get it to run the same report a second time with a different year nonstop. I know I can press the button twice and do the years separately but is there a way to loop the script but with different parameter second time?
Hi Vi Ha, you can contain your recorded SAP script in a loop, repeating the steps and inputting the list of years one by one during every run. I'll be working on a video soon explaining the concept in probably a few days. I'll let you know once the video is published.
Hello Joel, thanks for video. Not so many people are giving lessons on Sap automation. Great job! I have a question, how can I automate the weekly extraction (i.e from last week Monday till this week Monday) that I do every Monday.
Hi Gio. Thanks! If you would like to automate, one of the way to do it is to use the task scheduler, where you can set it up to run Excel and run the specified SAP Script every Monday. For the SAP Script, you just need to push in the latest date with the Now() formula and the start date to be 7 days/8 days less using the DateAdd method. Then, you will be able to run the report with the date parameters that you require.
@@JoelTing Thanks for coming back to me. Task scheduler part is clear. Im not sure about the SAP part. Could please somehow show it? If we take the steps as you showed in this video instead of Path and Country name I can have dates there. One will be always Monday and the other Monday - 7 days. Is this what you mean?
@@giogobronidze7788 That's one way to do it, to have the field prefilled.
Another way to go about is just input the date based on your automation schedule. Let’s say your task is scheduled to run on 17th Jan, which will generate report from 11th Jan to 16th Jan. What I can try is to input the fields using Date() function, which will return the date of the day itself.
objSess.findById("wnd[0]/usr/ctxtDateFrom").Text = Date() - 7
objSess.findById("wnd[0]/usr/ctxtDateTo").Text = Date() - 1
If at the time of running the script, it is on 17th Jan, the above 2 lines will be filled with 11th Jan and 16th Jan respectively.
can you do a video on how automate ourchase info record (PIR) please, thank you
Hi, which aspect are you looking at? Input of data into PIR? or extraction of PIR data?
Hi Joel, thank you for your video! Would like to ask If I can not find ocx file from reference library, may I know what should I do?
Hi there. Usually this file is installed together with other SAP software. May I know if you are accessing SAP via citrix? Is the directory "C:\Program Files (x86)\SAP\FrontEnd\SAPgui" available in your computer?
@@JoelTingsame error for me. As I mentioned in another comment I have SAP installed on my system.
Did you see when the extraction happened the downloaded file opens up automatically, how to stop that?
Hi there, unfortunately, as of now, I'm not able to stop the downloaded file from opening automatically. If really needed, I would just close the whole excel instance with macro at the end.
Hello Joel, thank you for the tutorial. Can you help me, please? My Save as dialog box is not the same as yours (mine opens Windows Save as dialog box). The script didn't record that part.
Hi, can you try unchecking the "Show native MS Windows dialogs" option in the SAPGUI options > Accessibility & Scripting > Scripting? This may bypass the save as dialog box
Hey, this is a great video!
Unfortunately I cannot find the SAP GUI Scripting API, what should I do?
Hi Gaz 5, you can try using the browse button and search for sapfewse.ocx in your SAP directory.
I made a step by step video on how to enable it that you may refer to:
czcams.com/video/7Rxh10Kt5v4/video.html
Hopefully it will work for you.