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.
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!
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.
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.
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.
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.
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.
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 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.
I was so happy when I found this video since I deal with a lot of data extraction in SAP, but later felt sad because the "Script Recording and Playback" features of our SAP were disabled :(
Hello. I recommend reaching out to your IT department. They often have specific policies regarding automation. When it comes to automating data inputs, they tend to be quite cautious. However, if you're only extracting data, they're usually more open to providing scripting access. Some companies create a separate SAP ID specifically for automation purposes, restricting it transactions that provides read-only access to users.
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 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.
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.
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 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 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.
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.
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
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.
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 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, 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! 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, 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 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, 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, i tried to record a script for T-code YPSFDSD order data flow down under quality instructions but unable to move the workbooks from the field while script started recording.... could you please how to move and record
Hello! The T-code you mentioned there is not a standard T-code. I do not really understand what you meant by moving the workbook. The script recorder will only record interaction in SAP GUI. Once you are out of SAP, like using file explorer to save file, the recorder will not work. Depending on what you need to do, there's some workaround for it.
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 Joel, What to do if I don´t have the sapfewse.ocx on my desk and have a company laptop were I can´t install anything.. Are there a work around - Lasse (Denmark)
Hi Lasse, unfortunately, if you are not able to find the ocx file locally, most likely you are connecting to SAP GUI remotely, maybe via Citrix. In that case, I'm not aware of any workaround to execute SAP scripts via VBA. Not able to try it from my end, but you may try running scripts using Python or Power Automate if it's already installed in your company laptop.
@@JoelTing I found the file (windows search in all folders!) - In guess that I need new glasses in 2023.. but thanks for the support so far and looking forward to use the function for download of data ..
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.. 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 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.
@@JoelTing Actually, I have two scenarios 1. I want to take screenshot from SAP, t-code FS10N. 2. Yes, I am looking from Browser automation in my work process
@@nilpankhcreations2877 Hi there, you can try to check this video out. He has some explaination on how screenshot can be taken. th-cam.com/video/W_J-9t_mWDU/w-d-xo.html&ab_channel=CsongorVarga
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?
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 Ronnie Gelacio, usually if SAP GUI is installed locally in your computer, you should be able to find it. I have a detailed step by step explanation on how to look for it in this video. Make sure to change the file type to ActiveX Control when you are looking for sapfewse.ocx file in SAP folder. th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html
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.
I'm trying to do this from Word, but SAP GUI isn't listed as an available reference. Is there any way it can be added? I tried browsing to the folder seen in the video, but I don't have an SAP folder in (x86).
@@JoelTing My logon is accessed remotely via Citrix portal, a very annoying practice my company started during the COVID lockdowns. I can't find the actual installation folder.
@@generalspidrax9699 Unfortunately, from what I understand, if you are connecting via Citrix, you will not be able to execute script on SAP GUI. I'm not able to test out from my side whether there's a workaround for Citrix portal as I'm not using it.
Hi Joel, i had an error while running the VB with notification "the control could not be found by id" what errors that we faced about?? what step that we missed?? and how to fix it?? because we did also add access of SAP GUI Script API before we run script Thanks
Hi there. This error usually happens when the script is executed on the SAP screen that it is not expected to be in. You may want to try and go through the script line by line, see where the code breaks when you are executing the script and compare to the steps and screen when you first record the script.
Hi Joel, i'm automating an excel sheet that gets data from 3 of this tyoe of files that are exported from SAP. But I can't get the macro to closet this files once the export is done (out of Range error) is there somethimg special that needs to be done?
Hi there, credits to Patrick Migues, he suggested to try the following: Dim export1 As Object Dim export2 As Object Set export1 = GetObject(“file location\Export 1.file extension”).Application Set export2 = GetObject(“file location\Export 2.file extension”).Application export1.Workbooks(1).Close export2.Workbooks(1).Close export2.Quit Error Situations: This will close either all of the Excel files opened after the Macro is run, explicitly listed or not (running correctly); or it will close all of the excel files opened prior to running the macro, to include the file running the Macro, if one of the explicitly listed files is not open or saved to the listed location. Because of the above, when this code fails, it will not throw a debug error. I gave up working on that part as I felt like it's not a good use of my time to keep trying and debugging this one simple action of closing the workbook, since I automated the most time consuming part.
@@JoelTing Thanks for the reply, Ill try the solution tomorrow.... for now I went into kernel to look at all open instances and killed all but the active one (saved the instance in a variable first) but its not a very nice way to close them
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?
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.
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 , 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!
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. 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.
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!
Hey Joel! I'm getting an issue when uploading this file on SharePoint! When I give the address to replace the file on my local drive, it works fine. But when I'm giving the address on SharePoint or OneDrive... It keeps on showing the same file! That file on SharePoint is modified easily when I do it manually!
Hey tallalaazmi1287, are you pointing the address using https and it's not a local drive? I'm usually just work with local file. For sharepoint and onedrive file, I will have the file synced so whenever it is being replaced, it can be uploaded and replaced in the cloud as well.
Hi there. Is ZM45 a tcode? If it's a tcode, it seems to be a T-code customized for your organization. The steps of recording and developing the automation should be quite similar as what you see in the video, unless your organization disabled scripting for SAP.
Hi Joel, I am trying to make the date and the time, and the folder path dynamic for my macro, but when I dim those variables in my code and set the value in the active worksheet, there is always the error "The control cannot be found by ID" and when I open the Watch window, the value for these variables is always . Do you have any idea what the problem could be?
Hi, apologies for the late reply. Is your SAP open when you are running your script? It seems like it is not able to find the "buttons" or "areas" that you are requesting it to interact with in SAP GUI.
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?
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
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.
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, do you have a video showing how to upload data into SAP using VBA? I have this repetitive task of loading data and don’t want to use LSMW. Please assist
Hi there, apologies for the late reply. Unfortunately, currently, I do not have a video showing that. The concept should be similar with what you see in the video. Record the script of the process of you loading your data, then use that script as a base in VBA and modify base on your needs.
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: th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html Hopefully it will work for you.
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 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 there, just convert the date into the string format that your SAP will need and store it in a variable, then replace the hard coded date in your recorded script with the variable.
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.
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.
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 joel hope you can help me as i tried the code in ectracting report in sap using the macro. My process is to extracr file then save, then back again to change the parameter but there is an instance that there is no data to extract on that specific parameter. What code should i add so the extraction will continue to the next parameter if there is no data on the previous parameter i chose. Appreciate your response. Thanks in advance
Hi Noemi. I believe when there's no data, SAP GUI will show that in the status bar on the lower left corner right? You can try to use session.FindById("wnd[0]/sbar").Text to capture the text in the status bar and wrap it around a condition to validate the output.
Hi Joel, thanks a lot for simple and useful lesson. I am facing a Run time error 619: the control could not be found by I'd. Can you please help here. Thanks a lott
Hi Monika, if you troubleshoot it step by step, are you able to identify where does it go wrong? Usually this happen when the step it is trying to execute is not the same as the time when you record the macro.
I've a dashboard that is based on data extracted from SAP. I want that dashboard to be dynamic, so that whenever anyone opens it, it will fetch the latest data from SAP Server and rebuild the dashboard. Is it possible without using any additional T-Code approvals & additional softwares?
Hi there. Unfortunately, that is not really possible. SAP GUI Scripting emulates the user's interaction with the user interface. It is not a direct connection to SAP data. So the user needs to have the necessary Tcode access to extract all the information. The closest solution that I am aware of is using SAP BW and Analysis add in. Refresh of data would be just a click away, but once again, the user needs to have access to the query that he is trying to refresh from. And to have SAP BW query built for your solution, you probably need to engage IT as they need to build the query from ground up using some SAP software which is usually not available for us end users.
@@JoelTing Thanks for replying, Joel Is it possible to give a refresh button in my excel sheet, which can fetch data from SAP and update the table in my dashboard?
Hi there. When there's an error with this line, it's usually due to SAP GUI script being disabled by the administrator. In some companies, users need to specifically request for a user with scripting access.
Getting a run time error "614" "the enumerator of the collection cannot find element with the specified index. While using the debugger it comes while trying to execute: Set objConn = objGui.Children(0) Set session = objConn.Children(0) any help would be appreciated
Hey Justin, 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.
Hi there, you can use the following as a reference: Private Declare PtrSafe Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal _ bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long) Private Const VK_SNAPSHOT = &H2C Private Const KEYEVENTF_KEYUP = 2 Sub PrintScreen() keybd_event VK_SNAPSHOT, 1, 0, 0 'Key Down keybd_event VK_SNAPSHOT, 1, KEYEVENTF_KEYUP, 0 'Key Up Pause (0.1) Dim wordApp As Object Set wordApp = CreateObject("Word.Application") wordApp.Visible = True Dim wordDoc As Object Set wordDoc = wordApp.Documents.Add wordDoc.Range.Paste wordApp.ActiveDocument.Range.Paste End Sub Function Pause(sec As Single) Dim t As Single: t = Timer Do: DoEvents: Loop Until Timer > t + sec End Function After pasting, you can just save the word document your own. Hope this helps.
Hi Kunal, that is possible, you first need to identify the field ID in SAP that you are trying to copy. You can do that by clicking on it then hit enter and trace back the field in the script. Then, just set the value of the specified Excel cell to the field value.
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. 😊
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, 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.
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!
it's my first time creating VBA and your tutorial works wonder, huge thanks!
Glad it helped!
Hi Joel, Awesome presentation. The information provided in the video is fabulous. Thank you!
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!
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!
Thanks
Thank you!
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,
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.
Hello Joel,
Thank you so much for this lesson. I was able to use it. You have my love and respect. God bless you!!.
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.
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 Joel, this is a great video. Thank you for sharing your valuable expertise.
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?
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 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.
Excellent video. Very interesting option and I will be looking for ways to apply this to my day to day work. Thanks
I was so happy when I found this video since I deal with a lot of data extraction in SAP, but later felt sad because the "Script Recording and Playback" features of our SAP were disabled :(
Hello. I recommend reaching out to your IT department. They often have specific policies regarding automation. When it comes to automating data inputs, they tend to be quite cautious. However, if you're only extracting data, they're usually more open to providing scripting access. Some companies create a separate SAP ID specifically for automation purposes, restricting it transactions that provides read-only access to users.
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.
@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.
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.
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.
Amazing Tutorial, simple explaining a complex matter.
Hope it was helpful!
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.
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.
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.
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, 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"?
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?
What a great Video
The explanations are so clear. Thank you very much!!
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, 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?
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 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, 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 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! 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, 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 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, 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 am facing the problem that where my sap not able to open the xlsx file, any idea for that?
Hi Jason, may I know what you mean by SAP not able to open xlsx file? It cannot export the spreadsheet?
Hi, i tried to record a script for T-code YPSFDSD order data flow down under quality instructions but unable to move the workbooks from the field while script started recording.... could you please how to move and record
Hello! The T-code you mentioned there is not a standard T-code. I do not really understand what you meant by moving the workbook. The script recorder will only record interaction in SAP GUI. Once you are out of SAP, like using file explorer to save file, the recorder will not work. Depending on what you need to do, there's some workaround for it.
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.
Hello Joel, Thank you so much , this video is very amazing and helpful, i am very grateful for your great work.
Hi Joel, What to do if I don´t have the sapfewse.ocx on my desk and have a company laptop were I can´t install anything.. Are there a work around
- Lasse (Denmark)
Hi Lasse, unfortunately, if you are not able to find the ocx file locally, most likely you are connecting to SAP GUI remotely, maybe via Citrix. In that case, I'm not aware of any workaround to execute SAP scripts via VBA. Not able to try it from my end, but you may try running scripts using Python or Power Automate if it's already installed in your company laptop.
@@JoelTing I found the file (windows search in all folders!) - In guess that I need new glasses in 2023.. but thanks for the support so far and looking forward to use the function for download of data ..
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!
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.
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.
@JoelTing - Hi Joel,
Could you please help, by what coding I can capture a particular screenshot from a website. where there is data.
Hi there, are you looking for browser automation?
@@JoelTing Actually, I have two scenarios 1. I want to take screenshot from SAP, t-code FS10N.
2. Yes, I am looking from Browser automation in my work process
@JoelTing - any idea how put the screenshot from SAP to excel report
@@nilpankhcreations2877 Hi there, you can try to check this video out. He has some explaination on how screenshot can be taken.
th-cam.com/video/W_J-9t_mWDU/w-d-xo.html&ab_channel=CsongorVarga
Hi there, not sure if you still need it. I just released a video for taking SAP Screenshot with VBA.
th-cam.com/video/lFLIwcRi0_U/w-d-xo.html
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?
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 Joel,
I cannot see the SAP GUI Scripting API on the reference neither browsing. is there alternative reference we can use?
Hi Ronnie Gelacio, usually if SAP GUI is installed locally in your computer, you should be able to find it. I have a detailed step by step explanation on how to look for it in this video. Make sure to change the file type to ActiveX Control when you are looking for sapfewse.ocx file in SAP folder.
th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html
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.
I'm trying to do this from Word, but SAP GUI isn't listed as an available reference. Is there any way it can be added? I tried browsing to the folder seen in the video, but I don't have an SAP folder in (x86).
Hi there. Do you know where is your SAP logon installed? It is usually in the same folder.
@@JoelTing My logon is accessed remotely via Citrix portal, a very annoying practice my company started during the COVID lockdowns. I can't find the actual installation folder.
@@generalspidrax9699 Unfortunately, from what I understand, if you are connecting via Citrix, you will not be able to execute script on SAP GUI. I'm not able to test out from my side whether there's a workaround for Citrix portal as I'm not using it.
@@JoelTing That's a shame, thanks for taking the time to respond.
Great job as always Joel. Keep up the good work!
Thanks again!
Hi Joel,
i had an error while running the VB with notification "the control could not be found by id"
what errors that we faced about?? what step that we missed?? and how to fix it??
because we did also add access of SAP GUI Script API before we run script
Thanks
Hi there. This error usually happens when the script is executed on the SAP screen that it is not expected to be in. You may want to try and go through the script line by line, see where the code breaks when you are executing the script and compare to the steps and screen when you first record the script.
hi@@JoelTing thanks its worked :)
Hi Joel, i'm automating an excel sheet that gets data from 3 of this tyoe of files that are exported from SAP. But I can't get the macro to closet this files once the export is done (out of Range error) is there somethimg special that needs to be done?
Hi there, credits to Patrick Migues, he suggested to try the following:
Dim export1 As Object
Dim export2 As Object
Set export1 = GetObject(“file location\Export 1.file extension”).Application
Set export2 = GetObject(“file location\Export 2.file extension”).Application
export1.Workbooks(1).Close
export2.Workbooks(1).Close
export2.Quit
Error Situations: This will close either all of the Excel files opened after the Macro is run, explicitly listed or not (running correctly); or it will close all of the excel files opened prior to running the macro, to include the file running the Macro, if one of the explicitly listed files is not open or saved to the listed location.
Because of the above, when this code fails, it will not throw a debug error.
I gave up working on that part as I felt like it's not a good use of my time to keep trying and debugging this one simple action of closing the workbook, since I automated the most time consuming part.
@@JoelTing Thanks for the reply, Ill try the solution tomorrow.... for now I went into kernel to look at all open instances and killed all but the active one (saved the instance in a variable first) but its not a very nice way to close them
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.
th-cam.com/video/OG54SWpPfDs/w-d-xo.html
Hi joel i tried same to change snc name in su01 but having difficultity to fetch data from excel 😭
Hi there. You mean to login into SAP via SSO? Or you are not able to establish a connection for SAP script to run?
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.
Hi. Is it possible to connect to sap and run recorded macros with office scripts?
Hi there, sorry for the late reply. My understanding is that office script is not able to work directly with SAP.
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.
It's exactly what I need. Very helpful. Thank you.
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 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
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.
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 Joel Under Browse Option I am not findin sap gui scripting api What should I Do
Hi there, are you using SAP GUI remotely like accessing it with Citrix?
Hey Joel!
I'm getting an issue when uploading this file on SharePoint!
When I give the address to replace the file on my local drive, it works fine.
But when I'm giving the address on SharePoint or OneDrive... It keeps on showing the same file! That file on SharePoint is modified easily when I do it manually!
Hey tallalaazmi1287, are you pointing the address using https and it's not a local drive? I'm usually just work with local file. For sharepoint and onedrive file, I will have the file synced so whenever it is being replaced, it can be uploaded and replaced in the cloud as well.
Hi Joel, I want to download the automated report from ZM45, can you help me on that ?
Hi there. Is ZM45 a tcode? If it's a tcode, it seems to be a T-code customized for your organization. The steps of recording and developing the automation should be quite similar as what you see in the video, unless your organization disabled scripting for SAP.
Hi Joel, I am trying to make the date and the time, and the folder path dynamic for my macro, but when I dim those variables in my code and set the value in the active worksheet, there is always the error "The control cannot be found by ID" and when I open the Watch window, the value for these variables is always . Do you have any idea what the problem could be?
Hi, apologies for the late reply. Is your SAP open when you are running your script? It seems like it is not able to find the "buttons" or "areas" that you are requesting it to interact with in SAP GUI.
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?
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
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
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.
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.
Mr. Joel, how do you get SAP to automatically download the ME5A tcode using a recording script so that it becomes data in excel format.
Hi Denny, sry I'm not very familiar with the ME5A tcode. However, if it can be done with clicks within SAP GUI, most likely it would be doable.
Hi Joel, do you have a video showing how to upload data into SAP using VBA? I have this repetitive task of loading data and don’t want to use LSMW. Please assist
Hi there, apologies for the late reply. Unfortunately, currently, I do not have a video showing that. The concept should be similar with what you see in the video. Record the script of the process of you loading your data, then use that script as a base in VBA and modify base on your needs.
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:
th-cam.com/video/7Rxh10Kt5v4/w-d-xo.html
Hopefully it will work for you.
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.
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.
How to set date from - to in excel and what's the code i have to add ?
Hi there, just convert the date into the string format that your SAP will need and store it in a variable, then replace the hard coded date in your recorded script with the variable.
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! 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/
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.
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.
Hi joel hope you can help me as i tried the code in ectracting report in sap using the macro. My process is to extracr file then save, then back again to change the parameter but there is an instance that there is no data to extract on that specific parameter. What code should i add so the extraction will continue to the next parameter if there is no data on the previous parameter i chose. Appreciate your response. Thanks in advance
Hi Noemi. I believe when there's no data, SAP GUI will show that in the status bar on the lower left corner right? You can try to use session.FindById("wnd[0]/sbar").Text to capture the text in the status bar and wrap it around a condition to validate the output.
Hi Joel, thanks a lot for simple and useful lesson. I am facing a Run time error 619: the control could not be found by I'd. Can you please help here. Thanks a lott
Hi Monika, if you troubleshoot it step by step, are you able to identify where does it go wrong? Usually this happen when the step it is trying to execute is not the same as the time when you record the macro.
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
I've a dashboard that is based on data extracted from SAP.
I want that dashboard to be dynamic, so that whenever anyone opens it, it will fetch the latest data from SAP Server and rebuild the dashboard.
Is it possible without using any additional T-Code approvals & additional softwares?
Hi there. Unfortunately, that is not really possible. SAP GUI Scripting emulates the user's interaction with the user interface. It is not a direct connection to SAP data. So the user needs to have the necessary Tcode access to extract all the information. The closest solution that I am aware of is using SAP BW and Analysis add in. Refresh of data would be just a click away, but once again, the user needs to have access to the query that he is trying to refresh from. And to have SAP BW query built for your solution, you probably need to engage IT as they need to build the query from ground up using some SAP software which is usually not available for us end users.
@@JoelTing Thanks for replying, Joel
Is it possible to give a refresh button in my excel sheet, which can fetch data from SAP and update the table in my dashboard?
Hi joel i am facing run time error 404. Line 4 set session = objConn.children(0) pls help
Hi there. When there's an error with this line, it's usually due to SAP GUI script being disabled by the administrator. In some companies, users need to specifically request for a user with scripting access.
Getting a run time error "614" "the enumerator of the collection cannot find element with the specified index. While using the debugger it comes while trying to execute:
Set objConn = objGui.Children(0)
Set session = objConn.Children(0)
any help would be appreciated
Hey Justin, 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.
Hi can you wrght a code for taking screen shot and save it to a word document
Hi there, you can use the following as a reference:
Private Declare PtrSafe Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal _
bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Const VK_SNAPSHOT = &H2C
Private Const KEYEVENTF_KEYUP = 2
Sub PrintScreen()
keybd_event VK_SNAPSHOT, 1, 0, 0 'Key Down
keybd_event VK_SNAPSHOT, 1, KEYEVENTF_KEYUP, 0 'Key Up
Pause (0.1)
Dim wordApp As Object
Set wordApp = CreateObject("Word.Application")
wordApp.Visible = True
Dim wordDoc As Object
Set wordDoc = wordApp.Documents.Add
wordDoc.Range.Paste
wordApp.ActiveDocument.Range.Paste
End Sub
Function Pause(sec As Single)
Dim t As Single: t = Timer
Do: DoEvents: Loop Until Timer > t + sec
End Function
After pasting, you can just save the word document your own. Hope this helps.
Hi Joel, I want to copy paste a particular field value and paste it to excel cell. I have to do this multiple times can you tell how this is done.
Hi Kunal, that is possible, you first need to identify the field ID in SAP that you are trying to copy. You can do that by clicking on it then hit enter and trace back the field in the script.
Then, just set the value of the specified Excel cell to the field value.