HomeТранспортRelated VideosMore From: Csongor Varga

SAP GUI Scripting - Extract SAP report data to Excel

364 ratings | 88269 views
This video provides and example of running GUI scripts to export table contents via SE16, or queries via SE38. This data is exported to CSV and loaded to Excel to be manipulated there. GUI scripting allows external applications like MS Excel to connect to an existing GUI session and execute operations within the GUI. Excel does not hold and authorization and access information as it relies on you logging into SAP first. This example is in CRM but it can be used in any GUI based SAP system. Download the Excel file in this example from here: https://drive.google.com/file/d/0Bw1XOPtMPlsNRi1SdDhHTUMwY3c/view?usp=sharing Playlist of my SAP GUI scripting Youtube playlist: https://www.youtube.com/watch?v=oPPhA14Pm-8&list=PLk9erb9HGsPh28DftWcP8BIXmBtu2wnsL
Category: Транспорт
Html code for embedding videos on your blog
Text Comments (107)
sunil kumar (4 months ago)
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.
sunil kumar (4 months ago)
Please hepl me how to resolve this issue.
Haris Khaliq (4 months ago)
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?
Csongor Varga (4 months ago)
Yes, you can pretty much do anything with scripting and VBA code. But you probably need to do your own coding in Excel.
Mariusz Dekier (4 months ago)
Its awesome. But i have problem with a special signs at Polish language. Where I can change coding to UTF-8?
Csongor Varga (4 months ago)
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.
KTY MHPR (4 months ago)
Hi Csongor, how can I call / execute a SAP BW Transaction for example "Listcube" with a Excel Macro?
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
Csongor Varga it works! thanks a lot!
Csongor Varga (5 months ago)
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.
Robert Paul Devadason (5 months ago)
Very useful video...gonna try this...will help me save time on downloading data and preparing reports on a weekly basis. thanks man
neelesh agarwal (6 months ago)
can we run multiple t-codes in the same file by adding new worksheet to after extract?
neelesh agarwal (6 months ago)
worked wonderful. Thank you so much for your time and great attitude of sharing the knowledge. :)
Lengyel Levente (7 months ago)
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.
Ahmed El-Alfy (7 months ago)
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
Lee Chesters (20 days ago)
+Csongor Varga 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?
Csongor Varga (7 months ago)
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?
uday kiran (7 months ago)
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
Csongor Varga (7 months ago)
Add the missing reference. sapfewse.ocx.
hanae jabri (7 months ago)
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.
Mohannad Miski (8 months ago)
any idea how to make date range a dynamic? example from = today to =31.12.2018?
Csongor Varga (8 months ago)
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.
Robert Johnson (9 months ago)
Awesome video!!!! One question, what reference(s) should I make available for this to work?
Robert Johnson (9 months ago)
Thank you. I watched your first video and found it :)
Csongor Varga (9 months ago)
You only need the "SAP GUI Scripting API" which is sapfewse.ocx.
Janine Pereira (9 months ago)
Hi, do I need to report non-generated accounts? You can do it through a direct transaction? please.
Sebastian Arias (10 months ago)
Thank you for your video... it's so helpful! ..do you know if there is a way in which I can export the data in Excel format, copy it and then paste it in my Excel (the one that has the code)?
Izabela Ranocha (10 months ago)
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?
Csongor Varga (10 months ago)
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.
Aaron _ (10 months ago)
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?
Csongor Varga (10 months ago)
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.
Dada Gholap (10 months ago)
Superp !!! very thanks Csongor
Aleksandrs Bebrišs (10 months ago)
I can not find the script in google drive file. :(
Csongor Varga (10 months ago)
The link works for me. Drop me a mail to my gmail (first name dot last name...) and I will sent it to you.
Carlos De La Cruz (10 months ago)
Thank you so much for this video. Keep up the great work !
julian mendoza (10 months ago)
Thank you Csongor. i was able to adjust you process to extract project actual costs CJI3
whitedeer bella (7 months ago)
Hello Julian, could you please share your code?
Csongor Varga (10 months ago)
Good job! Thanks for letting me know.
Rajkumar Gerard (1 year ago)
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!
Rajkumar Gerard (11 months ago)
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...
Csongor Varga (11 months ago)
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.
Rajkumar Gerard (1 year ago)
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.
Csongor Varga (1 year ago)
MTH format? Where is this? Just make sure to click on the field or option, even if it is already selected in the recording.
Pham Thi Hong Chau (1 year ago)
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?
Pham Thi Hong Chau (1 year ago)
Oh I see. Thank you for your reply :)
Csongor Varga (1 year ago)
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.
Ivanes santos (1 year ago)
Thanks for the videos again, I already use this for some transacions like ME5A and I use to save the export with spreadsheet, and to open the file I use this way - Workbooks.OpenText Filename:= "C:\temp\myfile.xls" sorry by poor english
Sofia Chernylo (1 year ago)
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?
Csongor Varga (1 year ago)
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.
João Carlos Lira (1 year ago)
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?
Csongor Varga (1 year ago)
I don't think there is an .xlsx option. There is a spreadsheet option, but that is not xlsx either.
Shirley Puente (1 year ago)
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.
Shirley Puente (1 year ago)
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
Csongor Varga (1 year ago)
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".
Edson Cavalcante (1 year ago)
Does this DCG210 code depend on the version of SAP we work with or on any version?
Aaron _ (6 months ago)
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
Aaron _ (6 months ago)
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.
Csongor Varga (1 year ago)
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.
Caroline Widjaja (1 year ago)
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.
Caroline Widjaja (1 year ago)
Csongor Varga ok. that's fine. I'll try again next time. thanks. Great video. I learned so much. thank you so much :)
Csongor Varga (1 year ago)
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.
Caroline Widjaja (1 year ago)
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.
Csongor Varga (1 year ago)
You mean this error comes when the script is running, right? What is happening in the GUI when you get this message?
hemanthmaharana (1 year ago)
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.
hemanthmaharana (1 year ago)
Thanks your best answer to solve my issue.
Csongor Varga (1 year ago)
%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.
joseph quinones (1 year ago)
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.
Csongor Varga (1 year ago)
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.
Raphael Veiga (1 year ago)
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?
Raphael Veiga (1 year ago)
yes, I found here. it's working very well. thank you very much
Csongor Varga (1 year ago)
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.
Leanne Gerodias (1 year ago)
Hello! All codes are workig except for the save/extract part. Im using ksb1 tcode, is there a different sap script for this when saving to spreadsheet? Thanks!
Leanne Gerodias (1 year ago)
Csongor Varga sent! :)
Csongor Varga (1 year ago)
Oh yes :) first name.last name at gmail dot com
Leanne Gerodias (1 year ago)
Csongor Varga where/how can i send it to you?
Csongor Varga (1 year ago)
Send it over and I see what I can do. My access nowadays are limited, let's hope I can run ksb1.
Leanne Gerodias (1 year ago)
Csongor Varga can i maybe send the file to you so can take a look?
emad Hossam (1 year ago)
can I connect SAP to MS Access? I need to extract some data and report from SAP to MS Access
Csongor Varga (1 year ago)
I never used MS Access, but Visual Basic for Applications are available for all MS products. The same code should work in Access as well (without the Excel specific lines). I am not sure what the options are for creating data from VB, but with some google I am sure you find some examples.
vini vini (2 years ago)
How ti connect P01 or any other system connect to excel. I can see LH1100 is already updated in your excel file(example)
Csongor Varga (2 years ago)
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.
Rogelio Lugo (2 years ago)
yes, but sessions are not created.
Csongor Varga (2 years ago)
It is my name (first name dot last name) and gmail dot com.
Rogelio Lugo (2 years ago)
+Csongor Varga the session is open. how i can send you a screenshot with muy issue?
Csongor Varga (2 years ago)
I am not sure what you mean. This script connects to an already open session. You need to log into the system before you start the script.
Rogelio Lugo (2 years ago)
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
Csongor Varga (2 years ago)
+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.
Rogelio Lugo (2 years ago)
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.
Csongor Varga (2 years ago)
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?
Dionicio Carrillo (2 years ago)
Great Video. I have a question, where do i find the reference SAP GUI Scripting API??
Csongor Varga (2 years ago)
Yes, that is correct.
Dionicio Carrillo (2 years ago)
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?
Csongor Varga (2 years ago)
In the Visual Basic for Applications under Tools / References menu.
Phenom (2 years ago)
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?
Csongor Varga (2 years ago)
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.
Phenom (2 years ago)
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)
Csongor Varga (2 years ago)
I am sorry I did not get this. What are you trying to do? Your issues is with the formatting of a date?
Raul Guerrero (2 years ago)
Great, thank you for sharing!!!!
Radek Dumania (2 years ago)
Problems starts when you have different table layout (ie grid list instead of standard se16 list) ,  different set of fields selection criteria in sap, too low number max number of hits, etc. Two choices then: enforce user to have a specific setup or add respective code into macro (which for me is not appropriate, to change user settings) I heard about RFC method which can bypass these potential complications, but I haven't had  yet 100 hours to explore it.
Csongor Varga (2 years ago)
There are some more advanced tools you can pay for that can do some of the coding for you. I have stumbled upon a few myself as well. This on the other hand is free, but requires you to get your hands dirty in the process.
Niko Nalli (2 years ago)
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"            <----- Here it says that "there is no session at "DCG210", or scripting is disabled (works in sap)"     ' Run the GUI script     RunGUIScript     ' End the GUI session     objSess.EndTransaction         <----- And because of that it draws this one to yellow     Any idea how to deal with this issue please?
hanae jabri (9 months ago)
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.
Niko Nalli (2 years ago)
It works! Thank you so much.
Csongor Varga (2 years ago)
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.
Nasri Brahim (2 years ago)
WELL DONE MEN!
Csongor Varga (2 years ago)
Thanks

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.