Automate SAP Data Extraction with Excel VBA & SAP GUI Scripting - Minimal Coding Required

[@JoelTing]

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/

[@stevejohnson5033]

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.

[@JoelTing]

Awesome! Glad to hear that you get to make it work 😀

[@Carla369]

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

[@JoelTing]

Thanks for your kind words! 😊

[@arboflix9523]

I made it for the 1st time. Thank you. 😊

[@pauline8903]

it's my first time creating VBA and your tutorial works wonder, huge thanks!

[@JoelTing]

Glad it helped!

[@dcpowered]

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?

[@JoelTing]

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.

[@carlallison6775]

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.

[@JoelTing]

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!

[@Rajuuutube]

Hi Joel, Awesome presentation. The information provided in the video is fabulous. Thank you!

[@Jst7926]

I gave this video a like, but I wish i could give it a LOVE!!! This is amazing, THANK YOU!

[@JoelTing]

Thanks for the kind word Jessenia!

[@yogeshloganathan8221]

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.

[@JoelTing]

Glad it was helpful!

[@mahh1956]

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 .

[@JoelTing]

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! :)

[@iLona_IL]

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

[@JoelTing]

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.

[@sdfsdfsdfsdf8556]

Amazing Tutorial, simple explaining a complex matter.

[@JoelTing]

Hope it was helpful!

[@leandrocarvalho3462]

Hi Brother!!! You are truly amazing! For distribute such rich knowledge in a simple and right way... Congratulations!!!

[@JoelTing]

Thanks for the kind word!

[@satheshs5152]

Hello Joel, Thank you so much for this lesson. I was able to use it. You have my love and respect. God bless you!!.

[@jameslimpin2537]

Hi Joel, this is a great video. Thank you for sharing your valuable expertise.

[@user-qp1un7qo2d]

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!

[@user-qp1un7qo2d]

I do have variations of the references being "SAPGUI ApiHooker", "SAPGUI LSAP 1.0 Type Library", etc. Are these the same Joel?

[@JoelTing]

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: kzbin.info/www/bejne/bYPbmWRmgNlorJY Hopefully it will work for you.

[@user-qp1un7qo2d]

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

[@mood902]

Great job as always Joel. Keep up the good work!

[@JoelTing]

Thanks again!

[@fdkt1982]

What a great Video The explanations are so clear. Thank you very much!!

[@weixu5377]

Hi Joel, when i run the VBA script. it throw run time error (Method Item of object "ISapCollectionTarget" failed), any suggestion? Thanks.

[@JoelTing]

Hi there, can you share which line of code is giving you the error?

[@weixu5377]

@@JoelTing after i click 'debug', it highlight this line: Set session = objConn.Children(0)

[@MrGautam92]

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.

[@JoelTing]

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.

[@luisramirez2123]

Excellent video. Very interesting option and I will be looking for ways to apply this to my day to day work. Thanks

[@thanhphamduy]

It's exactly what I need. Very helpful. Thank you.

[@MK0.0]

I'm extremely grateful for you. Thank you very much.

[@kamila6701]

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.

[@JoelTing]

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.

[@Riyaz313]

@@JoelTing did you found it sir?

[@judebotecial8530]

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?

[@JoelTing]

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.

[@gastonrodriguezbouzas7794]

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!

[@chrissypearse1273]

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?

[@JoelTing]

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?

[@chrissypearse1273]

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

[@thydinh20]

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?

[@JoelTing]

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

[@sravyasai4572]

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

[@JoelTing]

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

[@mrronaldino7298]

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!

[@JoelTing]

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.

[@thanos_sk]

What an amazing video that was! Thank you so much!

[@seducative8796]

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!

[@JoelTing]

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.

[@seducative8796]

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

[@JoelTing]

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

[@seducative8796]

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

[@JoelTing]

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

[@umamaheswari7298]

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

[@JoelTing]

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.

[@مريومالعشوان]

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

[@JoelTing]

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. kzbin.info/www/bejne/fWbUlGWdmNWsjZY

[@MrVerma-md7rn]

Error shown : User-defined type not defined How we solve this error

[@JoelTing]

Hi there, have you added SAP GUI Scripting API under reference in the VBA editor?

[@sameershinde1440]

If I want to generate multiple SAP GL data on the same time so do know about how to generate in vba

[@JoelTing]

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.

[@KiranKulkarni-o2l]

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

[@JoelTing]

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.

[@thefunkynotes8956]

Exactly what I needed!!!

[@shivasiddamshetty]

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 ?

[@JoelTing]

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.

[@lucaspo19]

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.

[@JoelTing]

Hi Lucas, have you tried "Browse" to locate it? I covered the steps in more details in my other video. kzbin.info/www/bejne/bYPbmWRmgNlorJY

[@lucaspo19]

@@JoelTing That worked like a charm! Thanks a bunch for your help. Take care!

[@myNAMEisKIRSTY]

Thank you so much! I think I might be able to solve my problem now.

[@tranbui6310]

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?

[@JoelTing]

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.

[@gadeern6389]

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?

[@JoelTing]

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.

[@jaypatidar5682]

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

[@JoelTing]

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

[@MlungisiSimelane-j3v]

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

[@JoelTing]

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.

[@SeshabhattarSaiKrishna]

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]

Hello! Have you enabled "SAP GUI Scripting API" under References?

[@SeshabhattarSaiKrishna]

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

[@ANUSHAYENGISETTY]

Hi, I am not able to record script in SAP GUI for vendor clearing using T-code F-44. Could you please help me

[@JoelTing]

Hello. What is the difficulty that you are facing?

[@sabbaghuss4250]

Hello Joel, Thank you so much , this video is very amazing and helpful, i am very grateful for your great work.

[@fabriglas]

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?

[@JoelTing]

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.

[@sparklst3f877]

Hi Joel, thanks for this great knowledge sharing. Does Epicor have the same feature to record script like this in SAP?

[@JoelTing]

Hello! I have not used Epicor before. So I'm probably not the person to advise you on this.

[@mohanishmurkute5831]

Thanks a lot for sharing such a wonderful session. Will the coding part be the same for all the table data extracts?

[@JoelTing]

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.

[@mohanishmurkute5831]

@@JoelTing Thanks for the update. I'm ZERO in coding part. But I'll surely check with my ABAP counterpart to give a try

[@classiccarpov2311]

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!

[@JoelTing]

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.

[@classiccarpov2311]

@@JoelTing Thank you very much!

[@classiccarpov2311]

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

[@JoelTing]

@@classiccarpov2311 Hi, yes it's possible. You can run it via sapshcut.exe. You can check out my previous video here for reference. kzbin.info/www/bejne/eZCahneVoZKtraM 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.

[@shealyeecheong9125]

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?

[@JoelTing]

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?

[@indianculture8758]

​@@JoelTingsame error for me. As I mentioned in another comment I have SAP installed on my system.

[@carlheroux777]

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

[@JoelTing]

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?

[@carlheroux777]

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!

[@niccolopellegrino951]

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!

[@JoelTing]

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.

[@WendyWu-t4j]

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

[@JoelTing]

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")

[@AlfonsoRodriguezBarrios]

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.

[@JoelTing]

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

[@mandeepjohal-bb3xk]

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?

[@JoelTing]

Technically yes. SAP GUI scripts are just scripts imitating your actions on SAP GUI.

[@alfazshaikh8986]

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?

[@JoelTing]

Hello! You can try and check if this video is applicable for your case to extract PDF with VBA. kzbin.info/www/bejne/hXiYZYaNpbWZetU

[@jumsjumi45]

Hello ! Thank you for this awesome video ! Guys could you tell me which version of SAP is it ? HANA or FICO ?

[@JoelTing]

Hi there! The version shown here does not contain any of the modules. Just a bare SAP built for ABAP developer.

[@soundofssap]

Thanks for good video. Very clear & useful.

[@aydeediaz2307]

Hi Joel thanks for the video! is working! Is it possible to run the button and have the data downloaded in the same excel?

[@JoelTing]

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.

[@Assal4m]

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

[@JoelTing]

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.

[@Assal4m]

hi@@JoelTing thanks its worked :)

[@deepakjayaram2351]

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

[@JoelTing]

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.

[@shankhamitra6843]

Awesome! well explained

[@gaz5360]

Hey, this is a great video! Unfortunately I cannot find the SAP GUI Scripting API, what should I do?

[@JoelTing]

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: kzbin.info/www/bejne/bYPbmWRmgNlorJY Hopefully it will work for you.

[@mohammadsuheb.l9872]

How to set date from - to in excel and what's the code i have to add ?

[@JoelTing]

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.

[@andresdavidrodriguezzamero2086]

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

[@JoelTing]

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.

[@tallalaazmi1287]

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!

[@JoelTing]

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.

[@hemangvadodariya9387]

Hi Joel Under Browse Option I am not findin sap gui scripting api What should I Do

[@JoelTing]

Hi there, are you using SAP GUI remotely like accessing it with Citrix?

[@DanEmerson-e3x]

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?

[@JoelTing]

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?

[@jasonkit6421]

Hi Joel, i am facing the problem that where my sap not able to open the xlsx file, any idea for that?

[@JoelTing]

Hi Jason, may I know what you mean by SAP not able to open xlsx file? It cannot export the spreadsheet?

[@MsEdls]

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.

[@JoelTing]

You can check out this video to check if you can look for it in your folder. kzbin.info/www/bejne/bYPbmWRmgNlorJY&lc=Ugx_ZXLVMeYmkqr28Zl4AaABAg Usually, you can find the file (sapfewse.OCX) in C:\Program Files (x86)\SAP\FrontEnd\SAPgui

[@warwickarmstrong5530]

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.

[@JoelTing]

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.

[@MoanaArtcrafts]

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?

[@JoelTing]

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

[@quickisdeadly12]

Hi. Is it possible to connect to sap and run recorded macros with office scripts?

[@JoelTing]

Hi there, sorry for the late reply. My understanding is that office script is not able to work directly with SAP.

[@violetnha]

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?

[@JoelTing]

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.

[@kiranarana7274]

The problem we stil have here is that the export excel file cannot be closed using VBA

[@JoelTing]

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.

[@stop007michaela]

Hi, I am looking in VBA in Preferences for the Possibility "SAP GUI Scripting API" but cant find it. Can you please tell me how to get it? :-)

[@JoelTing]

Hi there. Is SAP being installed locally in your computer or are you accessing SAP via Citrix? If it's via Citrix, SAP GUI script will not work based on my understanding.

[@indianculture8758]

​@@JoelTingsame issue as mentioned in another comment

[@KyleRivardDiaSorin]

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

[@JoelTing]

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.

[@franslengwati]

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

[@JoelTing]

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.

[@mohsin9651]

Hi can you wrght a code for taking screen shot and save it to a word document

[@JoelTing]

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.

[@reng7777]

That is what i was looking for ¬¬¬¬ Thanks!!!!

[@TaraCotz]

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?

[@JoelTing]

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.

[@ronniegelacio9102]

Hi Joel, I cannot see the SAP GUI Scripting API on the reference neither browsing. is there alternative reference we can use?

[@JoelTing]

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. kzbin.info/www/bejne/bYPbmWRmgNlorJY

[@ThePenoze]

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

[@JoelTing]

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.

[@abhishekchavan9651]

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.

[@JoelTing]

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!

[@yoke-yinp3188]

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.

[@JoelTing]

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.

[@JoelTing]

Hi there, I just released a video for taking SAP Screenshot with VBA. kzbin.info/www/bejne/oneveqqZh85jlbc

[@yoke-yinp3188]

@@JoelTing Excellent! Thank you so much!

[@RajeshwarUma]

Thank you for this video where did you get that copy paste one could you explain us it's very helpful

[@JoelTing]

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.

[@kunalsolanki4076]

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.

[@JoelTing]

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.

[@abbaskayyum9700]

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

[@JoelTing]

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?

[@joshua6673-p4s]

Very Very useful. Thanks a lot

[@Lalapooh24]

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.

[@JoelTing]

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.

[@juanchito5800]

Hi Joel! Great video! is it possible to also automate log in to SAP?

[@JoelTing]

Hi there. You can check this link to see if this works: simpleexcelvba.com/connect-to-sap-via-excel-vba/

[@violets9186]

Hi joel i tried same to change snc name in su01 but having difficultity to fetch data from excel 😭

[@JoelTing]

Hi there. You mean to login into SAP via SSO? Or you are not able to establish a connection for SAP script to run?

[@YpSoo88]

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.

[@JoelTing]

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.

[@MV_Special]

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

[@JoelTing]

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.

[@anayelibolanos6706]

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

[@JoelTing]

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: kzbin.info/www/bejne/bYPbmWRmgNlorJY Hopefully it will work for you.

[@Anonymous-qq1yy]

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?

[@JoelTing]

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.

[@Anonymous-qq1yy]

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