I'm a Regional Financial Controller and on this channel, I create content that helps people to lead more productive work lives so that we can spend time on things that matters more in our lives. As a financial controller or analyst, there is a lot of data that we need to extract, consume, analyse and visualise. In this channel, I will be sharing some of my processes that I have been using to improve my productivity and shorten the time needed to perform each step of the data analytics process. Hope you will find this channel helpful! Oh ya, nice to meet you! If you ever want to get in touch, please DM me on Instagram/Twitter/Facebook/Email - I will try my very best to reply to you :)
Hello Joel, May I ask you two questions? 1. If a T-code runs quite a long time(10mins) to get the result, the vba will remind no respone to wait ? How can we process this issue, is it possible to let the sap scripting run in background even I run it in new window? 2. When I use sap GUi scripting, I cannot upload file with the default sap dialogue. No window pop up to let me select file. Thank you very much.
Hello! 1. You can try to temporarily disable alerts by setting Application.DisplayAlerts = False before running the script, and then revert it back to True afterwards. This should suppress VBA from displaying reminders and warnings, allowing your script to run uninterrupted. 2. Is it possible to see if it would trigger the upload dialogue? Try to input the full file path and see if it would trigger the upload. As SAP GUI scripts are unable to interact with Windows pop-ups natively, so this method is actually preferred.
Hi Joel . Thank you for this informative video. You are doing a great job. I have request for you to create this project in a tutorial form which covers very details. It will be really helpful.
Hello! Thanks for your kind words. If this video sparks sufficient interest, I'll be happy to create a step-by-step tutorial to guide through building this project from scratch. Please share this with whoever you think would be interested in this project. Thank you!
I just used this for SAP Logon 800 and was getting the similar errors of "Not all data .......". I removed the " " for system ID, Client and username and Password, basically wherever they were supposed to be, and it worked. I am hoping to know, how can I get an input from a user for different login IDs and passwords, and then login to SAP with those user credentials? so that different users can login rather than just me. Thankss :)
Hello! I'm glad that it worked for you after some of your testing. If you would like to ask for users input in Power Automate Desktop, you can use "Display input dialog" action to get the user's username and password, then run the command with the variables submitted.
Thank you for your support! Currently, I don't have plans to launch an online course, as it requires significant time and effort. I'm also mindful of ensuring there's sufficient demand before investing in such a project. However, if there's enough interest from all of you, I'll definitely consider it. In the meantime, I'm more than happy to help answer any questions you may have. Feel free to book a complimentary call with me, and we can have a chat to see how I can assist you. I'm always here to help! 😊
Hello! You can get the code, database and Power BI file by signing up to my email newsletter. joelting.com/newsletter-download Then it would be delivered straight to your email inbox.
Hello! Thanks for your kind words. I always start with a clear end goal in mind and then work backwards, breaking it down into manageable steps. To fill in the knowledge gaps along the way, I rely on a few trusted resources. Google is my go-to for researching new concepts and finding relevant information. The Python community is incredibly vast and supportive, with many valuable resources available on platforms like Reddit and Stack Overflow. With the development of AI, if there's any concepts that I don't really understand, I would ask ChatGPT or META AI to explain it to me with examples. Hope this is helpful to your own learning journey.
@@JoelTing Thank you for your willingness to help! When I try to run the script it gives me a 'compile error variable not defined' it seems to be highlighting the 'x1Up' part inthe script you created: 'lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Row.Count, 1).End(x1Up).Row' also when I pull the lastRow variable into the watches window, it says it can't compile module. Thanks!
@@JoelTing I also noticed I had 'Row' instead of 'Rows' that fix along with your clarification worked. Thank you for the help! It's funny that in the macro table font '1' and 'l' look identical 🙃
Hello! Assuming you are running the script without any user input and your pc is on all the time, you can try using the apscheduler module. You can define the scheduled script to run every 4 hours. Something like the following: from apscheduler.schedulers.blocking import BlockingScheduler sched = BlockingScheduler() @sched.scheduled_job('interval', hours=4) def timed_job(): print('Script within timed_job will run every 4 hours.') sched.start()
Hi! You can book a call with me using the link: joelting.com/book-a-call/ During this call, we'll discuss your data management challenges and opportunities, and I'll share my view on how you could optimize your workflows. If you find our conversation valuable and would like to continue with personalized coaching or collaborate on a project, we can discuss further arrangements. Otherwise, consider this call a free consultation - no strings attached!
If you're interested, you can always learn it at your own pace. 😊 Personally, I began with VBA, but soon encountered limitations when trying to move data into a database. This led me to explore Python, and eventually, I was able to develop this end-to-end project that automatically retrieve data and refreshes Power BI. It has been a fun and rewarding journey.
Hello! If you can access it through the SAP GUI, then the script should work similarly. The SAP GUI script simply mimics user interactions. As long as you have the necessary access permissions and scripting is enabled, you should be able to successfully replicate the process in the script.
@@JoelTing Thanks Joel. I tried and this is works in SE16N. What if there are multiple excel ranges and paste into multiple SAP selection? Do I need modify some coding? For example: Customer ID (A1:A5) & Country(B1:B5) in excel ranges and paste into SAP "Customer ID", "Country" multiple selection.
In VBA, you can just copy the range using Range().Copy, then use the upload to clipboard button in SAP to paste. Just do it in sequence, then this should work.
Hi Joel.. How can close the workbooks after export data from SAP . Bcz it’s showing run time error 91 ( Out of the script ) while workbooks already open and VBA code doesn’t work pls help.
Hi there. It's something that I have yet been able to solve at the moment. Workaround I did try is to export it into a text file instead, then copy the text file content into a new workbook and save it from there.
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.
@@JoelTingI think my company just saves to g:\my drive\Sap exports. Some hard coded crap. I raised a ticket. In the meantime I’m using python to rename the file and move it. Question how do you have this run at 7am daily ? Sap gui timeout seems like it might be an issue. I can schedule the script to run, but it won’t work if it’s timed out.
@@defaultHandle1110 Renaming and moving it after export is a good way. There's always limitations, just need to think of a way to work around them. Regarding the timeout issue, I'm assuming you're referring to SAP logging out due to inactivity? If you're not using Single Sign-On (SSO), consider programmatically opening the SAP GUI (using sapshcut.exe) when no session is available to connect to. This might help mitigate the timeout problem.
Hey Joel, thanks for the video. I have something very similar to what you did in your video. So, after I added the variable of lastrow to the watches and run the script. But received an error message "Run-time error '619': The control could not be found by id. This is the line where it stopped : Session.findById("wnd[0]/usr/ctxt/CUM/CUNIT_IO-CUTYP").Text = "2" (Script from SAP). Do you have any suggestions how to resolve it? Thanks so much.
Hi there! This line returning error means that there's something different when the script is running compared to the time you recorded it and it is not able to locate the object you identified in the script. Would you be able to identify the difference? Try running line by line and you should be able to see why it is not able to fill in "2". The field may not be there at the point of the script.
@@JoelTing Thanks for the explanation. I think I have resolved the issue. However, I have another question as far as the "Business Partner" number you extracted from SAP and populated to the Excel sheet. That part, on my screen, once I clicked "Save", it will go to the first screen and the number created will show on the side bar (lower left corner). In this case, do you think I still can click on number on the side bar and record the SAP GUI Script? Thanks a bunch!
I'm assuming the text from the lower left corner that you are referring to is the status bar. The text can be retrieve using the line below: session.findById("wnd[0]/sbar").Text You can even check if it is successful, or it returns error using session.findById("wnd[0]/sbar").MessageType
Hello! Power Automate is more user-friendly and you can achieve similar things with it. I find that VBA offers more flexibility when it comes to customizing code as everything is just lines of codes. If you are using Power Automate, you need to break that down into different blocks, increasing development time. This advantage becomes even more apparent as your automation projects becomes more complex.
Hey there. I'm not able to understand what happened there with so little context. It could be the Browser Handle not returning the file name or it could be some other reason that breaks the assignment of that variable.
Hello Joel, please make a video on creating multiline PO (ME21N) via Purchase Requisition using SAP GUI Script and Excel VBA. It will be very helpful . Thanks in advance. 🙏
@@JoelTing With the help of your videos I am able to automate single line PO creation through VBA and SAP GUI Script and thanks for making life easy at workplace however if I have multilines PO, I am not able to create and getting error and I am not much proficient in VBA, what ever I know is from your videos only. Actually I copy PR number through "Document Overview ON" functionality on T-code ME21N. But the moment code reached on Document overview , code shows invalid ID error. Please make a video on multi line PO creation through PR. Again thanks for sharing such precious knowledge.
I agree, it's just too bad the finance jobs are very behind when it comes to technology and they're not utilizing this - they store everything in Excels
Thanks This video is really helpful. But while executing a particular transaction type there is no data generated in SAP and then I want to continue with another company code how to do that.
Hi there, you can actually use the status bar to your advantage in your script. Depending on is being shown in the status bar, use if statements to control the script. In your case, if you see a "success" message, then continue with the rest of the script to export, else, next loop. Hope this helps。
Please also make a video on how to create multi lines PO with the help of SAP Gui Script and thanks a lot for sharing such informative videos and giving us a thought how we can use SAP Gui Script and VBA to decrease the work load of repetitive task.
Hi there, I took a look at the T-code for creating multi-line purchase orders. I'm not very familiar with how this process works in operations, so I might not be the best person to advise on how it could be implemented.
Hi there, not too sure what you are referring to there. I have most of my experience working on Finance related SAP module. Are you referring to any specific T-codes?
I recorded a script for purchase order creation and tried with excel vba....sometimes it works and sometimes it does not....there is some problem with ID....SAP is changing control ID's...How to tackle this problem?
Hi there, typically, it shouldn't change unless something different occurs. If you encounter any unexpected changes, such as additional pop-ups for certain items during the purchase order creation, you'll need to use conditional statements to handle these situations. For example, if it encounters this specific ID, then it should follow a designated action.
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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-7Rxh10Kt5v4.html
Hi there. It's possible if you are using the normal way of logging in. As of now, I'm not able to reliably automate SSO login. You can refer to the link here to learn more about logging in SAP using scripts. joelting.com/articles/sap-gui-script/login-sap-using-vba/
Hi there. I'm not sure if I understand you correctly. I believe you are trying to send data from different Excel cells into SAP. If my understanding is correct, you can refer to the link below on how this could be done: joelting.com/articles/sap-gui-script/send-data-from-excel-to-sap-gui-with-excel-vba/
Hey Joel, lets say you want to have the header of the selected sales revenue and comparator sales revenue to be dynamic (based on the user selected value in both the slicers). If user selected JAN RE in selected sales revenue slicer and MAR RE in comparator sales revenue then the columns headers should be like JAN RE and MAR RE What should we do ?
Hi there, to create dynamic column headers based on user selections in both slicers, you can have a measure that adds selected and comparator total together. Then create a version table that has relationships with both the selected and comparator tables. Use this version table as a column in your visual. This way, you will see the column header changes according to the users' selection. However, it's important to note that this method has a limitation: you won't be able to visualize variance in the same table, as the version is now being used as a column.
Why can not I use below script? Can you help me? session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0019/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:3212/cntlGRIDCONTROL/shellcont/shell").pressToolbarButton "&MEREQDELETE"
I just launched "SAP GUI Scripting: Understanding the Basics" ebook! If you are interested to learn more, check it out in the link below: ko-fi.com/s/c366da7e71
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
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
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
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
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/
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/