I will buy your e book because you deserve all the support. Thank you for introducing me to SAP automation with vba and python. I had no idea it is posible
@user-ni9sj8gv2o16 сағат бұрын
Hi Joel, this video was mind blowing. if you have time please make a VBA for F-28 post customer incoming payments, because i struggle design VBA for incoming some billing reference number if there's customer pay for their 1 billing or more than 1 billing.
@209_Violate2 күн бұрын
Only up!!
@thydinh203 күн бұрын
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?
@JoelTing3 күн бұрын
@@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.
@markuskamot99103 күн бұрын
I cant find sap gui in vba what can i do?
@JoelTing3 күн бұрын
Hello! Can you try navigating to C:\Program Files (x86)\SAP\FrontEnd\SAPgui using file explorer and check if sapfewse.ocx file is there? Are you able to record script before going into VBA?
@markuskamot99103 күн бұрын
@@JoelTing yes i can record the Script and now i have the connction. Unfortunately my Skript when i just copy and paste does not work. It seems He does not find the Connection to my sap. What can i do for that
@JoelTing3 күн бұрын
@@markuskamot9910 What error did you e
@RayzaIzaguirre5 күн бұрын
Muchas gracias 🥹🥹🥹 lo pude hacer y ahora me encanta power automate
@joecosio67896 күн бұрын
This is great Joel. Do you have an example if you also want to compare different Periods along with different forecast versions?
@JoelTing5 күн бұрын
Hello! Based on the data model presented in the video, you can conduct comparisons across various periods for different forecasts. This can be achieved by positioning the month data along one of the axes in any of your visual from the DateTable.
@deepakjayaram23516 күн бұрын
Hi joel, your video was very helpful, i followed all the steps as per the video but unable to process the second line from the table which i need
@JoelTing5 күн бұрын
Hello! To identify the issue, consider examining two key areas: 1. Verify if the 'lastrow' variable is accurately capturing the correct number. 2. Ensure that the 'i=2, next i' loop is incrementing properly with each iteration. Try to run the script 1 step at a time and see where it went wrong.
@hyperlycomatose11 күн бұрын
Hi Joel, I am not able to find any .ocx files on my computer. I have never heard of Citrix so I don’t think we use that. I know macros work with excel and SAP for me because I have a file that someone created for me with a macro script. Please help!
@JoelTing11 күн бұрын
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?
@hyperlycomatose11 күн бұрын
@@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?
@stevealbert746011 күн бұрын
Hello Joel! Thank you very much for the videos, they are very helpful! I have a question that I’ve been dealing with, but unfortunately, I can’t figure out if it’s possible or not to copy texts from a note(from attachment list) behind an invoice into Excel using an SAP script, for example, using the VF03 transaction. I have researched this in several places, but I haven’t found a solution. I am running into the issue that it’s not possible to copy text from an SAP HTML Container. Thank you very much in advance for your response!
@JoelTing11 күн бұрын
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.
@stamilarasicharles926812 күн бұрын
Sir i have made a me51n pr transation code that to extract data from excel to sap im able to do it for the first line for making multiple lines what are the alterations that i should make
@JoelTing11 күн бұрын
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.
@stamilarasicharles926811 күн бұрын
@@JoelTing Tq for the response sir
@leeanna222916 күн бұрын
Very helpful! thanks a lot!
@alfazshaikh898618 күн бұрын
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?
@JoelTing16 күн бұрын
Hello! You can try and check if this video is applicable for your case to extract PDF with VBA. kzbin.info/www/bejne/hXiYZYaNpbWZetU
@Rama-yj2cf20 күн бұрын
Do you by chance know how to change the type of export type if you did at one point select "always use Selected Format" ?
@JoelTing20 күн бұрын
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.
@mathijs936521 күн бұрын
I would use vba and power query. PQ is master in manupulating/transform data. Done a similar project. Totally automate not necessary. 15 minutes labor work.
@JoelTing21 күн бұрын
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.
@mfaisal2021 күн бұрын
Will this work with sap s4 hana? Secondly, for this to work do you need a gui session already running? Can we configure this to open a sap session as well so the extraction runs even on weekend.
@JoelTing21 күн бұрын
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.
@SeshabhattarSaiKrishna22 күн бұрын
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 ?
@JoelTing21 күн бұрын
Hello! Have you enabled "SAP GUI Scripting API" under References?
@SeshabhattarSaiKrishna20 күн бұрын
@@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 ?
@rolandmartingumapac27 күн бұрын
Thanks for your video, can you create a video for copying job list from SM37 to excel file? vba will fill up the table from SAP to Excel
@JoelTing25 күн бұрын
Hello! I'm not sure how T-code SM37 works. What are the troubles you are having developing a script around that specific process?
@classiccarpov231128 күн бұрын
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!
@JoelTing27 күн бұрын
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.
@SavorPlalate29 күн бұрын
I am not able to do it showing user-defined type not defined. At Public As GuiApplication. Please help.
@JoelTing28 күн бұрын
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: kzbin.info/www/bejne/bYPbmWRmgNlorJY
@SavorPlalateАй бұрын
Hello I want to create a play button in excel for pasting comments in SAP. Please help anybody.
@JoelTing28 күн бұрын
Hello! Is there any steps that you are having trouble with?
@SavorPlalate28 күн бұрын
@@JoelTing Hello, is their anyway I can connect with you Insta, telegram, discord anything?
can you suggest how to auto export SAP dump at excel, click on refresh button
@JoelTing28 күн бұрын
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.
@anshulnalin7292Ай бұрын
Hi Joel - if possible can you make video using SAP, VBA, SQL, Power Automate (Cloud) not desktop and Power BI as these are only tools I am allowed to use in my work.
@JoelTing28 күн бұрын
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?
@hanselee1Ай бұрын
짱
@zhimabukaimenАй бұрын
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.
@JoelTingАй бұрын
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.
@zhimabukaimenАй бұрын
@@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.
@JoelTingАй бұрын
@@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.
@zhimabukaimenАй бұрын
@@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.
@JoelTingАй бұрын
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!
@brambudi7668Ай бұрын
so much appreciated, i was succesfully running my program based follow on your previous video, keep innovating & sharing mr joel
@JoelTingАй бұрын
I'm glad my videos are able to help!
@tauhidanwar3512Ай бұрын
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.
@JoelTingАй бұрын
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!
@darioamodeo5549Ай бұрын
@@JoelTing Please do it ;)
@TheHermit_mdАй бұрын
Excellent - helped me solve a problem I have been struggling with for quite a while. Thanks!
@JoelTingАй бұрын
Glad it helped!
@AditiKhandare-m8uАй бұрын
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 :)
@JoelTingАй бұрын
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.
@griffis2707Ай бұрын
Buy your ebook, will you take an online course on process automation someday? SAP, PYTHON,.. ?"
@JoelTingАй бұрын
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! 😊
@Thr4sheRGRPАй бұрын
Your help is very much appreciated 👏
@mofa5269Ай бұрын
is there a way to get this code ,like from github
@JoelTingАй бұрын
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.
@danielruedag1858Күн бұрын
Good day Joel, I tried to sign in the email newsletter, but I do not get the confirmation after providing my email. I am very interested to have the code to be able to finish my project. Thanks!!
@JoelTingКүн бұрын
@@danielruedag1858 Hello Daniel! Sorry for the technical difficulties. It may be in your spam folder. I see you have reached out to me via LinkedIn. I'll send the link over to you.
@shinyoda1351Ай бұрын
Hi Joel, I really appreciate your work. You are doing a great job. Where do you get your inspirations from and how do you teach them to yourself?
@JoelTingАй бұрын
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.
@jaredhoffmann7103Ай бұрын
lastRow variable won't define for me. Keeps bombing out.
@JoelTingАй бұрын
Hello! What's the error you are getting?
@jaredhoffmann7103Ай бұрын
@@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Ай бұрын
It is supposed to be "xlUp", not "x1Up". Try to correct that and check if it works?
@jaredhoffmann7103Ай бұрын
@@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 🙃
@mofa5269Ай бұрын
what if i want multiple tables appended/increment load on the db every 4 hours?? then what should i do?
@JoelTingАй бұрын
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()
@mofa5269Ай бұрын
@@JoelTing thank you so much
@bzflowerbeeАй бұрын
Hi Joel, I just noticed you have "book a call with Joel". May I know how much do you charge for the session? Thanks.
@JoelTingАй бұрын
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!
@bzflowerbeeАй бұрын
@@JoelTing Too bad I don't know Phyton.😞
@JoelTingАй бұрын
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.
@bzflowerbeeАй бұрын
@@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.
@bzflowerbeeАй бұрын
@@yuriitiunov8803 I might give it a shot. Thanks!
@chrisyip8260Ай бұрын
Hi Joel, million thanks about your sharing. Can I know this coding can be used in SE16N?
@JoelTingАй бұрын
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.
@chrisyip8260Ай бұрын
@@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.
@JoelTingАй бұрын
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.
@vishalrajbhar2954Ай бұрын
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.
@JoelTingАй бұрын
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.
@vishalrajbhar2954Ай бұрын
Hi joel i am facing run time error 404. Line 4 set session = objConn.children(0) pls help
@JoelTingАй бұрын
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.
@defaultHandle1110Ай бұрын
How do you save to custom name and folder on g:\
@JoelTingАй бұрын
Hi there, you can change the folderdir variable to that path. This will work for G:\ as well.
@defaultHandle1110Ай бұрын
@@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.
@JoelTingАй бұрын
@@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.
@bzflowerbeeАй бұрын
Thanks!
@JoelTingАй бұрын
Thanks for your support! 😊
@morganyard1627Ай бұрын
You are sent from God... Bless ur soul.. subscribed rn
@JoelTingАй бұрын
Thanks for the sub
@bzflowerbeeАй бұрын
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.
@JoelTingАй бұрын
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.
@bzflowerbeeАй бұрын
@@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!
@JoelTingАй бұрын
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
@bzflowerbeeАй бұрын
@@JoelTing yes, status bar, sorry. Let me try and see if it works. Thanks
@bzflowerbeeАй бұрын
@@JoelTing everything works well. I appreciate your help!🙏
@user-fv7hk8gw6wАй бұрын
How does this ocmpare to using Power Automate for SAP scripting? Do you get more custom behavior out of this?
@JoelTingАй бұрын
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Ай бұрын
When the excel has been extracted the current date is not included in the file name, can anyone help me with this?
@JoelTingАй бұрын
@@explorer-to5vp Hi there, could you check to make sure the formatted current date string is passed properly into the Excel Save as block?
@JP-br7ehАй бұрын
destinationFilePath = empty
@JP-br7ehАй бұрын
how to solve ?
@JoelTingАй бұрын
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.
@Daksha636Ай бұрын
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Ай бұрын
Hi there. May I understand specifically what difficulties that you are facing when developing automation for this process?
@Daksha636Ай бұрын
@@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.
@camiloandresochoagarcia2453Ай бұрын
@JoelTing Thanks You.
@sdfsdfsdfsdf8556Ай бұрын
Amazing Tutorial, simple explaining a complex matter.
@JoelTingАй бұрын
Hope it was helpful!
@philip8232Ай бұрын
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
@rupalineema4925Ай бұрын
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.
@JoelTingАй бұрын
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。