Відео

Hello. You can register with your email here and download the whole package used in this video. joelting.com/newsletter-download/

Hello. I recommend reaching out to your IT department. They often have specific policies regarding automation. When it comes to automating data inputs, they tend to be quite cautious. However, if you're only extracting data, they're usually more open to providing scripting access. Some companies create a separate SAP ID specifically for automation purposes, restricting it transactions that provides read-only access to users.

Hello. Are you referring to getting the text in the status bar? You can access text in status bar using session.findById("wnd[0]/sbar/pane[0]").Text

@@JoelTing No, I'm refering to the dialogue box that will appear when I create/change a document. For eg: lets say I'm using VA01 T code or vf01 or any other creation or modify t code and when I'm done making changes in the document using VA01 T code, I'll save it and it will generate a Document number which will trigger a 606183 has been created or saved in the bottom left corner which you can double tap and it'll open a window(called performance assistant) from which I want copy just the 606183 number.

and then paste it in a cell in excel

If you only need the number, you can identify the pattern in it, and get the number from the text. For example if the text in the status bar is "Document Number 606183 has been created", you can try the following: For i = 1 To Len(sbartext) If IsNumeric(Mid(sbartext, i, 1)) Then result = result & Mid(sbartext, i, 1) End If Next i ‘To paste value in Excel Range("A1").Value = result Assuming there's always only one number in the text, this will extract the numerical character only in the text. Then, you can paste in one of the cell in your Excel spreadsheet or use it later in your VBA script. This method works best when there's only one number in your text. If your text contains multiple numbers, you might need a different approach.

Hello. I have provided it in the description box. Feel free to download it.

Thanks, glad you liked it!

Apologies for the delayed response. Regarding your question, you can use Now() to retrieve the current date and time. Does this address what you're looking for? To design the approach, I would need to better understand your specific use case.

While Excel can replicate many SQL functions, the real advantage of SQL lies in its performance and data management capabilities. When dealing with multiple table joins and complex queries, SQL executes these operations significantly faster than Power Query merges in Excel. If you are merging queries in Power Query, things will get slower and slower as you merge more tables in a single query. In SQL, you can create multiple views from the same from the same data, eliminating the need to duplicate data or maintain multiple files. Any changes to source data automatically reflect across all views, making data management more efficient. That being said, if the data is small and they can be processed efficiently using just Excel, I will always go back to Excel because SQL is not something that everyone would learn in the Finance community. Building the solutions in Excel are more easily transferable to others when someone else needs to take over the maintenance of the solutions.

@@PhuongNguyen-cv1zk hello. There does not seems to be a way to handle the opening of saved file well. One workaround is you could try exporting the output in txt file and use Power Query to convert it into an Excel table. I covered this in my latest video.

Based on my experience, many companies are open to SAP GUI Scripting in production systems when there's a clear business need, especially for data extraction purposes. Typically, IT departments are willing to assist with such requests. They often provide scripting access to specific users or create dedicated robotic SAP IDs for this purpose.

I'm not too familiar with SAP Business ByDesign. Is that web based? If it is, then SAP GUI Script will not work (SAP GUI Script only works for SAP GUI) but the general approach can still be applied using Power Query. For web-based systems, you can automate the extraction process using Selenium. Once you have the output file's path, you can replace it the parameter with the latest output file path, allowing the process to be automated in a similar manner.

Hello! You can get the file for reference using the link below: www.dropbox.com/scl/fi/94a84nivqyeehtvv5fb7q/FAGLL03Extract.xlsb?rlkey=d8mlg4mrc62kbwrjuxy1x0gkn&st=onekagzv&dl=0

Yes, and it's much easier to maintain since not all users are familiar with VBA, while Power Query is quite user-friendly for everyday Excel users. I've worked on several projects and successfully handed over their maintenance to the team with little VBA knowledge.

Hello! I'm not too familiar with the processes around IA01. Maybe you can book a call with me and I can give you some ideas on how this could be set up?

Thank you! :)

Hello! That can be done by just assigning the element text value to a cell value (for example: Cells(1,1).Value = session.findById(elementID).Text ). You just need to identify what the element ID in SAP, then assign it to a cell value in Excel

Hello! Is it possible to output your table as a txt file instead? I found that using txt file would eliminate the complications of working with Excel file output.

Hello! I'm not too sure what you are looking at. Do you have scripting tracker installed? You can try using that to check the ID.

Hello! That can be done by just assigning the element text value to a cell value (for example: Cells(1,1).Value = session.findById(elementID).Text )

@JoelTing Thank you so much!!

Hello! 1. Try and debug step by step and see what's wrong in the loop. You should be able to pinpoint which part of your code goes wrong. I usually use For next loop and that has been working well for me. Alternatively, if you have multiple values, you can make use of the clipboard as well. Customize the clipboard with the input values, then in SAP use the paste from clipboard button. 2. Deleting the current file is one way to do it. Check if the file is already exist, if it is then delete it. Another alternative you can think about is to use the current time as part of the file name. Then, you would be sure that whenever you run the script, the file name is always unique.

Hello. So far, I am only able to get it work with LocationURL. Have you switched the Browser Control in SAP to Internet Explorer? When I use Edge as my Browser Control, then I am not able to locate the LocationURL.

Hello! I'm not entirely certain about your case. You should be able to interact with the word doc. From there, you should be able to save it just like your Excel file in VBA.

@JoelTing ya if I can do it manually I can save it I want it save automatically

@@Riyaz313 does the word document opens itself? does the word document have the same name for each script run?

@JoelTing yes it's open it's self and have different name every time. So I can have lists of labels in specific folder 📁 so I have record that how many label I have given

@@Riyaz313 If you are familiar with VBA, you can try to get the word application using getobject method, then you would be able to connect to the word document and save it into your desired folder. You may need to establish a loop to keep the script waiting until your word document is loaded before connecting to it. That would be the approach I'll try. As I do not have a use case from my end, I cant be sure if it will work.

Hello! I personally didn't use T-SQL before. From what I understand T-SQL, or Transact-SQL, is an extension of standard SQL designed specifically for Microsoft SQL Server. It adds extra features that aren’t available in standard SQL. While the basics of SQL are the same across different databases, T-SQL offers tools tailored to SQL Server. So, if you’re working with MySQL, for instance, you won’t have access to T-SQL features. So, I would say what you need to learn would really depends on what database that you are working with.

Hello. I do not have access to SAP via Citrix, so I'm not able to test if there's any workaround for it. Perhaps you could try to run the script in an Excel workbook within the Citrix environment to see if it works that way?

Hello. What is the difficulty that you are facing?

Hello. You can check it listed in the description box of this video: ua-cam.com/video/VBmbuSXRvIg/v-deo.html

Hello. You can try only copying the range right before you click on the "Paste from Clipboard" button.

Hello. Sorry for the late reply. Are you referring to getting the text in the status bar? You can access text in status bar using session.findById("wnd[0]/sbar/pane[0]").Text

@@JoelTing hi Joel! Thanks for this, will try it out 🙂

Hello. Are you using SAP via Citrix? Can you check via File Explorer if you have the folder "C:\Program Files (x86)\SAP\FrontEnd\SAPgui"?

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?

Hi! Thanks for you response! I will definitely give it a try! The size of my extraction are quite big indeed so that makes sens that Excel can't open the workook fast enough. Again, thank you the valuable informations you share! Its highly appreciated!

I'll try to work out some content on this. The general idea is once you have all the paths of the different reports after the export, you can just open them with VBA, then transfer them over into your current workbook. Another way to do it is via Power Query where you can setup data transformation steps and load it into your current workbook. We can use VBA to dynamically change the source into the newly extracted files.

Hello! You can try adding Application.DisplayAlerts = False before you run your script and right before it ends, set Application.DisplayAlerts = True again so that Excel can work normally.

Hello! Can you let me know which line triggers run-time error '438'?

@@JoelTing I didn’t expect you to answer, thanks so much! In the meanwhile I sorted the problem out. However now I have a new issue, i.e. I would like to input several data in SAP t-code (so instead of just one country code, for instance, I want to input 40). Do you know if it is possible? I have tried everything but nothing seems to work. So far I only managed to achieve this by doing a range.copy and then using sap script to paste 40 values in it. But it doesn’t seem the right way to me, even if it works

Also would you know which is the line of code needed to open SAP from scratch? I would like this macro to work even if the user does not have SAP already open

For multiple inputs, using range.copy and pasting from the Clipboard is the most efficient method. Alternative methods, such as scrolling and inputting values one by one, are more prone to errors due to potential variations in workspace size. Are you experiencing issues when pasting values, such as missing data?

To open SAP, the general idea is to run SAP using the following function: Function RunSAP(system As String, client As String, user As String, pw As String) Dim command As String command = "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\sapshcut.exe -system=" & system & " -client=" & client & " -user=" & user & " -pw=" & pw & " -language=en" Shell command, vbNormalFocus End Function This requires the password of the user. Another thing to note is that this approach does not work for system that uses SSO. It's best to get SAP user ID that can log in with password and have access to SAP GUI Scripting. Once you run this function, you will need to run a loop to check whether the SAP window is opened before connecting to it.

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.

Hello. That seems to be a decimal place field? Could it be a maximum decimal place in SAP? As a first step, try to do it manually to see if it works. You just need to make sure the data you passed into SAP works in the first place. For example, if the SAP field only works with 2 decimal place, you must convert the value into 2 decimal place 1st in VBA before passing it into SAP.

The problem already solved by adjusting a little bit the script. Now I managed to run the script completely 1 cycle until the transaction is saved. unfortunately, it won't loop to proceed with other order nos. When i try to run it mentioned about couldn't found the ID? What could be the issue... Is it SAP settings or the script itself?

@@fadlyriza6314 That's most likely your session is not at the correct screen to input another order. That error shows that it is not able to find the field you specify to fill in the value. You can try putting in "/n" in front of your transaction (ie. /nMM03)to make sure it will always go to the main transaction page for you to perform your processes.

Hello! The T-code you mentioned there is not a standard T-code. I do not really understand what you meant by moving the workbook. The script recorder will only record interaction in SAP GUI. Once you are out of SAP, like using file explorer to save file, the recorder will not work. Depending on what you need to do, there's some workaround for it.

yea. Agree. If there's access to the scripting tracker, I would definitely use the tool to record in python instead of using the native recorder in SAP.

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.

Thank you so much for your support!

Hello! I'm not familiar with the processes surrounding customer incoming payments. Just thinking out loud, could we potentially read the number of billings a customer has made and then, based on the specific billing invoice they paid for, create a loop that iterates through their current outstanding invoices and checks them against the billing they made?

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

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?

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

@@markuskamot9910 What error did you e