Video

Hello! If you have something that works for you already, it likely means you have the file in your computer. To confirm, try this: Open File Explorer and navigate to the SAP GUI installation directory (C:\Program Files (x86)\SAP\FrontEnd\SAPgui). There should be multiple .ocx files in there. Can you locate the file there?

@@JoelTing I checked that exact file location and there were no .ocx files in that folder. Is it possible that it is written in a different format that doesn’t require that file?

Hello! If I understand you correctly, you wanted to extract text from a PDF file? I don't think it's possible to read directly from a PDF file. One workaround you may try is to locate the PDF, read the file extract the note using some pattern recognition like regex. I am not very familiar with reading PDF files with VBA though, but from what I see in stackoverflow, it seems plausible.

Hello! To get started, determine the number of lines you'll be working with. Once you have that information, create a loop to input the data into SAP. Try recording multiple lines of input and observe the pattern. You will notice that each line has a row number (1st row is 0) and a column name based on the SAP technical name. By using a loop with a row counter that increments after each row input, you can enter your data line by line into the grid.

@@JoelTing Tq for the response sir

Hello! You can try and check if this video is applicable for your case to extract PDF with VBA. czcams.com/video/OG54SWpPfDs/video.html

Hello! Just right click anywhere on the table you are trying to export, then click on spreadsheet, you should be able to untick that option and select your desired format.

Hello! Thanks for the comment. For context, this is just an example using a small dataset. Originally, this was a VBA and Power BI project where data manipulation was handled with Power Query in Power BI. However, I encountered a bottleneck when I needed a database solution to manage over 30 tables feeding into Power BI, which impacted refresh performance if all the data are stored in Excel. That’s why I switched to Python. It interacts with databases like SQLite much more efficiently. The ability to use SQL to manipulate data before pushing it into Power BI is an added benefit, but the main goal is still to manage all the tables, which was challenging to do with VBA alone. For smaller projects, I agree that VBA and Power Query are more than sufficient and using a database solution would simply be an overkill.

If SAP S4 HANA is running via SAP GUI rather than a browser, it should function similarly. Regarding your second question, in this example, the GUI session is already active. However, you can launch a SAP session using the command line (credentials required), wait for the window to load, and then execute the script.

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

@@JoelTing Yes, i did. I have used SAP GUI Scripting for posting few journal entries before. Now i have given ' Public objGui As Object '. Now its showing a run time error 619- application defined or object defined error. Could you please solve this ?

Hello! I'm not sure how T-code SM37 works. What are the troubles you are having developing a script around that specific process?

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.

Hello! Have enabled SAP GUI Scripting API in "Reference"? I made a step by step video on how to enable it that you may refer to: czcams.com/video/7Rxh10Kt5v4/video.html

Hello! Is there any steps that you are having trouble with?

@@JoelTing Hello, is their anyway I can connect with you Insta, telegram, discord anything?

You can reach me personally via my email joelting92@gmail.com

@@JoelTing You got a new subscriber buddy. ❤️

Thanks for the sub! 😊

Hello! You can use VBA to achieve that. Have some SAP script written to export the data, then use the RefreshAll method to refresh your pivot tables.

Hello! I'm committed to exploring various tools to cater to diverse user needs. If you have SAP and SQL in your toolkit, I highly recommend seeking IT's approval to install Python. Its vast community and extensive libraries make it an ideal choice for streamlining data transfer between SAP, databases, and Excel. While similar tasks can be performed in VBA, they often require more complexity. Power Automate (Cloud) is better suited for automating data transfers between online files or information. In this example, I did use Power Automate (Cloud) to help me with Power BI refreshes. I'd love to hear from you - what specific workflows do you think would be beneficial for your needs?

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.

@@JoelTing Thank you very very much. I will try point 2. Regarding point 1, maybe I describe it not correctly, it seems when the macro runs into a long time ,vba goes into no response mode.

@@zhimabukaimen I believe that’s typical behavior for VBA. When running macros, Excel stops responding to user input. One workaround is to develop your automation script with Python. This way, you can continue to use Excel without interruption while the script is running.

@@JoelTing Thanks Joel, I just got your GUI scripting basic ebook, after finishing it I will try to find other solution of python with your suggestion.

Thanks for your support! Hopefully it will help you to gain a deeper understanding of SAP GUI script. Do let me know if you need any help!

I'm glad my videos are able to help!

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!

@@JoelTing Please do it ;)

Glad it helped!

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.

Hello! What's the error you are getting?

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

It is supposed to be "xlUp", not "x1Up". Try to correct that and check if it works?

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

@@JoelTing thank you so much

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!

@@JoelTing Too bad I don't know Phyton.😞

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.

@@JoelTing that's true. I learned SQL and VBA on my own. I'm not an expert, just enough knowledge to get the job done.

@@yuriitiunov8803 I might give it a shot. Thanks!

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

Hi there, you can change the folderdir variable to that path. This will work for G:\ as well.

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

Thanks for your support! 😊

Thanks for the sub

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

@@JoelTing yes, status bar, sorry. Let me try and see if it works. Thanks

@@JoelTing everything works well. I appreciate your help!🙏

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.

@@explorer-to5vp Hi there, could you check to make sure the formatted current date string is passed properly into the Excel Save as block?

how to solve ?

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.

Hi there. May I understand specifically what difficulties that you are facing when developing automation for this process?

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

@JoelTing Thanks You.

Hope it was helpful!

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。

Hope it helps!

Will take that into consideration for future videos. Thanks for the suggestions!

@@JoelTing Thanks for considering my request and will wait for this particular video.

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.

​@@JoelTing Thanks for your reply. Please keep on making such informative videos. :)

Hi, would it work if you rename the Save To field to a directory that you know does exist?

@@jaredgarbo3679 hi Jared. Do you mean to submit planning data into BW table using Python?

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?

Thanks for sharing this. This seems to be a better approach for SAP GUI screenshots. I'll definitely try that out. Thanks!

Thanks u both!