Hi Csongor Varga, I am very grateful to you for being very supportive of the subject and for helping me to improve the productivity of various applications that I have automated with excel. a hug from colombia and blessings.
@@work-mn8lu I found the answer! If you look carefully at the SAP window in the video at 0:01 you will see a small grey triangle to the left of DCG(1) 210 So in my SAP system everything to the right of this triangle was collapsed and the triangle was to the right bottom of the window. Click on it and everything should appear as it is in the video.
Thank you for this great video. I´m suscribed! One question, I am running a Script to Copy & Paste data from a SAP transaction to a Excel sheet, everything runs good but when i run the Script it is not pasting the data to Excel. Do you think that is because SAP is not allowing users to import data from SAP by just Copying and pasting? or should i add some special Command in the Script notepad that allows me to "Paste" the data to Excel? Thank yoy for any help you can give me
I did a trick and tips video where I show some examples you can copy paste. You should be able to copy data from SAP to Excel like objSheet.Cells(x,y).Value = objSess.findById("...").Text. Of course the Text property may change based on what SAP control that is. And if you reverse the expression you can write a cell value into SAP. No need to use the clipboard for that.
@@csongorvarga Thank you so much! i will look for your other video and give a try, i already made a script where i can export data from Excel to SAP, and worked perfectly, that´s why it was so weird to me that i couldn´t do the same but in reverse using the same logic :)
these are the commands i added to the recorded script (note pad), i want to paste the SAP text in Column 2 from the Excel file, maybe i am declaring the cells incorrect: Dim objExcel Dim objSheet, intRow, i Set objExcel = GetObject(,"Excel.Application") Set objSheet = objExcel.ActiveWorkbook.ActiveSheet For i = 2 to objSheet.UsedRange.Rows.Count cOL1 = Trim(CStr(objSheet.Cells(i, 1).Value))
Hello Csongor! Again thank you for your videos, hope you can read me and help me with a doubt, im trying to print PDF an invoice but as it opens adobe the Script stop workings, any idea of how can i solve this?
Scripting stops whenever SAP is launching any screen which is not SAP, like Windows Open, Save, Print dialogs. I never found a way to program these screens. But I think it should be possible to get the handle of the PDF window and send key commands to it (like print, save, etc.). If you google "VBA catch window handle", or "VBA send key to window" or similar phrases you will find examples. But I never spent enough time to properly research this.
Cool video! Thank you. But do you know how I can find the "sid and client to connect to ": ' Set the sid and client to connect to W_System = "DCG210"??
The SID is just the name of the system you're connecting to. The top left of your SAP window should have this information. Alternatively, the system name should also be near the bottom right, close to the script icon (assuming you haven't used the drop-down to choose something else--if so, just click on the drop-down to see the info). For example, one of mine says: E6P(2)/100. The (2) just means that's the second SAP window from that system that I've opened. You can ignore that and the forward slash.
I can't use the connection, I then declare next variables: dim connection, and dim application. When only paste all code of scripts, my application run perfect. But have advertised on the excel: error "esperando que otra aplicación complete una acción ole" en excel 2016. You have solution.
You mentioned that you used to use it to extract multiple reports at once, i would like to do a similar thing and extract multiple reports (different data points) from the same sap transactions, how did you make it loop and chose to run it for multiple reports ? thanks in advance.
Hi! Thank you for this tutorial! Quick question: I'm importing a column that contains both numbers and text infos from SAP. The text cells are not being recognized as text, it fails when I try to use the data in a Excel function. I've been trying to find a solution for a long time now.. Do you guys have some tips to fix it? Thank you
Thank-you for the video. Very helpful, I like the %pc tip. I noticed some times you remove SAP recorded code such as ".caretPosition = 3" and sometimes you do not. You seem to always remove the "setfocus" code. Can you explain?
Thanks. Actually no particular reason. Setfocus just moves the focus to a control, in most cases that does not do anything. You can change a value of a field without setting it to the active control, so there is no added benefit. And for me it makes the code more compact and easier to understand.
When you are logged in the SAP GUI, check the status bar of the gui, where it shows your user, or the transaction. If you click there you can see the system and client as well.
Thanks for the reply. Indeed nothing will be exported but it gives me error in my excel macro. Is there any coding to skip the export coding if no data is generated?
Hi Csonger, what if my report varies month to month, and when I use the t code, i always have to update the posting period to show the curren month. Is there any way to recoed this update of the posting period in the macro?
You can calculate the current month in Excel and set the value before you execute the report. But I think there is even dynamic date rule for current month.
Hi Csonger, Love the Videos! I have a quick question. Im looking to get tables/reports for five different tables out of SAP using this process. What would you recommend to accomplish this efficiently - Add more sheets and paste the code in each sheet and modify the gui recording and folder paths? Thx
Hi, I cannot get the final file (XLS file coming por SAP) I don't know why, at the end of the run, I have to write the name and the path to download the file. I want to go across the record and export automatically. Someone can help me please?
Koszonom :) Lehetseges ezt a makrot ugy atirni, hogy gombnyomasra lefuttasson a sap-ban egy folyamatot, aminek a vegen az adatokat kimenti egy mar letezo excel file-ba? LX01 funkciot hasznalom, amivel a raktarban levo ures poziciokat tudom "elohuzni". Esetleg tudunk a tovabbiakban e-mail formalyaban komunikalni?
@@kareszka1984 Igen lehet. Mind a két irányban van kommunkáció. Ez a GUI scripting egy olyan megoldás amihez Visual Basic programozási ismeretkre van szükség. Ha ez nincs meg akkor nem tudom ajánlani. Az email címem kereszt név pont vezeték név kukac gmail pont com.
Irtam mailt. Azota probalgattam es a legnagyobb fennakadas ott van, mikor ranyomok az "extract data" gombra kiirja a "No active session to system DCG210, or scripting is not enabled" pedig fut a SAP...viszont fura, h 1x vegigcsinalta a folyamatot ugy, h kidobta ezt a hibat...
Is it possibble to recur the session.findByID.sendVKey 0 while the unknown repetition of pop up message is gone and proceed with the next session? If yes. Can you share how?
This is a good question. I am not sure. I noticed that all pop-up windows have wnd[1] in the key, while the main window is always wnd[0]. So maybe checking the active screen element and checking if the key includes wnd[1] will tell if there is a pop-up on the screen.
I am facing an issue with on of my report. I am running a report which export data to an excel sheet. The number of data i was expection was around more than 26,000. But i am getting close to 25,000 data in excel sheet. So i want to know is there any limitation for number of data for excel sheet? Could you please help me out here because its urgent.
Hi, I am new to SAP and scripting. I was a little confused of what happened in this video. I am attempting to write a script that takes thousands of product cost reports and store them into a folder, all every report is individually saved as an excel sheet. Would a program such as this help me with that?
Hi Csongor. Thank you for your well-explained video. I have a question. Why don't we extract the report to Excel directly using the spreadsheet instead of text? Is it easier that we do not have to convert from csv?
Thanks, I am glad you liked the video. Whenever I was using the spreadsheet output in the past I got it in a format that I could not directly load to Excel without doing some manual conversion. Maybe Excel or SAP GUI itself got better in time, but I am just sticking to old habits I guess.
Thank you again and again for this great video.. i come back to you sir to ask you if there is a possibility to start a script and continue the same script in another window. I mean, when i will start my script, i want to open a new window in sap and continue my transactions in this window ( and the script is always recording my steps..) Thank you so much sir.
Hi, Can you tell me how to extract the log for the opening and closing of the accounting period? What is the T.code for extraction of a log from SAP? Thanks!
Excellent video, I have a question? If we want to change date on monthly basis, how can we chance it in macro? So if wante to pull a report and creat macro for it, next month I want to change date. How can I do this? Please reply asap
Hi Csongor, what do I need to do to run a t-code for reports several times and posting tables from that same t-cde in excel either one under the other or at least in separate tabs?
You can call a macro over and over, that is not a big issue. Switching tabs in Excel can also be done programatically. That is not an issue at all. Just need to write some code for it which is probably a bit more than what I can explain here.
extracting data from SAP (via Winshuttle) is there an option to extract data into a .CSV comma delimited Excel file? (not a .txt file) is it updating the script or is there a simple drop down type menu that allows you to select how you want your data extracted? I need a client using SAP and Winshuttle to provide me with client/interaction data in .CSV comma delimited format so we can assist them in integration to our software and system
This is going to make my Mondays far simpler. How can I use this to pull the last work week of data? I normally have to put in a start and finish date.
You have to code it in VBA. From the current day you can always calculate Monday of last week. If you are running the report on Mondays, last Monday is actually Now-7, and last Friday is Now-3.
I’m trying to download transaction MCBZ. For this particular transaction I can only export it as spreadsheet and not .txt. How can I save it once downloaded in excel? How can I connect both spreadsheets? (The one with the script and the one recently downloded) thanks!!
Hi Csongor. Could you please upload a video while you export a report and saved as an excel.file.and not text? I can' manage to adapt your file to my gl balance export.
@csongor Varga , why in the saving file or when the save file diaglog appears the script is cut. do we have solution on that? in using SAP 7.20 inside client CITRIX
File save dialog is a Windows dialog, and cannot be scripted. There is no way around it. In the 7.40 GUI there is an option in the settings to use SAP dialogs only.
I had the same Problem: You have to Change the .TextFilePlatform Code: .TextFilePlatform = 850 Dos-File Format 65001 UTF8 File Format will be in the right format 1252 Windows(Ansi)-Files will be shown in the right format. It works for the German ÄÜÖ etc. Hope you still need it!
Hello Csongor, how would you change the script to filter for specific different Object _D's? If you wanted the script to run specific Object Id's from a list in Excel?
In most of my other videos I show how you can pass data from Excel to SAP and even back. So you create a recording in which you run SE16, you specify the object IDs. Record the steps as you paste in the list of Object IDs from the clipboard. In Excel, you can use a VBA code like: Range("A1:A10").Select. This will put the entries between A1 and A10 into the clipboard that you can paste into Excel.
Your videos are of a great help! I just couldn't find the answer on how to select the dates range of the SAP report from Excel's cell. They are firmed in the script, while I would like them to be different every day, without changing them in the code. Does one of your videos explain the solution to that?
Yes, probably this is the only video in which I use variants. In most of my other video I show now to record changing values in SAP and pass those values from Excel.
Hi Csongor Varga, I am trying to record tcode zv401 however, the scripting is not recording the exporting the data. could you please help me with it? Also, all the message pop-up in recorded. Thank you in advance.
Yes, all the message pop-ups will be recorded. You can't so nothing about that. What do you mean not recording the exporting of data? Is your transaction using a Save as dialog window? That will never be recorded.
Hi everyone! I know that you cannot download SAP data straight to the ".xlsx" format because of the windows window that SAP GUI does not detect. But, does anyone have any trick to do that? I mean, maybe you can run a macro in excel that does this part of the script?? I have tried some different stuff, and still with 0 positive results...
Yes of course. There is no limit. Create separate recordings, turn them to separate functions in Excel and call these functions one after the other when the button is pressed on the first worksheet.
@@csongorvarga My recordings are done... 7 different transactions to *.XLSX then linked as external data to MS Access. I found your video, trying to overcome the "Handling a Save as dialog box" focus dilemma. I currently put the path\filname.xlsx on the clipboard, then when the DB opens, they click the title bar, paste, and click save. But they want to run them all at once and walk away - which I agree, would be ideal. With your solution, I have one XL file and one button, while retaining my links to MS Access.
This is amazing, but is it possible to do it when you use export?? When you export to spreedsheet appears a Windows "Save As" and here I don't know how to change the path and file name...😥
Hello Varga,Great video. It would help a lot.I did try run your code using SAP code FB03 and appear the error message after run the "StartExtract()" - "No Active session to system DCG210, or scripting is not anable" How could I check if DCG210 is the correct one for my SAP?
You should know in which SAP system you want to run the script. When you log into SAP you select a system and a client. It you click on the status bar where you see the transaction code or user, you will see the SID and client as well.
I'm getting a compile error "user defined not defined". objGui As GuiApplication is highlighted in the script. I use SAP GUI "EGP 301" so not sure if that's what causing it? however, I did change name from DCG210 to EGP 301" but not change.
Did you check that under Tools / Preferences you have the SAP GUI Scripting added? Also, don't put spaces between the SID and client, set it as "EGP301".
yes, I just checked the SAP GUI scripting options under tools/preferences but it still didn't fix it and I also already had "EGP301" without spaces. The error is happening in below line for some reason. Public objGui As GuiApplication
Hi Csongor Thank you for the video! I have one quick doubt 1) while you exporting data from SAP, you are using "%PC" Tcode is this SAP standard Tcode ?. while I'm using in my system it's not working 2) How to record "SAP GUI Security popup" window "Allow" button while exporting VBAK table information it's asking. Kindly do this needful support to better understand :) Thanks, Hemanth M.
%PC is a command which does the same as export to local file, which is usually available in the menu in an SE16 like transaction. So it is a standard GUI shortcut. I don't have SAP Logon installed on my laptop right now, but you can do two things with the security popup: 1) Tick Remember my decision and it will not be asked again for the same folder. 2) You can go into the SAP Logon settings, and in the Security group you can set if certain folders should be allowed or denies for export.
Dear Csongor Varga your videos are very helpful. Thanks for helping the newbies in GUI. I have minor issue. Showing error in "objSess.EndTransaction" when we have not logged in the SAP. I would like to put msgbox instead of error pop up. Please help.
Thanks, by the way, I dont have SAP on my computer, so the Reference doent appear, I guess that only in a computer where has SAP I could find it, am i Correct?
Thanks for sharing these tutorials. Helped me a lot. I faced one problem. When extracting the data from SAP from large tables I get a msg "waiting for another application to complete an OLE action" and SAP stops to execute unless I click OK. I have googled and found a solution how to turnoff this option in the Excel, but when I turn it off then I cannot open macro files. Have you faced this problem?
Sorry, I have never experienced this. I did a quick google and found this Stackoverflow page: stackoverflow.com/questions/22379815/microsoft-excel-is-waiting-for-another-application-to-complete-an-ole-action. There are few recommendations there. Give them a try.
Yes the new excel limit is 1,048,576 lines. Maybe you can split the table by setting up a selection criteria, export in two pieces and import them into separate sheets. I don't have a better solution right now. Unless you import it to somewhere else than Excel. Maybe Access.
Hi all, I hope you can help me. whe run the script and in the moment is assigned the "session" Excell shows a message "The enumerator of the collection cannot find en element with the specified index" and I review in the "watches" and the connection variable don't have sessions and I have open session un SAP. do you have any idea todo fix it? thank you
I think it will be very difficult to help. So you click on the button to start the script. Does Excel connect to SAP. Does the GUI window comes on top of the screen? Does it start the script at all?
I am running your script and it shows "no active session to system" un this case for me is "ACD" I activated the scripting in SAP Options on Customize Local Layout And the next message "Not connected to Client" But client is working.
Rogelio Lugo Did you update the SID and client in the code? Open the VB editor and right at the end of Module1 there is a variable called W_System. Change that to whatever SID and client you use.
Thanks for sharing. Just a quick question, I get an error message when I try to download the spreadsheet. It says "Invalid GUI input data: FOCUS DATA (Message no. 00123)". Any idea? Thanks.
is it possible to copy text from popup windows in R/3 into the cell of an Excel? recording of script in r/3 does not save the movement highlighting the text and copying
Yes you can. The recording will save as you press the ok button on the pop-up window. You can use the debugger and go to the parent object and explore all the elements in the popup.
Thank you for your video! I’m trying to use the macro but I get the error “no active session to system PRD900, or scripting is not enabled”. Well, scripting is enabled and I found the SID in the login window..so, the only mistake could be the client..where can I find it?
Riccardo Caruso I cannot fix it! I tried every number I found in the GUI for the client but the macro does not find the active session. If you’ll find the solution please share with me! I’ll do the same!
Hi Csongor That's brilliant. For some reason i am failing to have excel connected to SAP still. it gives an error on the line If objGui Is Nothing Then Set SapGuiAuto = GetObject("SAPGUI") ----------->Highlighted in Yellow Do you think i am putting the system wrongly? At the bottom right it says PE3 (1) 400 and the client is 400. so I have put PE3400 but still giving me this error. I have also checked and I have added the GUI Scripting in Excel as you instructed. And my SAP GUI Scripting is installed and enabled as well. I am struggling here to be honest and would love your help. thank you
I am confused. PE3400 is the correct setting. If you are getting an error in that line my first guess would have been that you did not add the GUI Scripting in the references, but you did. What does the error message says?
@@csongorvarga I am also trying to automate the session log on so I can schedule the guiscripts & extraction. Have you managed to automate the log on element?
Awesome video, it helped me a lot to understand how to export data. Do you know which function I can use to export to .xlsx spreadsheet instead of .txt?
You can set dynamic dates in variants as well. It should not be a problem. Otherwise you can record setting the dates in the script and add the date from Excel dynamically.
Hello Csongor, when stepping into your code, should SAP graphic show as running when you W_Ret = Attach_Session under the RunGuiScript sub routine or at W_System = "SIDClient" in the StartExtract sub routine? My SAP GUI code works fine, but i don't believe my connection works as it should. It connects at Set W_Sess = W_conn.Children(it + 0) under Attach_Session sub routine and i error out after the 1st line .maximize on My recorded GUI code. GUI codes works independently, but I "myerr" in excel.
Hi, Wanted to know, is there any way where we can extract the specific row or column in the SAP GUI window. Like in st06: how to extract only CPU idle % or memory% to excel.
very interesting video. I could successfully generate the code and run the script. Im running it from an Access file. Unfortunately what I couldn't do is to replace the part of the code where the inputs for SAP transaction are (dates, for instance) with a Variable that can take info from a particular field from an access table. My idea is to put fields in a form where I can complete the dates and when I run the macro, it downloads de SAP information for that dates (what it actually should do is to paste that date in an table and the code should be able to take the information from there). Do you know how to do this?
What I mean is that I recorded a macro, suppose a very simple one --> logging into SAP, entering a transaction and putting a date, then exporting it to excel and I put that code in an access. I created a form where I put a button that run that macro. Everything fine. The problem is that the date is fixed in the code. I want to replace that date for a variable (suppose "Date1") and defining that variable to take the date stored in a field of a table from the access (So I Can complete it before running the macro)
I have done the same in almost all of my videos. Even in this one I have a global variable for the file name and I am adding it to the script. You can do the same, and use the Format function to make sure your date is formatted in a way SAP expects it.
Thank you for the video! I am almost done with the code but I am getting error from the last rows: Sub StartExtract() ' Set the sid and client to connect to W_System = "DCG210"
I am pretty sure your system is DCG210. So you need to update that. The SID and the client ID is on the lower right of the GUI. Scripting should also be enabled in your SAP system.
Hello Niko Nalli Please can i ask you a question. did you run your code with the W_System = "DCG210" or you changed the W_System?? because i have the same problem you already had.Thank you.
Hello Csongor, thanks for the video, I have one query, i'm trying to extract the data in MHT format but while recording the script this doesn't gets captured, could you help me on this??? this way I'm trying to download the extract (List>>Export>>Spreadsheet [Shift+F4]). The reason I'm extracting in MHT format is the amount and data comes in proper format but in others its jumbled. please help!
Csongor, I'm using the tcode: S_ALR_87011964 to get the company codes details, instead of download the file through Local File method, I'm using Spreadsheet method, where i get the HTML format extract. When recording this only the downloading part is not getting captured in the script. Is there a way to solve this ? or I need to use only local file method for extracting the file.
I am sorry, I could not make this transaction to work in the systems I have access to. I don't know how it looks like and why it is not recording the steps correctly.
Csongor, I somehow made it to work with a different format... Thank you so much for all your videos which helped me a lot... Please share me your personal mail ID so that if any helped required I can write you a mail...
Hi ! I am getting this notification continuously this notification continuously that "Microsoft Excel is waiting for 'Display Project Actual Cost Line Items' to complete an OLE action". Can someone help me please.
@@csongorvarga yes I am trying to download an excel report from SAP using GUI scripting and your excel macro file but unless I click on 'OK' in this pop up message my file is not downloaded. Please check I have responded to your email with screenshots.
@@csongorvarga I just checked in SAP. 'Display Project Actual Cost Line Items' is the same notification I get in SAP while exporting an excel report after the TCode is run successfully.
@@rodrigoaguado153 I don't think so. Google Sheets has its own macro language but probably the biggest reason it would not work, because the Google Sheet is not allowed to go outside Google Sheets. So that macro language will not be able to interact with the SAP GUI windows API running on your computer.
Thank you so much for the video. I am using Excel 2016 and I am unable to run the macro I have "Missing: SAP GUI Scripting API" in the excel References. is there any other way that we can exclude and run the code
This 18 minute video saved an hour of my working time. Thank you so much for your kindness in sharing a valuable skill and knowledge.
Hi
Csongor Varga, I am very grateful to you for being very supportive of the subject and for helping me to improve the productivity of various applications that I have automated with excel.
a hug from colombia and blessings.
MI amigo, quizás usted no entienda español y yo muy poco de Ingles, de verdad que fue de gran ayuda en este día. Saludos.
Anxious to start using this to download a lot of various data on a weekly and monthly basis. Thank you for your informative videos and the template!
Very useful video...gonna try this...will help me save time on downloading data and preparing reports on a weekly basis. thanks man
Thank you for this and the first video, it helped me to create cool application.
You make this so easy to follow. Thanks
Thank you Csongor. i was able to adjust you process to extract project actual costs CJI3
Good job! Thanks for letting me know.
Hello Julian, could you please share your code?
@Julian Can you please share how you did that? It will be really helpful
Thank you very much for this video and material! VERY HELPFUL! Good job! Keep up the great work! :)
worked wonderful. Thank you so much for your time and great attitude of sharing the knowledge. :)
Thanks for the series and the effort, though what is "DCG210"? How can I find this value for my specific system?
I have exactly this problem, he didnt explain what is DCG210
@@work-mn8lu I found the answer! If you look carefully at the SAP window in the video at 0:01 you will see a small grey triangle to the left of DCG(1) 210 So in my SAP system everything to the right of this triangle was collapsed and the triangle was to the right bottom of the window. Click on it and everything should appear as it is in the video.
Thank you for this great video. I´m suscribed!
One question, I am running a Script to Copy & Paste data from a SAP transaction to a Excel sheet, everything runs good but when i run the Script it is not pasting the data to Excel. Do you think that is because SAP is not allowing users to import data from SAP by just Copying and pasting? or should i add some special Command in the Script notepad that allows me to "Paste" the data to Excel?
Thank yoy for any help you can give me
I did a trick and tips video where I show some examples you can copy paste. You should be able to copy data from SAP to Excel like objSheet.Cells(x,y).Value = objSess.findById("...").Text. Of course the Text property may change based on what SAP control that is. And if you reverse the expression you can write a cell value into SAP. No need to use the clipboard for that.
@@csongorvarga Thank you so much! i will look for your other video and give a try, i already made a script where i can export data from Excel to SAP, and worked perfectly, that´s why it was so weird to me that i couldn´t do the same but in reverse using the same logic :)
these are the commands i added to the recorded script (note pad), i want to paste the SAP text in Column 2 from the Excel file, maybe i am declaring the cells incorrect:
Dim objExcel
Dim objSheet, intRow, i
Set objExcel = GetObject(,"Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet
For i = 2 to objSheet.UsedRange.Rows.Count
cOL1 = Trim(CStr(objSheet.Cells(i, 1).Value))
Hi, thank you for the video. How do I then format the extracted data as a table? Can this also be included in the script? Many thanks.
Hello, i need little help to pull the data of current date
Hello thanks for the video. love it . But what happens when you have many variant and you need to select one in particular?
Hello Csongor! Again thank you for your videos, hope you can read me and help me with a doubt, im trying to print PDF an invoice but as it opens adobe the Script stop workings, any idea of how can i solve this?
Scripting stops whenever SAP is launching any screen which is not SAP, like Windows Open, Save, Print dialogs. I never found a way to program these screens. But I think it should be possible to get the handle of the PDF window and send key commands to it (like print, save, etc.). If you google "VBA catch window handle", or "VBA send key to window" or similar phrases you will find examples. But I never spent enough time to properly research this.
@@csongorvarga thanks for your response, i will look for it, thanks again!
Very good indeed
Thanks, great video and explanation!!
Cool video! Thank you.
But do you know how I can find the "sid and client to connect to
":
' Set the sid and client to connect to
W_System = "DCG210"??
The SID is just the name of the system you're connecting to. The top left of your SAP window should have this information. Alternatively, the system name should also be near the bottom right, close to the script icon (assuming you haven't used the drop-down to choose something else--if so, just click on the drop-down to see the info).
For example, one of mine says: E6P(2)/100. The (2) just means that's the second SAP window from that system that I've opened. You can ignore that and the forward slash.
Thank you! The macro works well 😊
Superp !!! very thanks Csongor
I can't use the connection, I then declare next variables: dim connection, and dim application. When only paste all code of scripts, my application run perfect. But have advertised on the excel: error "esperando que otra aplicación complete una acción ole" en excel 2016. You have solution.
This is amazing!
Hello Csongor, lehetseges riportot kesziteni SAP GUI requsition and teljesitett order and sent vegosszegekrol honapokra lebontva?
Valahogy biztos, de en nem vagyok FIs
You mentioned that you used to use it to extract multiple reports at once, i would like to do a similar thing and extract multiple reports (different data points) from the same sap transactions, how did you make it loop and chose to run it for multiple reports ? thanks in advance.
Hi! Thank you for this tutorial!
Quick question: I'm importing a column that contains both numbers and text infos from SAP. The text cells are not being recognized as text, it fails when I try to use the data in a Excel function. I've been trying to find a solution for a long time now..
Do you guys have some tips to fix it?
Thank you
I am not sure I understand. I assume this is an Excel issue, right?
@@csongorvarga Yes! But I've been trying to fix it by VBA code, without success.. anyway, thank you so much for the video and for the reply!
Thank-you for the video. Very helpful, I like the %pc tip. I noticed some times you remove SAP recorded code such as ".caretPosition = 3" and sometimes you do not. You seem to always remove the "setfocus" code. Can you explain?
Thanks. Actually no particular reason. Setfocus just moves the focus to a control, in most cases that does not do anything. You can change a value of a field without setting it to the active control, so there is no added benefit. And for me it makes the code more compact and easier to understand.
Ayuda, necesito copiar una columna de datos que esta en sap y pegarlo en excel
great!!! thank you very much.
Thank you so much Csongor
Hi Csongor, many thanks for your tutorial, How can I know client name ? minutes 7:30
When you are logged in the SAP GUI, check the status bar of the gui, where it shows your user, or the transaction. If you click there you can see the system and client as well.
I have 1lakh records to download..its taking almost one hour time..Can you please give me an idea to download quickly.
Hi Csongor, while saving the file through Local file option, unable to open excel sheet. it says file is corrupted. I am using SAP 7.4.
Please hepl me how to resolve this issue.
Hi Csongor
How do i skip the exporting to excel if there is no data generated?
The code loops through the entries in the view, so if there is nothing, nothing will get exported.
Thanks for the reply. Indeed nothing will be exported but it gives me error in my excel macro. Is there any coding to skip the export coding if no data is generated?
@@janewong8808 If you put a line "On Error Resume Next" before the error happens, Excel will just ignore the error and move on.
Hi Csonger, what if my report varies month to month, and when I use the t code, i always have to update the posting period to show the curren month. Is there any way to recoed this update of the posting period in the macro?
You can calculate the current month in Excel and set the value before you execute the report. But I think there is even dynamic date rule for current month.
How to set date from - to in excel and what's the code i have to add ?
What do you mean by date from - to?
In my SAP I have to regularly use the date for example from 07/24/2023 - To 07/23/2023 to pull the report. So how do I do this in vba
Hi Csonger, Love the Videos! I have a quick question. Im looking to get tables/reports for five different tables out of SAP using this process. What would you recommend to accomplish this efficiently - Add more sheets and paste the code in each sheet and modify the gui recording and folder paths? Thx
Hi, I cannot get the final file (XLS file coming por SAP) I don't know why, at the end of the run, I have to write the name and the path to download the file. I want to go across the record and export automatically. Someone can help me please?
Szia Csongor
Lehet par kerdesem ezzel a makroval kapcsolatban?
Szia, persze segitek amiben tudok.
Koszonom :)
Lehetseges ezt a makrot ugy atirni, hogy gombnyomasra lefuttasson a sap-ban egy folyamatot, aminek a vegen az adatokat kimenti egy mar letezo excel file-ba?
LX01 funkciot hasznalom, amivel a raktarban levo ures poziciokat tudom "elohuzni".
Esetleg tudunk a tovabbiakban e-mail formalyaban komunikalni?
@@kareszka1984 Igen lehet. Mind a két irányban van kommunkáció. Ez a GUI scripting egy olyan megoldás amihez Visual Basic programozási ismeretkre van szükség. Ha ez nincs meg akkor nem tudom ajánlani. Az email címem kereszt név pont vezeték név kukac gmail pont com.
Irtam mailt.
Azota probalgattam es a legnagyobb fennakadas ott van, mikor ranyomok az "extract data" gombra kiirja a "No active session to system DCG210, or scripting is not enabled" pedig fut a SAP...viszont fura, h 1x vegigcsinalta a folyamatot ugy, h kidobta ezt a hibat...
Hello, I need to extract data from a long tex field where session.findbyID does not report any text or value. How can I do that? Please help.
Is it possibble to recur the session.findByID.sendVKey 0 while the unknown repetition of pop up message is gone and proceed with the next session? If yes. Can you share how?
This is a good question. I am not sure. I noticed that all pop-up windows have wnd[1] in the key, while the main window is always wnd[0]. So maybe checking the active screen element and checking if the key includes wnd[1] will tell if there is a pop-up on the screen.
Awesome video!!!! One question, what reference(s) should I make available for this to work?
You only need the "SAP GUI Scripting API" which is sapfewse.ocx.
Thank you. I watched your first video and found it :)
I am facing an issue with on of my report. I am running a report which export data to an excel sheet. The number of data i was expection was around more than 26,000. But i am getting close to 25,000 data in excel sheet. So i want to know is there any limitation for number of data for excel sheet? Could you please help me out here because its urgent.
I am not aware of anything like that. Did you set the max number of records in se16 correcly?
Can you please just tell me how to check if i have missed it in the video? How to check that?
Great, thank you for sharing!!!!
Hi, I am new to SAP and scripting. I was a little confused of what happened in this video. I am attempting to write a script that takes thousands of product cost reports and store them into a folder, all every report is individually saved as an excel sheet. Would a program such as this help me with that?
Yes, you can pretty much do anything with scripting and VBA code. But you probably need to do your own coding in Excel.
Hi It is not working for SAP logon 760
Hi Csongor. Thank you for your well-explained video. I have a question. Why don't we extract the report to Excel directly using the spreadsheet instead of text? Is it easier that we do not have to convert from csv?
Thanks, I am glad you liked the video. Whenever I was using the spreadsheet output in the past I got it in a format that I could not directly load to Excel without doing some manual conversion. Maybe Excel or SAP GUI itself got better in time, but I am just sticking to old habits I guess.
Oh I see. Thank you for your reply :)
Thank you again and again for this great video.. i come back to you sir to ask you if there is a possibility to start a script and continue the same script in another window. I mean, when i will start my script, i want to open a new window in sap and continue my transactions in this window ( and the script is always recording my steps..) Thank you so much sir.
Hi,
Can you tell me how to extract the log for the opening and closing of the accounting period? What is the T.code for extraction of a log from SAP?
Thanks!
can we run multiple t-codes in the same file by adding new worksheet to after extract?
do you resolve this ? i have same question . thanks
Excellent video, I have a question? If we want to change date on monthly basis, how can we chance it in macro? So if wante to pull a report and creat macro for it, next month I want to change date. How can I do this? Please reply asap
You can use a variant, or pass it from Excel and just use and Excel function to calculate the date from the current system date.
@@csongorvarga thanks for quick response, do you have any video on this so that I can see, please
How to pass date from excel to SAP? Any video please forward me?
Hi Csongor, what do I need to do to run a t-code for reports several times and posting tables from that same t-cde in excel either one under the other or at least in separate tabs?
You can call a macro over and over, that is not a big issue. Switching tabs in Excel can also be done programatically. That is not an issue at all. Just need to write some code for it which is probably a bit more than what I can explain here.
%pc doesn't work with me is there an alternative ?
There is save to local file or similar in the menu as well.
extracting data from SAP (via Winshuttle) is there an option to extract data into a .CSV comma delimited Excel file? (not a .txt file) is it updating the script or is there a simple drop down type menu that allows you to select how you want your data extracted? I need a client using SAP and Winshuttle to provide me with client/interaction data in .CSV comma delimited format so we can assist them in integration to our software and system
The only option is what you can get out of SAP. I did not use CSV before.
Hi
If I change sap password wha will be the effect should I need to change the code again?
The script works on a session you already logged into. So it will have no impact.
This is going to make my Mondays far simpler. How can I use this to pull the last work week of data? I normally have to put in a start and finish date.
You have to code it in VBA. From the current day you can always calculate Monday of last week. If you are running the report on Mondays, last Monday is actually Now-7, and last Friday is Now-3.
I’m trying to download transaction MCBZ. For this particular transaction I can only export it as spreadsheet and not .txt. How can I save it once downloaded in excel? How can I connect both spreadsheets? (The one with the script and the one recently downloded) thanks!!
Hi Csongor. Could you please upload a video while you export a report and saved as an excel.file.and not text? I can' manage to adapt your file to my gl balance export.
@csongor Varga , why in the saving file or when the save file diaglog appears the script is cut. do we have solution on that? in using SAP 7.20 inside client CITRIX
File save dialog is a Windows dialog, and cannot be scripted. There is no way around it. In the 7.40 GUI there is an option in the settings to use SAP dialogs only.
Is it possible to do it in reverse, for example extract from Excel to SAP
Yes, it works both ways. You can populate SAP fields from Excel cells. I coveres cases in other videos.
Its awesome. But i have problem with a special signs at Polish language. Where I can change coding to UTF-8?
I have an Encoding field on the SAP pop-up where you specify the path and filename. I have to admit I have never used it before.
I had the same Problem:
You have to Change the .TextFilePlatform Code:
.TextFilePlatform =
850 Dos-File Format
65001 UTF8 File Format will be in the right format
1252 Windows(Ansi)-Files will be shown in the right format. It works for the German ÄÜÖ etc.
Hope you still need it!
Hello Csongor, how would you change the script to filter for specific different Object _D's? If you wanted the script to run specific Object Id's from a list in Excel?
In most of my other videos I show how you can pass data from Excel to SAP and even back. So you create a recording in which you run SE16, you specify the object IDs. Record the steps as you paste in the list of Object IDs from the clipboard. In Excel, you can use a VBA code like: Range("A1:A10").Select. This will put the entries between A1 and A10 into the clipboard that you can paste into Excel.
Your videos are of a great help! I just couldn't find the answer on how to select the dates range of the SAP report from Excel's cell. They are firmed in the script, while I would like them to be different every day, without changing them in the code. Does one of your videos explain the solution to that?
Yes, probably this is the only video in which I use variants. In most of my other video I show now to record changing values in SAP and pass those values from Excel.
Hi Csongor Varga, I am trying to record tcode zv401 however, the scripting is not recording the exporting the data. could you please help me with it? Also, all the message pop-up in recorded.
Thank you in advance.
Yes, all the message pop-ups will be recorded. You can't so nothing about that. What do you mean not recording the exporting of data? Is your transaction using a Save as dialog window? That will never be recorded.
Hi everyone! I know that you cannot download SAP data straight to the ".xlsx" format because of the windows window that SAP GUI does not detect. But, does anyone have any trick to do that? I mean, maybe you can run a macro in excel that does this part of the script?? I have tried some different stuff, and still with 0 positive results...
Hi sir. Can I also apply this for treeview?
I did a Gui scripting trick and tips video where I talk about the tableview
So, this could be used to run multiple transactions synchronously, while putting the results in their corresponding tabs - correct?
Yes of course. There is no limit. Create separate recordings, turn them to separate functions in Excel and call these functions one after the other when the button is pressed on the first worksheet.
@@csongorvarga My recordings are done... 7 different transactions to *.XLSX then linked as external data to MS Access. I found your video, trying to overcome the "Handling a Save as dialog box" focus dilemma. I currently put the path\filname.xlsx on the clipboard, then when the DB opens, they click the title bar, paste, and click save. But they want to run them all at once and walk away - which I agree, would be ideal. With your solution, I have one XL file and one button, while retaining my links to MS Access.
Fantastic video
This is amazing, but is it possible to do it when you use export?? When you export to spreedsheet appears a Windows "Save As" and here I don't know how to change the path and file name...😥
No you cannoy script that. It is a Windows dialog and sap will not script that.
Awesome! Thanks for sharing!
Thank you so much for this video. Keep up the great work !
Hii I want to load the excel file into Sap system is there any way we can automate this process
I do have another video on downloading and uploading attachments to SAP. That could be useful for you.
Hello Varga,Great video. It would help a lot.I did try run your code using SAP code FB03 and appear the error message after run the "StartExtract()" - "No Active session to system DCG210, or scripting is not anable" How could I check if DCG210 is the correct one for my SAP?
You should know in which SAP system you want to run the script. When you log into SAP you select a system and a client. It you click on the status bar where you see the transaction code or user, you will see the SID and client as well.
yes, I found here. it's working very well. thank you very much
I'm getting a compile error "user defined not defined". objGui As GuiApplication is highlighted in the script. I use SAP GUI "EGP 301" so not sure if that's what causing it? however, I did change name from DCG210 to EGP 301" but not change.
Did you check that under Tools / Preferences you have the SAP GUI Scripting added? Also, don't put spaces between the SID and client, set it as "EGP301".
yes, I just checked the SAP GUI scripting options under tools/preferences but it still didn't fix it and I also already had "EGP301" without spaces. The error is happening in below line for some reason.
Public objGui As GuiApplication
Hi Csongor
Thank you for the video! I have one quick doubt
1) while you exporting data from SAP, you are using "%PC" Tcode is this SAP standard Tcode ?. while I'm using in my system it's not working
2) How to record "SAP GUI Security popup" window "Allow" button while exporting VBAK table information it's asking.
Kindly do this needful support to better understand :)
Thanks,
Hemanth M.
%PC is a command which does the same as export to local file, which is usually available in the menu in an SE16 like transaction. So it is a standard GUI shortcut.
I don't have SAP Logon installed on my laptop right now, but you can do two things with the security popup:
1) Tick Remember my decision and it will not be asked again for the same folder.
2) You can go into the SAP Logon settings, and in the Security group you can set if certain folders should be allowed or denies for export.
Thanks your best answer to solve my issue.
Dear Csongor Varga your videos are very helpful. Thanks for helping the newbies in GUI. I have minor issue. Showing error in "objSess.EndTransaction" when we have not logged in the SAP. I would like to put msgbox instead of error pop up. Please help.
Hi I am trying to pull the data from SAP FBL5 to excel and format it. Can you provide me the coding as it is not matching with this coding
This is a tutorial how to make your own, not a request forum.
Great Video. I have a question, where do i find the reference SAP GUI Scripting API??
In the Visual Basic for Applications under Tools / References menu.
Thanks, by the way, I dont have SAP on my computer, so the Reference doent appear, I guess that only in a computer where has SAP I could find it, am i Correct?
Yes, that is correct.
Thanks for sharing these tutorials. Helped me a lot. I faced one problem. When extracting the data from SAP from large tables I get a msg "waiting for another application to complete an OLE action" and SAP stops to execute unless I click OK. I have googled and found a solution how to turnoff this option in the Excel, but when I turn it off then I cannot open macro files. Have you faced this problem?
Sorry, I have never experienced this. I did a quick google and found this Stackoverflow page: stackoverflow.com/questions/22379815/microsoft-excel-is-waiting-for-another-application-to-complete-an-ole-action. There are few recommendations there. Give them a try.
Hi, I'm wondering if this will work for a table with 1.1m entries which exceeds the excel limit. If not , is there a workaround?
Yes the new excel limit is 1,048,576 lines. Maybe you can split the table by setting up a selection criteria, export in two pieces and import them into separate sheets. I don't have a better solution right now. Unless you import it to somewhere else than Excel. Maybe Access.
Hi Csongor,
how can I call / execute a SAP BW Transaction for example "Listcube" with a Excel Macro?
Hi all,
I hope you can help me.
whe run the script and in the moment is assigned the "session" Excell shows a message "The enumerator of the collection cannot find en element with the specified index" and I review in the "watches" and the connection variable don't have sessions and I have open session un SAP.
do you have any idea todo fix it?
thank you
I think it will be very difficult to help. So you click on the button to start the script. Does Excel connect to SAP. Does the GUI window comes on top of the screen? Does it start the script at all?
I am running your script and it shows "no active session to system" un this case for me is "ACD"
I activated the scripting in SAP Options on Customize Local Layout
And the next message "Not connected to Client"
But client is working.
Rogelio Lugo Did you update the SID and client in the code? Open the VB editor and right at the end of Module1 there is a variable called W_System. Change that to whatever SID and client you use.
Thanks for sharing. Just a quick question, I get an error message when I try to download the spreadsheet. It says "Invalid GUI input data: FOCUS DATA (Message no. 00123)". Any idea? Thanks.
You mean this error comes when the script is running, right? What is happening in the GUI when you get this message?
Csongor Varga The error comes at the end of the script. It ran the report but it failed when it tried to save the report to xls file.
I am sorry, but I cannot help like this. I would need to know which line the script is failing and also see what is happening in the GUI.
Csongor Varga ok. that's fine. I'll try again next time. thanks. Great video. I learned so much. thank you so much :)
is it possible to copy text from popup windows in R/3 into the cell of an Excel?
recording of script in r/3 does not save the movement highlighting the text and copying
Yes you can. The recording will save as you press the ok button on the pop-up window. You can use the debugger and go to the parent object and explore all the elements in the popup.
Csongor Varga it works! thanks a lot!
Thank you for your video! I’m trying to use the macro but I get the error “no active session to system PRD900, or scripting is not enabled”. Well, scripting is enabled and I found the SID in the login window..so, the only mistake could be the client..where can I find it?
I have the same error :(
Riccardo Caruso I cannot fix it! I tried every number I found in the GUI for the client but the macro does not find the active session. If you’ll find the solution please share with me! I’ll do the same!
@@manuelsilvestri3159 I did it!
bar at the bottom right in Sap screen.
Following the tutorial you will see DCG (1) 210
DCG = sid
210 = client
Riccardo Caruso good! Now I have to understand how show it in my interface! My bottom bar is empty!
Hi Csongor
That's brilliant. For some reason i am failing to have excel connected to SAP still. it gives an error on the line
If objGui Is Nothing Then
Set SapGuiAuto = GetObject("SAPGUI") ----------->Highlighted in Yellow
Do you think i am putting the system wrongly? At the bottom right it says PE3 (1) 400 and the client is 400. so I have put PE3400 but still giving me this error.
I have also checked and I have added the GUI Scripting in Excel as you instructed. And my SAP GUI Scripting is installed and enabled as well. I am struggling here to be honest and would love your help. thank you
I am confused. PE3400 is the correct setting. If you are getting an error in that line my first guess would have been that you did not add the GUI Scripting in the references, but you did. What does the error message says?
@@csongorvarga I am also trying to automate the session log on so I can schedule the guiscripts & extraction. Have you managed to automate the log on element?
Awesome video, it helped me a lot to understand how to export data. Do you know which function I can use to export to .xlsx spreadsheet instead of .txt?
I don't think there is an .xlsx option. There is a spreadsheet option, but that is not xlsx either.
Hi, do I need to report non-generated accounts?
You can do it through a direct transaction?
please.
Is it possible to extract file that reqire tommorow date? For example T Va05
Yes, why not. You can set up a variant with System Date + 1 day, or just calculate tomorrows date in Excel and pass that to SAP.
any idea how to make date range a dynamic? example from = today to =31.12.2018?
You can set dynamic dates in variants as well. It should not be a problem. Otherwise you can record setting the dates in the script and add the date from Excel dynamically.
Does this DCG210 code depend on the version of SAP we work with or on any version?
I used GUI scripting on GUI versions 7.20 to 7.40. DCG210 is just the sid and client id of the system I was using at that time.
Hello Csongor, when stepping into your code, should SAP graphic show as running when you W_Ret = Attach_Session under the RunGuiScript sub routine or at W_System = "SIDClient" in the StartExtract sub routine? My SAP GUI code works fine, but i don't believe my connection works as it should. It connects at Set W_Sess = W_conn.Children(it + 0) under Attach_Session sub routine and i error out after the 1st line .maximize on My recorded GUI code. GUI codes works independently, but I "myerr" in excel.
It's weird, because i have to stop "Reset" my in excel to stop the SAP GUI from running, but yet no session attached. hmmm
Hi,
Wanted to know, is there any way where we can extract the specific row or column in the SAP GUI window.
Like in st06: how to extract only CPU idle % or memory% to excel.
I do talk about gridviews in my tips and tricks video. So you can access each column and row of the gridview. So it should be possible.
very interesting video. I could successfully generate the code and run the script.
Im running it from an Access file. Unfortunately what I couldn't do is to replace the part of the code where the inputs for SAP transaction are (dates, for instance) with a Variable that can take info from a particular field from an access table. My idea is to put fields in a form where I can complete the dates and when I run the macro, it downloads de SAP information for that dates (what it actually should do is to paste that date in an table and the code should be able to take the information from there). Do you know how to do this?
I am sorry I did not get this. What are you trying to do? Your issues is with the formatting of a date?
What I mean is that I recorded a macro, suppose a very simple one --> logging into SAP, entering a transaction and putting a date, then exporting it to excel and I put that code in an access. I created a form where I put a button that run that macro. Everything fine. The problem is that the date is fixed in the code. I want to replace that date for a variable (suppose "Date1") and defining that variable to take the date stored in a field of a table from the access (So I Can complete it before running the macro)
I have done the same in almost all of my videos. Even in this one I have a global variable for the file name and I am adding it to the script. You can do the same, and use the Format function to make sure your date is formatted in a way SAP expects it.
Thank you for the video! I am almost done with the code but I am getting error from the last rows:
Sub StartExtract()
' Set the sid and client to connect to
W_System = "DCG210"
I am pretty sure your system is DCG210. So you need to update that. The SID and the client ID is on the lower right of the GUI. Scripting should also be enabled in your SAP system.
It works! Thank you so much.
Hello Niko Nalli Please can i ask you a question. did you run your code with the W_System = "DCG210" or you changed the W_System?? because i have the same problem you already had.Thank you.
Encounter the same issue
How ti connect P01 or any other system connect to excel. I can see LH1100 is already updated in your excel file(example)
It is in the VB code in Module1, and towards at the bottom of the code, where it says W_System = "DCG210". Replace that with your SID + client.
I can not find the script in google drive file. :(
The link works for me. Drop me a mail to my gmail (first name dot last name...) and I will sent it to you.
Hello Csongor, thanks for the video, I have one query, i'm trying to extract the data in MHT format but while recording the script this doesn't gets captured, could you help me on this??? this way I'm trying to download the extract (List>>Export>>Spreadsheet [Shift+F4]).
The reason I'm extracting in MHT format is the amount and data comes in proper format but in others its jumbled. please help!
MTH format? Where is this? Just make sure to click on the field or option, even if it is already selected in the recording.
Csongor, I'm using the tcode: S_ALR_87011964 to get the company codes details, instead of download the file through Local File method, I'm using Spreadsheet method, where i get the HTML format extract. When recording this only the downloading part is not getting captured in the script. Is there a way to solve this ? or I need to use only local file method for extracting the file.
I am sorry, I could not make this transaction to work in the systems I have access to. I don't know how it looks like and why it is not recording the steps correctly.
Csongor, I somehow made it to work with a different format... Thank you so much for all your videos which helped me a lot... Please share me your personal mail ID so that if any helped required I can write you a mail...
Para extrair de outra transação vl06o o que teria que mudar?
Hi ! I am getting this notification continuously this notification continuously that "Microsoft Excel is waiting for 'Display Project Actual Cost Line Items' to complete an OLE action". Can someone help me please.
What is happening in SAP? Is the GUI doing something? You get this message when Excel is waiting for SAP to do something.
@@csongorvarga yes I am trying to download an excel report from SAP using GUI scripting and your excel macro file but unless I click on 'OK' in this pop up message my file is not downloaded. Please check I have responded to your email with screenshots.
@@csongorvarga I just checked in SAP. 'Display Project Actual Cost Line Items' is the same notification I get in SAP while exporting an excel report after the TCode is run successfully.
Thanks a lot! Does anybody know if this works with google sheets?
No, Google Sheet does not includes VBA scripting support.
@@csongorvarga Thanks for the replay! Do you know if there is a similar way to do this with sheets? Or maybe an alternative way that works too?
@@rodrigoaguado153 I don't think so. Google Sheets has its own macro language but probably the biggest reason it would not work, because the Google Sheet is not allowed to go outside Google Sheets. So that macro language will not be able to interact with the SAP GUI windows API running on your computer.
@@csongorvarga thats a shame, thank you for everything!
Thank you so much for the video. I am using Excel 2016 and I am unable to run the macro I have "Missing: SAP GUI Scripting API" in the excel References. is there any other way that we can exclude and run the code
Add the missing reference. sapfewse.ocx.