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 Joel! Very very helpful video, can I ask how would I generate the texts in the status bar every after loop so I’d know what the status of my changes are? Appreciate your answer as this is my remaining problem for the tool im making 😊
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
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 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.
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 @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.
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 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.
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
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. 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 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. 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.
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 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).
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 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
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.
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.
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.
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 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
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?
@@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.
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 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.
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
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.
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.
@@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 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!
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.
TH-cam 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.
@@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.
![Automate Data Extraction with SAP GUI Scripting & Excel Macro VBA [english]](http://i.ytimg.com/vi/8nvCW-l2Jdc/mqdefault.jpg)
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!
Amazing tutorial! This is a breakthrough! Thank you so much and please keep sharing your new and better methods to extract SAP data!
Thanks for the kind word. I'll try my best to share more.
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
Thanks for your support! Appreciate it a bunch! I will work on more videos during my free time.
Thanks a lot for providing this resources to help facilitate our daily task at work!
Gr8 stuff. It actually helped me to do the opposite with Loop (For i = ... Next i) and import data from Excel to SAP in MAT1.
Thank you
Glad that it helped in some ways 😊
Thanks Joel. Keep this coming :)
Thanks for your kind words Abhishek!
Hi Joel! Very very helpful video, can I ask how would I generate the texts in the status bar every after loop so I’d know what the status of my changes are? Appreciate your answer as this is my remaining problem for the tool im making 😊
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 🙂
Great Vídeo Joe!
Please, prepare a video using LB10 transaction to stract report from it.
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.
Mec je t'aime, t'es un génie
Muchas gracias Joel!, muy muy util para mi
Thanks for watching!
Very nice stuff, man!
this is very good stuff, thanks for sharing.
Hi Joel, thanks for this video. Do you have a code to export data from SAP to same VBA file but different tabs? Would really appreciate your comment
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.
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 @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.
Hi Joel do you have tutorial to automate SAP GUI data to Google Sheets
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.
Superb information
Hi Harendrasinh, appreciate the kind words 😀 I hope you find it useful.
@@JoelTing Yes it's usefull 😊
Thank you this worked👌.... how can this be done for multiple report from different t-code
You can string the different reports in a single process, run 1 report after another. You should be able to run it without issue.
Hi Joel. Amazing videos, you helped me a lot. Is possible to have a tutorial for saving pdf files from an adobe crobat window open in sap?
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.
I Love your video
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
Hello. What problem are you facing? You should be able to do this by just combining the 2 scripts together.
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.
Thanks @@JoelTing I'm gonna try that!
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.
Thank You, Joel!
Hi Rob, hope it helps. Thanks for watching!
Very informative
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 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?
thanks for video! useful!
Thanks Bro. It was very helpful
Glad it helped!
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?
Hey there. Did you use "/n" in front of the Tcodes that you are running in the command bar? Eg. "/nKE24"
Thank you So much!
thank you so much Joel
You're welcome. Hope it helps!
Genius 🎉. New subscriber.
Thanks for subbing!
Joel, what if no data is found from the i 3 per example, how do I make it skip to the next one without needing to re-run the script?
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.
@@JoelTing thank you very much, you should make a error handling video 😁😄
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
Nice joel. Thanks.
Thank you too!
Hello,
I was trying to loop the data but getting an error message (followed your steps still count not get loop)
Hi Jo , You are rocking.... learned lot awesome.
How to loop particular Sap steps in vba? Would be good to know that please
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).
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.
Hello Joel we stuck in this VBA please help on this. in this code line we face the issue this line code is red colour mark
Which code you are referring to?
Hi Joel ,
How would you handle an exception management in this case , in the event say parameter define has typo in the country field.
Thanks
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
@@JoelTing Thank you for taking time to respond !
Thank you, Joel! Is it possible to save all results in 1 file, instead of the separate paths?
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.
How to set date from - to in excel and what's the code i have to add ?
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.
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.
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 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
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?
Hey Mohammad Ayan, should that be xlUp instead of x1UP?
@@JoelTing you're amazing!!!
Can I automate SAP GUI without RZ11 authorization?
Hi there. Unfortunately no. If it is disabled, you will not be able to connect to a SAP session with VBA.
@@JoelTing actually its tough to get authorization
@@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.
hi, i'm new to sap vba. i have a question, what if the last row is not a data? how can we specify the last row on some condition? thanks
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.
For example I have a data value from column 1-10 but I only want to use the value from column 1-8. How should I write to that condition?@@JoelTing
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 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.
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
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 Christian Rees, is there a way to identify whether a layer have been added?
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.
Bro, I need code for selecting specific layout before extracting the report. Kindly help
Hi Suresh Kumar, could you select the layout in the recording itself when you are recording your steps?
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.
@@maheshchagi6349 my email is joelting92@gmail.com. if possible, try sending me a recording of what you are trying to achieve as well
@@JoelTing Hello Brother,
I have shared SAP script recording via mail, plz do help
@@JoelTing Hi Joel,
Hope you are doing well,
Thanks you so much for your help, with the help of you I have completed my task,
Error message was " wrong number of arguments or invalid property assignment"
Could you please help me to get it fixed this
Do you mind to share your script?
@@JoelTing thanks for ur prompt reply,
I got it n working fine, once again thanks a lot for your video on SAP macro looping, waiting for many more
I keep getting error for script out of range for lastrow = .... Any idea?
Usually it happens when you try to reference a variable that does not exist in a code. Did you declare lastrow? Have you assign it to a value?
@@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
@@JoelTing Thanks a lot..it works! Such a genius... Thanks ya
DEAR JOEL I AM WAITING YOUR NEW VIDEO,,,AND HOW ARE YOU?
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!
If I click on button I want to download table data in pdf format. With json data as well as odata
Hi Sohel, usually are you able to download in pdf or json format manually?
@@JoelTing yes usually, I can download table data in excel format (.xlsx) formatbut I want to download in pdf format
@@JoelTing 😊 thank you for responding 🙏
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 I will try if do you have any blog related this please, share with me thanks 😊
Coding is a challenge to me. I'm bad at it. Where n how can I learn it?
TH-cam 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.
lastRow variable won't define for me. Keeps bombing out.
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 🙃
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.
@@JoelTing good stuff thanks for the information.
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. 👏🏻👏🏻
@@JoelTing Hmmm how about handling 'No record found'. How do you keep the script running? I'm sorry, I'm new to SAP GUI scripting.
@@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.
@@JoelTing Got this! After several tests, finally it worked! Thank you!