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.
This is super brilliant presentation and very usefull for simple SAP user! Mr you are awesom! If you also could show how to extract several different reports and to save them into one work book, on different sheets. ❤
I'll try to work out some content on this. The general idea is once you have all the paths of the different reports after the export, you can just open them with VBA, then transfer them over into your current workbook. Another way to do it is via Power Query where you can setup data transformation steps and load it into your current workbook. We can use VBA to dynamically change the source into the newly extracted files.
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! :)
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: th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html Hopefully it will work for you.
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?
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.
Hey Joel, this is what I needed. Great share on this video. So, my question is, what if i want to have my collogues perform this and share the spreadsheet with them. With SAP logon, that would cause a Run-Time error "the Control could not be found by ID. how do we go about doing that?
@@thydinh20 hello! This error seems to show your colleagues page is not the same as yours. It is not navigating to the correct page. For your script, did you include "/n" at the front of the Tcode when running it? Sometimes, your screen could be on other Tcode. Running another Tcode without "/n" will result in the interface being at the wrong page, resulting in the script not being able to perform what it needs to do.
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.
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 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.
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.
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 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.
Hi Joel thanks for the vid ive been automating a lot of functions with this. one problem i have now is when i have multiple tabs of SAP open that have different SID. sometimes i run it and it executes the actions in a different SID not for S4HANA. is there a way to specify which SID it runs on?
Hello Jude. Yes, you can loop through all the system IDs available using objGUI.Children(i) to check the SID description before connecting to it. If you have multiple, and want to work with them individually by assigning each SID into individual objects.
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. th-cam.com/video/G5qc2gcpyW4/w-d-xo.html
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.
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.. 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.
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.
@@JoelTing One more question: is there a possibility that, before I run the script, to run anonther script to launch SAP GUI? Without SAP running, it gives me an error.
@@classiccarpov2311 Hi, yes it's possible. You can run it via sapshcut.exe. You can check out my previous video here for reference. th-cam.com/video/C_7UD_l-zwA/w-d-xo.html Although I am using Power Automate in the video, the same concept applies and you can call commands using VBA. That being said, you would need to store the password somewhere for the script to access, which could expose yourself to security risk. Unless the users know what they are doing and how to safely store their credentials in KeePass, I would not recommend to automate this portion of the process.
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.
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.
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: th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html Hopefully it will work for you.
Hi Joel, I know I'm damn late but still want to ask you 2 questions: 1. I want to add multiple values for an input (plant --> 001, 002, 003). How can I customize it with coding? I tried For each for it only got the last value. 2. At the last step, I clicked "Generate" as I save the file for the first time. If running macro to refresh that file, VBA will tell me the file has existed. Do I need to run a side module to delete the current file before running it, or any solutions?
Hello! 1. Try and debug step by step and see what's wrong in the loop. You should be able to pinpoint which part of your code goes wrong. I usually use For next loop and that has been working well for me. Alternatively, if you have multiple values, you can make use of the clipboard as well. Customize the clipboard with the input values, then in SAP use the paste from clipboard button. 2. Deleting the current file is one way to do it. Check if the file is already exist, if it is then delete it. Another alternative you can think about is to use the current time as part of the file name. Then, you would be sure that whenever you run the script, the file name is always unique.
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.
HI Joel , Nice Video. Can you suggest how do we add a Data below the existing data. Example I have a Sap report in a file till 15th of the month. When I run the macro it should only paste data after 15th till date in the sheet.
Hi Abhishek, perhaps you can try the following flow process when building your sub procedure: After you generate SAP report, open Workbook with existing data -> Identify max value of date column within existing data -> Filter date column of SAP report to be greater than max value of existing data date -> identify last row of existing data -> paste in data starting from row number (last row + 1) Hope this helps!
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.
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?
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 Joel, great video and easy to follow. I had a VBA code running based on Excel 2016 and after updating to 365 I encountered that my code won´t run. It was compiled to open a userform to enter the User and Pass and then connect to SAP. Is there any chance you can teach us how to do that in Excel. (I am not able to get power automate unfortunately) Thank you!!
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.
HI Joel, I know this video is a year old, but the information is very useful. I tried SAP recording, but it does not save it. I cant progress... Any thoughts?
For SAP recording, what do you mean you can't save it? The script does not save after recording? Have you tried saving to a different directory (like Desktop or your Documents folder)?
Hi Joel, great video as always. Can you comment on a situation where I would paste a list of items into the parameters such as a list of cost centers or a list of WBS elements to generate monthly reports. How would I write the vba script for that ? A range perhaps? I am not a VBA coder. My extent with VBA is using the Macro recorder.
Hi Steve Johnson, to copy a list of items, you can copy the range into clipboard by by referencing the range with the following code in VBA (eg. {workbook}.{worksheet}.Range("A2:A99").Copy) Subsequently, you would use the "Upload from clipboard" button in SAP (you can get the code with SAP Script recording) to paste all your values in. Hope this helps!
@@stevejohnson5033 Not really. When the copy instruction is executed, it should save it into the clipboard and it will be used to populate the multiple selection box in SAP.
@@JoelTing Thanks for getting back to me. I now have a issue locating the SAP Script API. I can not locate it. Can I sent you a screen shot of what I am seeing?
@@stevejohnson5033 SAP Script API ocx file can be found in usually in the SAP installation folder (C:\Program Files (x86)\SAP\frontend\sapgui\sapfewse.ocx). Click on the browse button in the reference window. At the bottom right of the window, make sure to change the file type to ActiveX control (*.ocx). If you are still not able to find, feel free to send me the screenshot and I'll get back to you.
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.
Hello Joel, Great video and work! can you make another example but with a date selection interval for a report? (I can't populate a specific month report cause in the macro recording it records a specific interval, how can I bypass that or work arround ? ) thanks!
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.
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!
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! 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.
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.
Hello! If you're looking to run multiple sessions simultaneously, with each session executing a specific GL code, that's definitely possible. To achieve this, you can modify the connection children index when connecting to SAP. For instance, you can use objConn.Children(0) to connect to the first session, objConn.Children(1) to connect to the second session, and so on. By doing so, you can instruct your script to run distinct GL code on separate sessions, enabling parallel processing.
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.
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, 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 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 Joel, Thank you for the insightful videos. However, I'm getting a compile error on objGui and objConn as "User-defined type not defined". This will be my very first step towards an automation between SAP through VBA scripting, if I manage to make it work. Keep up the good work!
@@angelnikolov9072 Hi, you can try to look for it using the browse button. I have a step by step video showing how to browse for it th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html You may not be able to locate it if you are using SAP GUI via citrix from what I understand.
@@Manishreddy09 Hi Manish reddy. I have not worked with Citrix or VDI before and I did not have an environment to test out if some of the alternative I found will work. Perhaps you can try running the VBS script right after recording to see if it works. If it does, then you can probably try building the script in Python or Power Automate.
Hi Joel, this is Wendy and i must say your videos are really helpful and i am able to do SAP automatically!! Just a quick question , when i tried to extract the data of one specific cell of SAP APO module to excel, it could not be completed with number i wanted. It shows "SAPGUI.GridViewCtrl.1" even i tried several times with different ways...the code i use is below: session.findById("wnd[0]/usr/subREQMTS:/SAPAPO/SAPLRRP_REQMTS:3000/cntlALV_GRID_REQMTS/shellcont/shell").currentCellColumn = "EXTRA" session.findById("wnd[0]/usr/subREQMTS:/SAPAPO/SAPLRRP_REQMTS:3000/cntlALV_GRID_REQMTS/shellcont/shell").PressEnter Cells(currentline, 6).Value = session.findById("wnd[0]/usr/subREQMTS:/SAPAPO/SAPLRRP_REQMTS:3000/cntlALV_GRID_REQMTS/shellcont/shell").Text Not sure whether you have experience at this problem, many thanks in advance. BR Wendy
Hi Wendy, sry for the late reply on this. I believe I have replied you via email. But just for everyone's information, you can capture the data in GridView object by using the following syntax: session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").GetCellValue(rownum, "column name here")
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.
Hi! This is an older video but I hope you see this. I use lot of scripts to automate extractions for users and usually, the next step after the extraction is to copy paste the data in the main workbook. When I put the copy paste code after the extraction code, it bugs because the macro seems to be going faster than the extraction opening and can't find the workook to copy from. I use 2 separate macros to solve that but is there any ways to have all the steps in the same macro? Thanks
Hi there. I had similar problems with you if the data export takes a long time. I'm able to solve this by exporting the table using List -> Export -> Local file -> Text with tabs instead. This process seems to be more consistent compare to exporting them in spreadsheet. Then, I'll pass the text file into a custom converts it into a table and saves it to Excel. Maybe you could try this approach and see if it works for you?
Hi! Thanks for you response! I will definitely give it a try! The size of my extraction are quite big indeed so that makes sens that Excel can't open the workook fast enough. Again, thank you the valuable informations you share! Its highly appreciated!
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
Thanks for the video. Still need some help though... I have an excel file with a bunch of numbers and I want to select a number and have this number pasted into the SAP transaction. What would be the correct code for this? It's not always the same cell so I can't put cell "A1" in the coding for example, it should just be the cell whichever one I select and copy with my cursor. Current code I have: Option Explicit Public SapGuiAuto, WScript, msgcol Public objGui As GuiApplication Public objConn As GuiConnection Public session As GuiSession Sub Stock() Set SapGuiAuto = GetObject("SAPGUI") Set objGui = SapGuiAuto.GetScriptingEngine Set objConn = objGui.Children(0) Set session = objConn.Children(0) session.FindById("wnd[0]").Maximize session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nmd04" session.FindById("wnd[0]").SendVKey 0 session.FindById("wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-MATNR").Text = "10236675" session.FindById("wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-WERKS").Text = "e704" session.FindById("wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-WERKS").SetFocus session.FindById("wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-WERKS").CaretPosition = 4 session.FindById("wnd[0]").SendVKey 0 End Sub
Hi Tom, if that's the case, then how would you know which cell value to use? If it's the one you select, that would be a little complicated, (what if you forgot to click on the cell before you start the script?). Either way, you can try using ActiveCell.value to extract the text in the current selected cell.
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!
So are you saying that we can record our steps in sap to get what we require and then save and copy the script in sap and create a button in excel and it will run it for us. Regardless of what we are searching and creating excel files for?
Hi Joel, Thanks for the video, really helpful! I'm also facing the issue that the exported workbook opens automatically after the export. I tried to make a code to close it again right away but I always get a reference error when trying to close it. Seems like to workbook is neither recognized as open but also not closed. Do you know a fix for this?
Hi J.S. I'm also facing the same issue and it's something that I'm not able to fix currently. I noticed that only the last exported workbook always opens automatically. So if I need to export a number of workbooks, I would just string them together so that I would only need to close Excel one last time after running the code instead of closing multiple workbooks.
@@JoelTing Thanks for the tipp Joel! I'll try to string the workbook i need to a dummy export which i won't need so i can use the actual export for consecutive steps in my process
@@j.s.6080 From my previous experience, you can interact with it right away after export. You won't need to export a dummy report for this. Just open the file directory with "Workbook.Open" method and you can interact with it. Just that by the end of your script, it may reopen your export again. Kinda annoying, but didn't interfere with my interaction with the exported workbook immediately after export.
Hi Joel, nice presentation! I was wondering if there is any way to easily copy the data from the exported file into another specific workbook? I would like to have multiple automated data exports from SAP into a KPI excel document. This also means copy pasting the data below already existing data. I was looking to creating a Excel Macro that would just copy the data and paste it below existing data, but i am worried that the source excel file might change "name" every time i export new data.
Hi Jeppe. Thanks! Yea, it's definitely possible. You can use vba to open the workbook exported, copy the data in it, then open the final destination workbook and paste it there. Usually that's how the flow works if you want to put the data in an existing workbook. When you say source excel, you mean the one from SAP right? What you can do is to let VBA handle the naming, then it can pass it on to the next step.
@@JoelTing Yeah exactly my thoughts! However, the data that I export automatically opens an Excel file with the name "EXPORT" from which I then try to copy the data into another document. However, the piece of code: "Set wsCopy = Workbooks("EXPORT.xlsx").Worksheets(1)" seems to bug as the "EXPORT" document doesn't open automatically when this code is written right after the SAP extraction code. When the SAP extraction code is run individually the "EXPORT" document opens just fine. The copy code also works fine by itself. I have tried with a wait command in between the two. Do you have any idea why this happens? Thanks for the response!
Hi Jeppe, what I noticed is that the workbook is actually not opened upon export. Rather, it is opened at the end of the process, which means that however long you put in the wait function within the process, it wouldnt work. Instead of "Set wsCopy = Workbooks("EXPORT.xlsx").Worksheets(1)", you can try working with the Workbooks.Open() method (ie Set wbCopy = Workbooks.Open("EXPORT.xlsx")). Then, you work with the worksheet in it (ie Set wsCopy = wbCopy.Worksheets(1)). This will work regardless of whether your workbook is open or not. See if this way works for you?
Hi Joel, what can i do if i dont have the reference SAP GUI SCRIPTING API activated and when i look for it in the browser doesnt appear? NIce explication.
You can check out this video to check if you can look for it in your folder. th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html&lc=Ugx_ZXLVMeYmkqr28Zl4AaABAg Usually, you can find the file (sapfewse.OCX) in C:\Program Files (x86)\SAP\FrontEnd\SAPgui
Hi Joel, Thanks for the Script. if i want to select the date as the selection criteria. For example, Instead of country selection, i want to select date in the excel to populate data. Your help will be appreciated. Thanks
Hi, apologies for the late reply. That should be doable. Just read the cell value containing your date, then extract the day, month and year and form the kind of string that your SAP GUI recognises.
Hey Joel! Thank you for the video; it is very helpful. I do have an issue however when attempting to run the code, I keep getting a "Run-time error '614' "The enumerator of the collection cannot find an element with the specified index"" Do you know what would cause this and how to fix it?
Hey Joshua, from my understanding, this error usually arise due to either 1 of the following reason: 1) your SAP is not logged on; or 2) Scripting have been disabled by your company's administrator. For the 1st reason, you just need to make sure that there's an active session for SAP and it is logged on before running your script. If it's due to the 2nd reason, you need to get your company's IT to enable it for you.
![Automate Data Extraction with SAP GUI Scripting & Excel Macro VBA [english]](/img/n.gif)
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! 😊
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.
This is super brilliant presentation and very usefull for simple SAP user! Mr you are awesom! If you also could show how to extract several different reports and to save them into one work book, on different sheets. ❤
I'll try to work out some content on this. The general idea is once you have all the paths of the different reports after the export, you can just open them with VBA, then transfer them over into your current workbook. Another way to do it is via Power Query where you can setup data transformation steps and load it into your current workbook. We can use VBA to dynamically change the source into the newly extracted files.
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!
I made it for the 1st time. Thank you. 😊
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, 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, Awesome presentation. The information provided in the video is fabulous. Thank you!
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!
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:
th-cam.com/video/7Rxh10Kt5v4/w-d-xo.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!
Hello Joel,
Thank you so much for this lesson. I was able to use it. You have my love and respect. God bless you!!.
Amazing Tutorial, simple explaining a complex matter.
Hope it was helpful!
Hi Joel, this is a great video. Thank you for sharing your valuable expertise.
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 Brother!!!
You are truly amazing! For distribute such rich knowledge in a simple and right way...
Congratulations!!!
Thanks for the kind word!
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.
@@JoelTing did you found it sir?
Excellent video. Very interesting option and I will be looking for ways to apply this to my day to day work. Thanks
Hey Joel, this is what I needed. Great share on this video. So, my question is, what if i want to have my collogues perform this and share the spreadsheet with them. With SAP logon, that would cause a Run-Time error "the Control could not be found by ID. how do we go about doing that?
@@thydinh20 hello! This error seems to show your colleagues page is not the same as yours. It is not navigating to the correct page. For your script, did you include "/n" at the front of the Tcode when running it? Sometimes, your screen could be on other Tcode. Running another Tcode without "/n" will result in the interface being at the wrong page, resulting in the script not being able to perform what it needs to do.
What a great Video
The explanations are so clear. Thank you very much!!
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.
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.
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 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.
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.
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 , 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.
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.
Hi Joel thanks for the vid ive been automating a lot of functions with this. one problem i have now is when i have multiple tabs of SAP open that have different SID. sometimes i run it and it executes the actions in a different SID not for S4HANA. is there a way to specify which SID it runs on?
Hello Jude. Yes, you can loop through all the system IDs available using objGUI.Children(i) to check the SID description before connecting to it. If you have multiple, and want to work with them individually by assigning each SID into individual objects.
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.
th-cam.com/video/G5qc2gcpyW4/w-d-xo.html
@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.
I'm extremely grateful for you. Thank you very much.
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
Hello Joel, Thank you so much , this video is very amazing and helpful, i am very grateful for your great work.
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.. 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.
It's exactly what I need. Very helpful. Thank you.
Thank you so much! I think I might be able to solve my problem now.
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.
@@JoelTing Thank you very much!
@@JoelTing One more question: is there a possibility that, before I run the script, to run anonther script to launch SAP GUI? Without SAP running, it gives me an error.
@@classiccarpov2311 Hi, yes it's possible. You can run it via sapshcut.exe. You can check out my previous video here for reference.
th-cam.com/video/C_7UD_l-zwA/w-d-xo.html
Although I am using Power Automate in the video, the same concept applies and you can call commands using VBA. That being said, you would need to store the password somewhere for the script to access, which could expose yourself to security risk. Unless the users know what they are doing and how to safely store their credentials in KeePass, I would not recommend to automate this portion of the process.
Great job as always Joel. Keep up the good work!
Thanks again!
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.
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.
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:
th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html
Hopefully it will work for you.
What an amazing video that was! Thank you so much!
Hi Joel,
I know I'm damn late but still want to ask you 2 questions:
1. I want to add multiple values for an input (plant --> 001, 002, 003). How can I customize it with coding? I tried For each for it only got the last value.
2. At the last step, I clicked "Generate" as I save the file for the first time. If running macro to refresh that file, VBA will tell me the file has existed. Do I need to run a side module to delete the current file before running it, or any solutions?
Hello!
1. Try and debug step by step and see what's wrong in the loop. You should be able to pinpoint which part of your code goes wrong. I usually use For next loop and that has been working well for me. Alternatively, if you have multiple values, you can make use of the clipboard as well. Customize the clipboard with the input values, then in SAP use the paste from clipboard button.
2. Deleting the current file is one way to do it. Check if the file is already exist, if it is then delete it. Another alternative you can think about is to use the current time as part of the file name. Then, you would be sure that whenever you run the script, the file name is always unique.
Exactly what I needed!!!
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.
HI Joel , Nice Video. Can you suggest how do we add a Data below the existing data. Example I have a Sap report in a file till 15th of the month. When I run the macro it should only paste data after 15th till date in the sheet.
Hi Abhishek, perhaps you can try the following flow process when building your sub procedure:
After you generate SAP report, open Workbook with existing data -> Identify max value of date column within existing data -> Filter date column of SAP report to be greater than max value of existing data date -> identify last row of existing data -> paste in data starting from row number (last row + 1)
Hope this helps!
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.
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 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)
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.
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 Joel! Great video! is it possible to also automate log in to SAP?
Hi there. You can check this link to see if this works:
simpleexcelvba.com/connect-to-sap-via-excel-vba/
Thanks for good video. Very clear & useful.
Hi Joel, great video and easy to follow. I had a VBA code running based on Excel 2016 and after updating to 365 I encountered that my code won´t run. It was compiled to open a userform to enter the User and Pass and then connect to SAP. Is there any chance you can teach us how to do that in Excel. (I am not able to get power automate unfortunately) Thank you!!
Hi Natalia, may I know what error did you ran into when running your code?
Awesome! well explained
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.
Very Very useful. Thanks a lot
HI Joel, I know this video is a year old, but the information is very useful. I tried SAP recording, but it does not save it. I cant progress... Any thoughts?
For SAP recording, what do you mean you can't save it? The script does not save after recording? Have you tried saving to a different directory (like Desktop or your Documents folder)?
Hi Joel, great video as always. Can you comment on a situation where I would paste a list of items into the parameters such as a list of cost centers or a list of WBS elements to generate monthly reports. How would I write the vba script for that ? A range perhaps? I am not a VBA coder. My extent with VBA is using the Macro recorder.
Hi Steve Johnson, to copy a list of items, you can copy the range into clipboard by by referencing the range with the following code in VBA (eg. {workbook}.{worksheet}.Range("A2:A99").Copy)
Subsequently, you would use the "Upload from clipboard" button in SAP (you can get the code with SAP Script recording) to paste all your values in. Hope this helps!
@@JoelTing Thank you for getting back to me. So I do not need to set that range as a variable ?
@@stevejohnson5033 Not really. When the copy instruction is executed, it should save it into the clipboard and it will be used to populate the multiple selection box in SAP.
@@JoelTing Thanks for getting back to me. I now have a issue locating the SAP Script API. I can not locate it. Can I sent you a screen shot of what I am seeing?
@@stevejohnson5033 SAP Script API ocx file can be found in usually in the SAP installation folder (C:\Program Files (x86)\SAP\frontend\sapgui\sapfewse.ocx).
Click on the browse button in the reference window. At the bottom right of the window, make sure to change the file type to ActiveX control (*.ocx).
If you are still not able to find, feel free to send me the screenshot and I'll get back to you.
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.
Hello Joel, Great video and work! can you make another example but with a date selection interval for a report? (I can't populate a specific month report cause in the macro recording it records a specific interval, how can I bypass that or work arround ? ) thanks!
Hey Christian, do you need the intervals to run your report? or it is optional but already pre populated?
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.
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!
Amazing
That is what i was looking for ¬¬¬¬ Thanks!!!!
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! 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.
th-cam.com/video/lFLIwcRi0_U/w-d-xo.html
@@JoelTing Excellent! Thank you so much!
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.
amazing, it´s incredible
If I want to generate multiple SAP GL data on the same time so do know about how to generate in vba
Hello! If you're looking to run multiple sessions simultaneously, with each session executing a specific GL code, that's definitely possible. To achieve this, you can modify the connection children index when connecting to SAP. For instance, you can use objConn.Children(0) to connect to the first session, objConn.Children(1) to connect to the second session, and so on. By doing so, you can instruct your script to run distinct GL code on separate sessions, enabling parallel processing.
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.
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.
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 ?
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.
th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html
@@JoelTing That worked like a charm! Thanks a bunch for your help. Take care!
Hi Joel,
Thank you for the insightful videos. However, I'm getting a compile error on objGui and objConn as "User-defined type not defined". This will be my very first step towards an automation between SAP through VBA scripting, if I manage to make it work. Keep up the good work!
Hi Ole. Have you enabled the SAP GUI Scripting API in References?
@@JoelTing Hi, I cannot find the SAP GUI Scripting API in References.
@@angelnikolov9072 Hi, you can try to look for it using the browse button. I have a step by step video showing how to browse for it th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html
You may not be able to locate it if you are using SAP GUI via citrix from what I understand.
@@JoelTing what's the workaround then... most of the client networks are accessed via citrix or vdi
@@Manishreddy09 Hi Manish reddy. I have not worked with Citrix or VDI before and I did not have an environment to test out if some of the alternative I found will work. Perhaps you can try running the VBS script right after recording to see if it works. If it does, then you can probably try building the script in Python or Power Automate.
Hi Joel, this is Wendy and i must say your videos are really helpful and i am able to do SAP automatically!!
Just a quick question , when i tried to extract the data of one specific cell of SAP APO module to excel, it could not be completed with number i wanted. It shows "SAPGUI.GridViewCtrl.1" even i tried several times with different ways...the code i use is below: session.findById("wnd[0]/usr/subREQMTS:/SAPAPO/SAPLRRP_REQMTS:3000/cntlALV_GRID_REQMTS/shellcont/shell").currentCellColumn = "EXTRA"
session.findById("wnd[0]/usr/subREQMTS:/SAPAPO/SAPLRRP_REQMTS:3000/cntlALV_GRID_REQMTS/shellcont/shell").PressEnter
Cells(currentline, 6).Value = session.findById("wnd[0]/usr/subREQMTS:/SAPAPO/SAPLRRP_REQMTS:3000/cntlALV_GRID_REQMTS/shellcont/shell").Text
Not sure whether you have experience at this problem, many thanks in advance.
BR
Wendy
Hi Wendy, sry for the late reply on this. I believe I have replied you via email. But just for everyone's information, you can capture the data in GridView object by using the following syntax:
session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").GetCellValue(rownum, "column name here")
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 👍
Hi! This is an older video but I hope you see this. I use lot of scripts to automate extractions for users and usually, the next step after the extraction is to copy paste the data in the main workbook. When I put the copy paste code after the extraction code, it bugs because the macro seems to be going faster than the extraction opening and can't find the workook to copy from. I use 2 separate macros to solve that but is there any ways to have all the steps in the same macro? Thanks
Hi there. I had similar problems with you if the data export takes a long time. I'm able to solve this by exporting the table using List -> Export -> Local file -> Text with tabs instead. This process seems to be more consistent compare to exporting them in spreadsheet. Then, I'll pass the text file into a custom converts it into a table and saves it to Excel. Maybe you could try this approach and see if it works for you?
Hi! Thanks for you response! I will definitely give it a try! The size of my extraction are quite big indeed so that makes sens that Excel can't open the workook fast enough. Again, thank you the valuable informations you share! Its highly appreciated!
Hello Joel, what SAP theme are we using here? Thanks sir
Hey. What do you mean by SAP theme? Do you mean the color scheme?
Yes sir, figured it out already. Cheers
Fantastic video! ❤
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
Interesting ................thanks for sharing
Hi, i am unable to find SAP GUI scripting API in reference, its also not available in the path you have shown. Any suggestions please...
Hello. Are you using SAP via Citrix? Can you check via File Explorer if you have the folder "C:\Program Files (x86)\SAP\FrontEnd\SAPgui"?
Thanks for the video. Still need some help though... I have an excel file with a bunch of numbers and I want to select a number and have this number pasted into the SAP transaction. What would be the correct code for this? It's not always the same cell so I can't put cell "A1" in the coding for example, it should just be the cell whichever one I select and copy with my cursor. Current code I have:
Option Explicit
Public SapGuiAuto, WScript, msgcol
Public objGui As GuiApplication
Public objConn As GuiConnection
Public session As GuiSession
Sub Stock()
Set SapGuiAuto = GetObject("SAPGUI")
Set objGui = SapGuiAuto.GetScriptingEngine
Set objConn = objGui.Children(0)
Set session = objConn.Children(0)
session.FindById("wnd[0]").Maximize
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nmd04"
session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-MATNR").Text = "10236675"
session.FindById("wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-WERKS").Text = "e704"
session.FindById("wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-WERKS").SetFocus
session.FindById("wnd[0]/usr/tabsTAB300/tabpF01/ssubINCLUDE300:SAPMM61R:0301/ctxtRM61R-WERKS").CaretPosition = 4
session.FindById("wnd[0]").SendVKey 0
End Sub
Hi Tom, if that's the case, then how would you know which cell value to use? If it's the one you select, that would be a little complicated, (what if you forgot to click on the cell before you start the script?). Either way, you can try using ActiveCell.value to extract the text in the current selected cell.
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!
So are you saying that we can record our steps in sap to get what we require and then save and copy the script in sap and create a button in excel and it will run it for us. Regardless of what we are searching and creating excel files for?
Technically yes. SAP GUI scripts are just scripts imitating your actions on SAP GUI.
Hi if "script recording and playback"in SAP is not enabled, I can still use the excel to control SAP or no way??
Hi, if the script recording and playback is disabled by admin, there's no way to control SAP with scripts.
Thank you, thanks a TON.
Hi Joel,
Thanks for the video, really helpful!
I'm also facing the issue that the exported workbook opens automatically after the export. I tried to make a code to close it again right away but I always get a reference error when trying to close it. Seems like to workbook is neither recognized as open but also not closed. Do you know a fix for this?
Hi J.S. I'm also facing the same issue and it's something that I'm not able to fix currently. I noticed that only the last exported workbook always opens automatically. So if I need to export a number of workbooks, I would just string them together so that I would only need to close Excel one last time after running the code instead of closing multiple workbooks.
@@JoelTing Thanks for the tipp Joel! I'll try to string the workbook i need to a dummy export which i won't need so i can use the actual export for consecutive steps in my process
@@j.s.6080 From my previous experience, you can interact with it right away after export. You won't need to export a dummy report for this. Just open the file directory with "Workbook.Open" method and you can interact with it.
Just that by the end of your script, it may reopen your export again. Kinda annoying, but didn't interfere with my interaction with the exported workbook immediately after export.
Hi together, i'm facing the same problem at the moment.. is there a new or better solution for this now?
Thank you for your great video @Joel Ting
Hi Joel, nice presentation!
I was wondering if there is any way to easily copy the data from the exported file into another specific workbook? I would like to have multiple automated data exports from SAP into a KPI excel document. This also means copy pasting the data below already existing data. I was looking to creating a Excel Macro that would just copy the data and paste it below existing data, but i am worried that the source excel file might change "name" every time i export new data.
Hi Jeppe. Thanks! Yea, it's definitely possible. You can use vba to open the workbook exported, copy the data in it, then open the final destination workbook and paste it there. Usually that's how the flow works if you want to put the data in an existing workbook. When you say source excel, you mean the one from SAP right? What you can do is to let VBA handle the naming, then it can pass it on to the next step.
@@JoelTing Yeah exactly my thoughts! However, the data that I export automatically opens an Excel file with the name "EXPORT" from which I then try to copy the data into another document. However, the piece of code: "Set wsCopy = Workbooks("EXPORT.xlsx").Worksheets(1)" seems to bug as the "EXPORT" document doesn't open automatically when this code is written right after the SAP extraction code. When the SAP extraction code is run individually the "EXPORT" document opens just fine. The copy code also works fine by itself. I have tried with a wait command in between the two. Do you have any idea why this happens?
Thanks for the response!
Hi Jeppe, what I noticed is that the workbook is actually not opened upon export. Rather, it is opened at the end of the process, which means that however long you put in the wait function within the process, it wouldnt work.
Instead of "Set wsCopy = Workbooks("EXPORT.xlsx").Worksheets(1)", you can try working with the Workbooks.Open() method (ie Set wbCopy = Workbooks.Open("EXPORT.xlsx")). Then, you work with the worksheet in it (ie Set wsCopy = wbCopy.Worksheets(1)). This will work regardless of whether your workbook is open or not. See if this way works for you?
You are truly Amazing
Hi Joel, what can i do if i dont have the reference SAP GUI SCRIPTING API activated and when i look for it in the browser doesnt appear?
NIce explication.
You can check out this video to check if you can look for it in your folder.
th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html&lc=Ugx_ZXLVMeYmkqr28Zl4AaABAg
Usually, you can find the file (sapfewse.OCX) in C:\Program Files (x86)\SAP\FrontEnd\SAPgui
Hi Joel, Thanks for the Script. if i want to select the date as the selection criteria. For example, Instead of country selection, i want to select date in the excel to populate data. Your help will be appreciated. Thanks
Hi, apologies for the late reply. That should be doable. Just read the cell value containing your date, then extract the day, month and year and form the kind of string that your SAP GUI recognises.
@@JoelTing it worked! Thanks
Hey Joel!
Thank you for the video; it is very helpful. I do have an issue however when attempting to run the code, I keep getting a "Run-time error '614' "The enumerator of the collection cannot find an element with the specified index""
Do you know what would cause this and how to fix it?
This happens on the 4th line of code "Set session = objconn.Children(0)"
Hey Joshua, from my understanding, this error usually arise due to either 1 of the following reason:
1) your SAP is not logged on; or
2) Scripting have been disabled by your company's administrator.
For the 1st reason, you just need to make sure that there's an active session for SAP and it is logged on before running your script. If it's due to the 2nd reason, you need to get your company's IT to enable it for you.
I'm having this issue also. It's due to my company policy, unfortunately.
@@JoelTing If it's disabled by a company/administrator one would likely also see the "Script Recording And Playback" grayed out when trying to record.
Hi, I am not able to record script in SAP GUI for vendor clearing using T-code F-44. Could you please help me
Hello. What is the difficulty that you are facing?