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 😀

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

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

[@jesseniatavares7926]

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!

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

[@sdfsdfsdfsdf8556]

Amazing Tutorial, simple explaining a complex matter.

[@JoelTing]

Hope it was helpful!

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

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

[@Rajuuutube]

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

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

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

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

[@fdkt1982]

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

[@luisramirez2123]

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

[@MK0.0]

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

[@jameslimpin2537]

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

[@thanhphamduy]

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

[@sabbaghuss4250]

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

[@myNAMEisKIRSTY]

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

[@thanos_sk]

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

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

[@mood902]

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

[@JoelTing]

Thanks again!

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

[@soundofssap]

Thanks for good video. Very clear & useful.

[@thefunkynotes8956]

Exactly what I needed!!!

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

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

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

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

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

[@virgiiv]

amazing, it´s incredible

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

[@jaisonkx4666]

Thank you, thanks a TON.

[@유진우-p2l]

Very Very useful. Thanks a lot

[@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)

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

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

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

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

[@pagnonig]

Awesome

[@reng7777]

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

[@shankhamitra6843]

Awesome! well explained

[@sunv8500]

Interesting ................thanks for sharing

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

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

[@kiranarana7274]

Dear joel, Could you please creat video how to creat vba SAP AS01 Create Asset Master Record all data from excel file Thank you

[@JoelTing]

Let me see what I can do. Thanks for the suggestion!

[@harshbiswari1611]

Amazing

[@prasenjitmandal5486]

You are truly Amazing

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

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

[@콧물-h2j]

You are the best!

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

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

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

[@gruber7393]

First I want to thank you a lot for the video. It really helped me :) and I really appreciate that you read and answer the comments. But I have another problem, maybe you can help me. I want to open a excel file in SAP. Here I have to select various options (country, year, sales, costs…) via macros. How can I automate that with vba? Because the screen recording of SAP doesn’t record what I’m selecting in the excel file…. Thank you again :)

[@JoelTing]

Hi Gruber, thanks for the kind words. For automation via VBA, yes you can. Screen recording for SAP only record what is happening in SAP GUI. Anything outside of it will not be captured. To be more flexible about how you can retrieve information in Excel, you need to have a basic understanding of VBA. For example, if you want to get a value in cell A1, you can get it by using the following code: Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1").Value

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

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

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

[@dmytroskrypka]

I used to have a custom desktop app that would scan SAP window for all Object tree structure and you can write a code without needing recording macro using better methods described in SAP scripting help

[@JoelTing]

Interesting. What is the custom desktop app? Is it the built in Inspect tool in Windows?

[@dmytroskrypka]

@@JoelTing that one used to work till 2011 I think. Then Microsoft phased it out. I'll try to find the name of the tool. It was 10 years ago so ..

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

[@bilge9888]

excellent video

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

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

[@yuvrajangadi1379]

can you do a video on how automate ourchase info record (PIR) please, thank you

[@JoelTing]

Hi, which aspect are you looking at? Input of data into PIR? or extraction of PIR data?

[@bartdevries8726]

Hi, Thanks for the amazing video. I would like to know how to loop this macro while it selects a new cell each loop. Maybe a next video? Greetings from The Netherlands!

[@JoelTing]

Hi Bart de Vries, let me see how I can incorporate this in my future videos. Thanks for the suggestion!

[@makeauditeasier4732]

Great video - thank you :)

[@kapibara2440]

Fantastic video! ❤

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

[@muhammaddanish9149]

Please make a video on how to change a vendor master specific Field in mass through vb script in SAP Thanks

[@JoelTing]

Hi Muhammad Danish, thanks for the video idea. Unfortunately, I do not have access to all the different modules to make videos relating to them at the moment.

[@muhammaddanish9149]

@@JoelTing we will wait for this thanks 👍

[@taylorberman7851]

This doesn't work for me because my SAP opens up the windows file browser when I try to export to excel. It doesn't give the option to name my file in SAP.

[@JoelTing]

Hi there. Do you have "Show native Microsoft Windows dialogs" enabled in your option?

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

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

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

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

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

[@karmaestark]

Hi, muchas gracias me funcionó a la perfección

[@JoelTing]

Thank you for the kind words!

[@bibeo2493]

Thank you so much 😍

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

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

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

[@ayhobbyist]

great explanation, thanks a lot. would you please explain to to extract long texts from read_text function module to excel ?

[@JoelTing]

Hi Ahmed Yahya, I'm not familiar with the read_text function module. I did some search about it, seems like it's part of ABAP. The scripts that I developed usually is just to replace the manual steps performed with SAP GUI.

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

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

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

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

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

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

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

[@JesseNethononda]

stuck on excel error message: Compile Error - Sub or function not defined. please help...😭

[@leeconales8307]

Thank you!

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

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

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

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

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

[@vijayj3296]

Nice video

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

[@pratikrahane4101]

Informative video