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
Really you have given me a very lovely tricks and I am using in my daily work .100/100 I love you Joel You saved my lot's of time and please upload more video's Your videos more useful.
Thanks for your kind word. I'm glad you find my videos useful. I'll work on more videos relating to tips to save time using different tools as an analyst. Thanks again for your support!
I am recommending your videos to others .. really ,,I am herlty sying to you you are the best Please give more video's to us ,,,, I am working on SAP It's my firstexpirince
Hi there, I'm not too familiar about the uses of LB10 operationally. Perhaps you can try recording and see if it works with similar method and if you face issues, feel free to send me an email. I'll see how I can help.
Hi Joel. This is not wokring for TCODE /nf.27 The print option is not being recorded by SAP GUI And hence my export is not working. If you can help me with this.
Hi saket004, apologies for the late reply. You can try out the following code which I found on SAP forum (credits to Script Man): set Wshell = CreateObject("WScript.Shell") Do bWindowFound = Wshell.AppActivate("Print") WScript.Sleep 1000 Loop Until bWindowFound bWindowFound = Wshell.AppActivate("Print") if (bWindowFound) Then Wshell.appActivate "Print" WScript.Sleep 100 Wshell.sendkeys "{ENTER}" end if bWindowFound = Wshell.AppActivate("Print") if (bWindowFound) Then Wshell.appActivate "Print" WScript.Sleep 100 Wshell.sendkeys "{TAB}{ENTER}" end if bWindowFound = Wshell.AppActivate("Print") if (bWindowFound) Then Wshell.appActivate "Print" WScript.Sleep 100 Wshell.sendkeys "{TAB}{TAB}{ENTER}" end if on error resume next session.findById("wnd[1]").close on error goto 0
Hi Pier, I have not use the script to save pdf files from adobe acrobat opened. Do you mind telling me what is the T-code that you usually use for this and how you open the PDF in SAP step by step? I'll try running it in my test system to see if I can figure something out.
Hi there, I believe that you can do that. At the end of the script, you can continue to read the exported data workbook, then copy all the data and paste it into the VBA file that you are in. The typical workflow would be export from SAP -> read the exported spreadsheet or data -> copy all the data -> create a new worksheet in your VBA file -> paste in all the data.
Hi @joel, Thanks for the video... I have a request, how to record 2 different transactions (MM60 and ME2M) and export the output to one Excel file with each sheets seperately... I tried to do it after watching your video of dynamic scripting...but for me it's not working... Can you help me how to do it?
Hi there. What is currently not working for you? The steps should be quite similar you record yourself going through both tcode, then once both are exported, open both sheet, copy the data and placed it in your desired Excel workbook.
You Got a new subscriber......👍👍 You are an inspiration to people who are lazy on repeated report extraction all the time..😜 and excited to improve productivity and save time🦾🦾
Hi. What you can try doing is try to read the status bar with session.findById("wnd[0]/sbar").Text in cases where there's no data found. For example, you can put an if statement, For i = 1 to 3 if Not(session.findById("wnd[0]/sbar").Text = "No items selected") Then "run your script" End If Next i. The text needs to be changed based on what is being displayed at your end.
Hi there. Currently I don't have a tutorial for Google Sheets. From a brief research, Python looks to be a simpler way to do it instead of VBA as there's already libraries readily available. Probably it can work this way: Script to extract data from SAP GUI into Excel -> Use Python Pandas to read the Excel data -> send data via Google Sheets API.
Hi Ibrahim, usually what I will do is I will isolate the chunks of codes (like a new paragraph) that I need the loops to be performed. Once those are isolated, I will indent them and put a loop around it (could be For..Next, Do..While loop etc).
Hello Joel thanks for sharing automate video of sap .. I am stucked in where i want to run two transaction in sap mb51 and Mb52 on a button click in excel and it saved in desired location ..can you please help me out
Hi Joel, I'm really seeking for this same. I need the same what you showed with the text files. I have a 20 set of text files which needs to be used for downloading a report. And need to save with the same name. Could you pls help me out with this
Hi there, what do you mean by 20 sets of text file and download 1 report? What is the outcome? Is it only one spreadsheet at the end? Since yhou are saving it with the same name, do you mean you will replace the previous version upon export?
hello, i'm trying to do similar task of extracting multiple reports but from different sap systems, i tried to do multiple vbscript one after another but after extracting 1 system, it stops, do you have an idea how to do it?
Hi user is asking don't want close macro file but need to close SAP download reports only. Please do video on this. SAP reports downloading multiple files so those files need to close only not macro file.
I do not have a working solution now. A workaround that can be done is to export it using a local file instead of a spreadsheet and then look for some ways to automate the extraction from the local file to Excel.
Hi Joel, thanks for the videos, as a complete beginner this has taught me a lot already. I have one question though, how to close all instances of Excel, it only seems to close one instance (The original one with the three rows of countries in your case). Since I have created 3 reports like yourself, I have all three created ones left open. Even with the code at the end
Hey there. I'm not too sure about this issue where not all instances of Excel is closed using the Application.Quit function. You could try looping through all your exported file path and then close and quit them accordingly. I'm just showing below how this could work for 2 workbooks. Loop through all the exported file path and substitute them in the GetObject function. Dim export1 As Object Dim export2 As Object Set export1 = GetObject(“file location\Export 1.file extension”).Application Set export2 = GetObject(“file location\Export 2.file extension”).Application export1.Workbooks(1).Close export2.Workbooks(1).Close export2.Quit
Hello Joel, Thank you for your informative video. Do you have any video "How to import data into SAP?". I have time records in Excel sheet, and I want to import it to SAP instead of inserting it one by one using "CAT2".
Hi Hamada, would you have the correspondent reference as well (which document number relate to which time record) in the same Excel sheet as well? Not too familiar with Tcode CAT2. In terms of importing data, not sure if you are referring to using script to loop through Excel and pasting in? The workflow should be similar. 1) Identify the range of cells you want to put in Excel 2) Get the cell value 3) Paste into SAP by assigning the value into the SAP control ID
Hi there, just convert the date into the string format that your SAP will need and store it in a variable, then replace the hard coded date in your recorded script with the variable.
Joel I have a question after watching this. Suppose that the for loop works the other way: for example you have to iterate over an excel table but you paste the values in a different sap cell. The value from the cell (1,1) in excel goes to the cell in (0,1) in SAP, and then the value from the excel cell (2,1) goes to the Sap cell (0,2), and it goes... My problem is that I can't find the way to iterate over sap cells. "session.findById("wnd[0]...45A-MABNR[0,1]").text" would be the first cell but the script presents an error if I put an iterator and write "...MABNR[0,iterator]").text". Do you get what I'm saying? And I can't just simply copy and paste all the column because SAP has it limits (I can copy only 8 registers, then I have to put enter and copy 7 by 7) and it depends on the amount of values I have.
Hi Lucas, your thinking is spot on. To iterate it correctly, you need to treat it like a string and concatenate them together. In your example, instead of "...MABNR[0,iterator]").text", use "...MABNR[0," & iterator & "]").text" One thing to take note when using this approach is to make sure you adjust for the VerticalScrollbar position as well if it exceeds the number of entries available on screen. When you need to scroll down in the UI, you need to scroll down using the script as well.
Hello Lucas. I just wanted to share with you what I found by accident to copy more than 8 registers. You can simply COPY and PASTE, but you have to use the SAP clipboard icon to paste the copied values of your Excel column instead of using the CTRL+V method. At least that allowed me to copy 30+ values in one shot (thats what I need normally). Idon't know what the limit of the clipboard is, but certainly was a very nice thing to find out. Hope that may help you or someone else that has the same problem when copying more than 8 items.
@@luisramirez2123 Hi Luis, thanks for sharing that, but I dont seem to find that icon, where it might be? Because if you are talking about the "Upload and portapapeles" option that's not the one I have in my trx.
Hi Joel, great stuff! I am wondering about saving pdf files from SAP though. I've tried your code up till a save window pops up, but have no idea how to automate inputing the file names from a sheet column and then selecting the save button. Could you suggest me on this?
Hello! I saw this being asked in SAP forum. One of the suggested solution is to create a shell object and use a loop to look for the name of the Window that you are trying to activate. Could be "Save As" or something else. Once it is found, activate that shell object. Then, depending use the tab key to reach the file input field and the enter key to perform the manual work. Something like the following: Set Wshell = CreateObject("WScript.Shell") Do WindowFound = Wshell.AppActivate("Save As") wscript.sleep 1000 Loop Until WindowFound if (WindowFound) Then Wshell.appActivate "Save As" Wshell.SendKeys "{TAB}" WScript.Sleep 100 Wshell.SendKeys "{TAB}" WScript.Sleep 100 Wshell.SendKeys "{TAB}" WScript.Sleep 100 Wshell.SendKeys "{TAB}" WScript.Sleep 100 Wshell.SendKeys "{TAB}" WScript.Sleep 100 Wshell.SendKeys FileName WScript.Sleep 100 Wshell.SendKeys "{ENTER}" End if *Credit to Michal Udvardy for the suggested solution in SAP forum.
Hi Yan Hirata, usually if I were to do that, I'll still save it in a separate path, then use workbook.open method to work with all the exports and copy the data into 1 single workbook. Then, you can delete those separate files if you want after the process is complete.
Hey!! Thanks a lot. I have downloaded multiple excel sheets from SAP through VBA, now I want to close those open excel sheet automatically after download through VBA, kindly help
Hi Saket Valdez, you can include the following at the end of your script: ThisWorkbook.Save Application.Quit ThisWorkbook.Saved = True This will quit the Excel Application. Currently I am not able to find a workaround to close individual Excel sheets opened by SAP.
RU-vid can be a good starting point for you to learn some of the basics for VBA. But like everything else, coding needs practise. Once you know the basics, best way to advance your skill would be to have a hands on project and learn along the way.
Hi there! What do you mean it's not data? It depends on what your condition is. For example, if your pattern is always the 2nd last row, then use the formula, then -1. Or, if it is even more complex where you need to check the value before you can decide, we can always make a conditional statement to check the cell values to get the desired "last row" in your context before running the loop.
In VBA, if you need to work specifically with values from columns 1 to 8, you can clearly define this column range in your code. Suppose your task involves handling a range within a worksheet. In this case, you can create a Range object encompassing only columns 1 to 8. Typically, you would determine the last row number to define this range. Once set, you can assign specific columns to represent certain data, like setting column 1 as "ID", column 2 as "name", and column 4 as "address". If you don't need to use a particular column, like column 3 in this instance, you can simply exclude it from assignment.
Hi Joel, do you know how can run this script on second window/tab of SAP. When I run all the excel vba code to SAP, it will always use the first window of SAP, but i want the script to run on other gui window.
Hi Hellcryer94, in the line where you declare the session variable, you can try changing the number to run on a different session: Set Session = objConn.Children(0) -> Set Session = objConn.Children(1)
Hi Jeremy, It will depend on what signs or text would be shown upon encountering the error made. Usually, the way I go about it is to read the message appearing in the status bar or the message type shown in the status bar of SAP GUI. This can be done with the following lines: session.findById("wnd[0]/sbar").text session.findById("wnd[0]/sbar").MessageType
I have a question. Suppose I want to extract data from Sap, but my company has a lot of layers or visualizations and I have to choose the correct one in order to automatize the process. Which is the code to do that?
I mean, we found out with the macro recorder that you can choose it by order number, but if any person add a layer, script would be broken... We need a way to select by the name or user of the layer
Hi Joel, when I try to run vba code, its shows a 'runtime error 13' type mismatch. I follow your code for SAP connection with excel, I don't know where its going wrong for me. How should I proceed with this?
Hi Naseeba, I'm not too sure what happened there. You can look into the portion where you declare your variable as which type of data. I may need to see your code to understand better.
@@AKASHMANDAL2386 I understand. SAP scripting may be disallowed in some organizations due to security concerns. Since that is disabled, it is meant to block any scripts or automation from running. There should not be any way around it.
Great videos, is there any chance you could do a video, where, you can create a macro in excel to run multiple reports from preferably iw49n and info from iw32 documents > completion confemation in sap, the data to then be uploaded onto an existing document and the for it to use that data on the document to create a job in iw31? Cheers,
Hey there! Unfortunately, I'm not able to do that as I am not authorised to use the 2 T-codes you mentioned in my organization. If there's a flow to it, usually that's workable, but I cant be sure as I'm not able to test it out from my end.
@@JoelTing cheers, even just how sap can transfer to excel without exporting would be great, I can figure the rest out. It just that part of the macro really I need.
I see. If you don't need it to be exported in Excel, would the data you need be accessible directly by the script itself? That would be one way to do it. The other may be just export them in Excel, read from them, pass the info to the next process, and by the end of the macro, just delete those exported files automatically.
@@JoelTing yeah accessible directly to the script, I can to it by doing 2 scripts already, like a first step and second step one after exporting, but just wanted it as one script really so no export.. if that's possible
Hi Joel, I want to create the loop for maintaining mass data for some deliveries in VL06. When I used your code, it gave me an error that x1UP is not defined. What should I do in this case?
Hello there. I'm fine. Apologies for being missing for a while. Have been quite busy as there's some major change in my career. I'll get back to upload schedule as soon as possible. As always, thanks for the support!
@@JoelTing yes i already assign the value.. btw.. can i ask for your suggestion.. where i got 3 column start at A17:C20 .. is there any way for me to do the looping where those data need to be input into the sap.. means need to put cells A17,B17,C17 then loop A18,B18,C18.. loop until C20..i really stuck..
If you are still not able to resolve, you can share with me your script so that I can have a quick look. As for your question, you can just use a for loop. For i = 17 to lastrow column1 = Range("A"&i).Value column2 = Range("B"&i).Value column3 = Range("C"&i).Value 'Your SAP script here and push in the value using variables above Next i
Hello Joel, Hope you are doing good, Need your help on my problem, I am trying to write a script, we do have a transaction code to maintain 1000+ vendor codes to exclud, but the thing is we only can copy and paste 17 codes in a shot and by using page down key we can update 1000+ codes so in macro how do we have to write coding or script which will take automatically even after 17 codes by using page down until finishes the list of 1000 codes? Appreciate your help,,,,
Hello. Sorry for the late reply. When you record the script, any button is being registered when you use the page down key? Is there a paste from clipboard button available any where in the interface?
@@JoelTing Thanks for your prompt reply bro, Yes while recording script page down key is also recorded however there is no clipboard to paste, My issue is I am facing difficulties in looping the task , how can I loop this for 17 line items in macro, If you get a chance plz share ur mail, so I can share my coding to understand more, tried to send over here but but it's not getting delivered.
If you are not able to export to PDF directly from SAP, you can try using the ExportAsFixedFormat method to export your excel into a PDF file after the spreadsheet is exported.
@@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 🙃
Use this code to close the export files without closing all of excel. Dim export1 As Object Dim export2 As Object Set export1 = GetObject(“file location\Export 1.file extension”).Application Set export2 = GetObject(“file location\Export 2.file extension”).Application export1.Workbooks(1).Close export2.Workbooks(1).Close export2.Quit Error Situations: This will close either all of the Excel files opened after the Macro is run, explicitly listed or not (running correctly); or it will close all of the excel files opened prior to running the macro, to include the file running the Macro, if one of the explicitly listed files is not open or saved to the listed location. Because of the above, when this code fails, it will not throw a debug error. If you can figure out how to improve it, so an error is handled instead of the files closing, I’d be very grateful. Otherwise, I run this code everyday without issue.
Hi, thanks for the code! Yes, you are right. Closing the exported workbook is always a hit or miss for me as well. I gave up working on that part as I felt like it's not a good use of my time to keep trying and debugging this one simple action of closing the workbook, since I automated the most time consuming part. Nevertheless, I will try to look for a workaround when I have the time.
I typically used something like this: session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\temp\" session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "export.XLSX" session.findById("wnd[1]/tbar[0]/btn[11]").press 'delay to allow file to open Dim i As Long For i = 1 To 10000 'adjust as needed Application.StatusBar = "Delay count " & i DoEvents Next i 'do stuff with the export Workbooks("export.XLSX").Close
Hello, Joel! This is exactly what I needed! I tried for 2 instances. It successfully ran for the first extraction but I keep having "Run-time error '619' The control could not be found by id " here: session.FindById("wnd[1]/tbar[0]/btn[3]").Press ---> clicking the back button So, it does not proceed to the next extraction.
Hi there! It seems like your steps got stuck after the first extraction. The button it is trying to click is not there. When it stopped, are you able to see where how it is different from the time when you run the script? You can go into debugging mode, then try running the script step by step and see why the button that it should be clicking is no longer there after the first extraction.
I tried recording again and used the shortcut key for back (F3) instead of clicking the back button. And it worked! Thank you so much! Your vids are very helpful. 👏🏻👏🏻
@@laradanicalim3954 It depends on how the no record found is displayed. You can combine if statements and on error statements to decide what to do in the event that there's no data there or if there's an absence of an element in the screen. It can get a bit complicated, but you really need to try it out to know how to handle the different events.
