Automate SAP Data Extraction with Excel VBA & SAP GUI Scripting - Minimal Coding Required
HTML-код
- Опубликовано: 4 авг 2024
- Get instant access to the eBook "SAP GUI Scripting: Understanding the Basics" and discover how you can transform your SAP work processes, reduce errors, and boost productivity!
joelting.com/sap-ebook/
As an analyst in one of the MNC, I'm always extracting data from SAP to prepare reports for different users.
I saved a lot of time automating these extraction process. After the automation is done, I will just have to click on a button which will trigger a script and the data would have been extracted
In this video, I'll break down exactly how I use Excel VBA to automate the process of extracting data from SAP.
Timestamps
00:00 Introduction
00:13 Why Excel VBA?
00:50 SAP GUI Scripting Basics & Recording Tool
02:00 Preparing Microsoft Excel for SAP GUI Scripting
02:33 Preparing Visual Basic Editor for SAP GUI Scripting Development
03:07 Create variables and establish connection with SAP GUI with template
03:44 Complete sub-procedure with SAP recorded script
04:16 Editing script to make it dynamic based on users' inputs
06:20 Create shortcut to run SAP script with a single click
06:48 Completed script test run demo
08:54 Summary/Recap of SAP Data Extraction Automation
#SAPScripting #ExcelSAPAutomation #SAPScriptingwithVBA
Code Template used in the video:
Option Explicit
Public SapGuiAuto, WScript, msgcol
Public objGui As GuiApplication
Public objConn As GuiConnection
Public session As GuiSession
Sub SAPDownloadAttachment()
Set SapGuiAuto = GetObject("SAPGUI")
Set objGui = SapGuiAuto.GetScriptingEngine
Set objConn = objGui.Children(0)
Set session = objConn.Children(0)
'Insert your SAP Script here
End Sub
DISCLAIMER:
SAP® and SAP GUI Scripting are registered trademarks of SAP AG.
I am NOT associated with SAP AG in Germany or any other country. I am NOT SAP Partners. I do NOT offer live SAP Training. I do NOT offer SAP Access. I do not recommend the use of SAP Access from any particular provider.
🤝 BE MY FRIEND:
🐦 Twitter - / joelting92
🏢 Linkedin - / joel-ting
🙎🏻♂️ WHO AM I:
I'm Joel, an analyst working in Singapore. I make videos about technology, automation and productivity.
📧 GET IN TOUCH:
If you'd like to talk, I would love to hear from you. Email me directly at joelting92@gmail.com would be the quickest way to get a response. I will try my best to reply to your email as soon as possible. 
Наука
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/
Hi Joel, Awesome presentation. The information provided in the video is fabulous. Thank you!
Hi Joel, I am now able to get the macro to work.
I want to convey my sincere gratitude for your videos. Your videos will help countless professionals enrich their skills and to be more productive in their work. Keep up the GREAT WORK.
Awesome! Glad to hear that you get to make it work 😀
Hello Joel,
Thank you so much for this lesson. I was able to use it. You have my love and respect. God bless you!!.
Hi Joel, thanks for your video. I was looking for Excel VBA for beginners and I found your video which is very useful and easy to understand.
Glad it was helpful!
it's my first time creating VBA and your tutorial works wonder, huge thanks!
Glad it helped!
Hi Joel, this is a great video. Thank you for sharing your valuable expertise.
Hello Joel, Thank you so much , this video is very amazing and helpful, i am very grateful for your great work.
What a great Video
The explanations are so clear. Thank you very much!!
It's exactly what I need. Very helpful. Thank you.
Great job as always Joel. Keep up the good work!
Thanks again!
I gave this video a like, but I wish i could give it a LOVE!!! This is amazing, THANK YOU!
Thanks for the kind word Jessenia!
I made it for the 1st time. Thank you. 😊
Excellent video. Very interesting option and I will be looking for ways to apply this to my day to day work. Thanks
What an amazing video that was! Thank you so much!
Hi Joel, this video is a goldmine of information! The only one that clearly explains the steps. Please keep posting new videos on SAP automation and SAP data extraction. Is it possible to automatically upload the extracted multiple Excel files to a SharePoint folder?
Hi Shakeel, in my opinion, the simplest way is to sync your SharePoint folder to your local drive. Then, when you export and save the spreadsheet in the folder, it will be uploaded automatically to SharePoint.
Hi Brother!!!
You are truly amazing! For distribute such rich knowledge in a simple and right way...
Congratulations!!!
Thanks for the kind word!
With these kinda videos you know that the word ''genious'' is badly used sometimes. Thank you, I hope to be able to do it myself :D
Thanks for your kind words! 😊
HI Joel, You have really solved my problem , i really like the way you make difficult task easy enough for new comers to understand, i would appreciate if you can also explain how we can run multiple VB scripts for download various reports from SAP in one single click, i believe it will a piece of cake for you as usual .
Hi Mah H,
Thanks for the kind words, glad that you find it helpful. As for your question about various reports in 1 go, you can simply string all the steps together. For example, right after the first report have been generated, continue the script to navigate to the 2nd tcode using "/n *your t-code*" in the navigation bar.
I will put this in the list of new videos that I will be working on. Thanks for the suggestion! :)
Thanks for good video. Very clear & useful.
Amazing Tutorial, simple explaining a complex matter.
Hope it was helpful!
I'm extremely grateful for you. Thank you very much.
Exactly what I needed!!!
Thank you so much! I think I might be able to solve my problem now.
Sir! You just saved me a lot of time. I needed this code: ValorBase = ActiveWorkbook.ActiveSheet.Range("F3").Value
It was driving me crazy figuring out how to create this function in order to paste it into SAP. Anyway, thank you very very much!
Awesome! well explained
Fantastic video! ❤
That is what i was looking for ¬¬¬¬ Thanks!!!!
Great video - thank you :)
amazing, it´s incredible
Very Very useful. Thanks a lot
You are the best!
You are truly Amazing
Interesting ................thanks for sharing
excellent video
Thank you, thanks a TON.
Awesome bro
Informative video
Thank you so much 😍
Amazing
Awesome
This is great
Thank you!
Nice video
Hi, muchas gracias me funcionó a la perfección
Thank you for the kind words!
Hello Joel, this video is amazing! However I do not understand how to choose the between TEST and PRODUCTION environments of SAP, since I have access to both for work. Which specification is needed in the code?
Thanks!
Hello Niccolò,
Thanks! For the choice of Test and Production, are those 2 SAP sessions being accessed both at the same time?
If it's not, then just make sure the right one is being open at the time of running the script. If there are 2 sessions and you would like to differentiate, checks can be done by going through objGui.Children object (checking the system name of the session) and identifying the correct sessions to connect to.
Thank you
Hi Joel, I am trying to make the date and the time, and the folder path dynamic for my macro, but when I dim those variables in my code and set the value in the active worksheet, there is always the error "The control cannot be found by ID" and when I open the Watch window, the value for these variables is always . Do you have any idea what the problem could be?
Hi, apologies for the late reply. Is your SAP open when you are running your script? It seems like it is not able to find the "buttons" or "areas" that you are requesting it to interact with in SAP GUI.
Man, I wish I would have found this when you put it out 2 years ago. Great video. My only constructive feedback would be the volume. I had both my computer and YT volume on max, and it made it better, but I had to turn on CC to really follow you. Still, great job, and keep it up.
Thanks for your feedback! I have since upgraded my recording device. Hopefully my future videos will provide a better experience to you and everyone else. Thanks again!
Thanks for the great video :). Do you know how to change that excel does not start automatically when exporting data ? I ask because I extract a lot of data from SAP and every time I have to close the window with excel.
Hi Kamil, I think this is also one of the issues that I'm facing. Excel will automatically start after extraction. However, something interesting that I noticed is that if I string all the reports together when extracting multiple reports, Excel will only open once at the end and the Excel opened will only be the last spreadsheet that you are exporting. So, you can try it out and don't let it deter you from developing something.
Meanwhile, I'll still be looking for a workaround. I'll let you know if I found one.
Thank you!!!!
U experienced a different type of pain doing stuff manually at work huh 😂
Joel you're awesome :). Thank you so much for the video.
Edit : subscriber +1 :)
Hi Joe, Is there any way to reduce SAP report execution time? Macro is working for the rest of the manual steps except to reduce SAP execution time.
Hi kotipalli sowmya, unfortunately no. SAP GUI Scripting is all about imitating user input on SAP GUI, thereby automating the front end process. SAP execution time is dependent on the scripts at the backend of SAP server.
Thank you Joel for this video. I just have one question if you could help please.
Is there a way I can automate this report to be uploaded to sharepoint everyday at a fixed time? or Should I be logged in to SAP to get this done?
Thank you.
Hi MrGautam92, it really depends on how this is being setup. If your company is not using Single Sign On in SAP, you can use Azure Key Vault to store your username and password, schedule a daily flow in Power Automate to run the automation. This should work as long as the laptop is on.
However, if your company is using Single Sign On, most likely you need to log into SAP every time before you run your script. In that case, you would not be able to schedule it to run at a specific time daily. As of now, I am not able to find a workaround for cases where single sign on is being used.
Great video! Loved it!
Could you also do one with an RFC call function please?
Hi Pnorby, I'm not familiar with the RFC call function though. I did some search about it, seems like it's part of ABAP. I only have access to SAP GUI, which I use VBA to interact with.
Hi Joel, When I try to save, it brings up Windows save box instead of SAP save box. Is there a setting I have to select to use SAP save options instead of Windows? The script record does not capture what is done in Windows Save. Thanks
Hey there. You could try checking in your Options to see if the "Show native Microsoft Windows Dialog" option is unticked. Another option is to export your data/table using "Text with Tabs" and process the text with VBA into tables in Excel. Usually exporting with "Text with Tabs" will always brings up the SAP save box.
Hi Joel,
I cannot see the SAP GUI Scripting API on the reference neither browsing. is there alternative reference we can use?
Hi Ronnie Gelacio, usually if SAP GUI is installed locally in your computer, you should be able to find it. I have a detailed step by step explanation on how to look for it in this video. Make sure to change the file type to ActiveX Control when you are looking for sapfewse.ocx file in SAP folder.
ruclips.net/video/7Rxh10Kt5v4/видео.html
Mr. Joel, how do you get SAP to automatically download the ME5A tcode using a recording script so that it becomes data in excel format.
Hi Denny, sry I'm not very familiar with the ME5A tcode. However, if it can be done with clicks within SAP GUI, most likely it would be doable.
Thanks
Thank you!
Hello Joel, thank you for the tutorial. Can you help me, please? My Save as dialog box is not the same as yours (mine opens Windows Save as dialog box). The script didn't record that part.
Hi, can you try unchecking the "Show native MS Windows dialogs" option in the SAPGUI options > Accessibility & Scripting > Scripting? This may bypass the save as dialog box
Hello joel,
In Preparing Visual Basic Editor for SAP GUI Scripting Development, the setting are not same i am using excel release number, like 2105 (May 2021), and build number, such as 14026.201.
So can u guide me .
Hello Trusty Vibes @ Abhi , would you be able to browse for sapfewse.ocx file in the directory "C:\Program Files (x86)\SAP\FrontEnd\SapGui\" ?
Usually it's not there for selection by default.
You can refer to the video below for a step by step guide to search for the OCX file.
ruclips.net/video/7Rxh10Kt5v4/видео.html
Hi joel hope you can help me as i tried the code in ectracting report in sap using the macro. My process is to extracr file then save, then back again to change the parameter but there is an instance that there is no data to extract on that specific parameter. What code should i add so the extraction will continue to the next parameter if there is no data on the previous parameter i chose. Appreciate your response. Thanks in advance
Hi Noemi. I believe when there's no data, SAP GUI will show that in the status bar on the lower left corner right? You can try to use session.FindById("wnd[0]/sbar").Text to capture the text in the status bar and wrap it around a condition to validate the output.
Hi Folks, I am trying to determine if I have a list of parts in Excel is there a way to automate the SAP process of getting the SAP MM03 Standard Cost and Price Unit out? Rather than doing it individually for each item?
Hi there, yes it's possible. Just do a loop, for each row, get the part number and plant (if it's different) in Excel, navigate to MM03, run your script where it can input your part number to reach the standard cost page, then at the page, get the value using the script and input it as a cell value in Excel.
Thank you so much for the explanation Joel😁
I do have a query- I would like to extract data from SAP but for current dates. For example, if I run the macro today it should pick up today's date tomorrow then tomorrow's date and so on. Could you please help me with this. Thank you!!
Hi. You can just declare a date variable, and then assign it to today's date. Then just pass the variable into the script where you want to input the date. The following code will return the today's date in VBA:
Dim dtToday
dtToday = Date
Hey,
I do not have the the SAP GUI API option available in Excel. I also cannot find the ocx file. Is there something i am missing?
Hey, usually, this file is installed together with SAP. There are some cases where they access SAP via Citrix. In that case, you probably do not have the file on your computer. Can you try navigating to the file path and see if there's any sign of the OCX file in Windows Explorer?
Hi Joel, i'm automating an excel sheet that gets data from 3 of this tyoe of files that are exported from SAP. But I can't get the macro to closet this files once the export is done (out of Range error) is there somethimg special that needs to be done?
Hi there, credits to Patrick Migues, he suggested to try the following:
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.
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.
@@JoelTing Thanks for the reply, Ill try the solution tomorrow.... for now I went into kernel to look at all open instances and killed all but the active one (saved the instance in a variable first) but its not a very nice way to close them
@Joel, lovely videos. What if I have to run more than one tcodes at a time where there should not be timing differences. How can I go about this?
Hi there, 1 way is to have 2 SAP sessions opened. then run the execute button on each session one line after the other, that way, it would be as if they were ran at about the same time.
HI Joel , Nice Video. Can you suggest how do we add a Data below the existing data. Example I have a Sap report in a file till 15th of the month. When I run the macro it should only paste data after 15th till date in the sheet.
Hi Abhishek, perhaps you can try the following flow process when building your sub procedure:
After you generate SAP report, open Workbook with existing data -> Identify max value of date column within existing data -> Filter date column of SAP report to be greater than max value of existing data date -> identify last row of existing data -> paste in data starting from row number (last row + 1)
Hope this helps!
Hi Joel, thanks for this great knowledge sharing. Does Epicor have the same feature to record script like this in SAP?
Hello! I have not used Epicor before. So I'm probably not the person to advise you on this.
Thanks Joel! Your video is straight forward and explained easily. I am unable to find the "SAP GUI Scripting API" reference noted in your video at 2:56mins, so do you know where or how I can obtain please? Cheers heaps!
I do have variations of the references being "SAPGUI ApiHooker", "SAPGUI LSAP 1.0 Type Library", etc. Are these the same Joel?
Hey N, those variations are different. You can use the browse button and search for sapfewse.ocx in your SAP directory.
I made a step by step video on how to enable it that you may refer to:
ruclips.net/video/7Rxh10Kt5v4/видео.html
Hopefully it will work for you.
@@JoelTing thank you so much Joel! I will give it a try when i am back in the office next week. Fingers crossed and appreciate your help!
hi joel this is a great tutorial. may I ask, if the Script Recording and Playback option is not available, what should I do? do I need to raise a ticket with our IT to have access with this? or is there a simple way to enable it? hope you answer my question! thank you!
Hi there. Sry for the late reply. If Script Recording and Playback option is not available, it is most likely that it has been disabled in the server. Only way to have it enabled is via IT or your SAP administrator.
Hello Joel, thank you very much for the video! It has helped me a lot!! I wanted to ask you, is it possible that I saved my script with the "save as" window, since as you know, it stops recording and does not record when I save the file. I need to save it that way so that the file is not modified once I download it
Hi there. I'm glad it helped you in some ways. The reason it's stop recording is because it's no longer part of SAP GUI already. There's some workaround using key strokes but sometimes it will break. Can you check if the settings "Show native Microsoft Windows dialog" under "Accessibility & Scripting" have been unticked. If it is already unticked, then the next alternative would be to try to export it in text format, then read the text and pass the data into Excel. Usually exporting in text allows SAP to continue the save process within the SAP GUI environment.
Hi if "script recording and playback"in SAP is not enabled, I can still use the excel to control SAP or no way??
Hi, if the script recording and playback is disabled by admin, there's no way to control SAP with scripts.
Hi Joel, when i run the VBA script. it throw run time error (Method Item of object "ISapCollectionTarget" failed), any suggestion? Thanks.
Hi there, can you share which line of code is giving you the error?
@@JoelTing after i click 'debug', it highlight this line: Set session = objConn.Children(0)
Hey Joel, I tested your code, but got a runtime error 438. It seems the objects aren't compatible, no matter what I record. Any thoughts on why this is happening?
Hi there. Are you able to record? Before you run the code, have you enabled the SAP GUI Scripting API under reference?
Hi, I'd like to select multiple variables for ex: US, DE, AU etc.. in one go, what's the code to select one row after another and once it reaches last row the script should stop. Can you please help ?
Hi Dayanand, if you would like to select one after another, you can use loop for it.
For n = 2 To lastRow
'Action to be done
Next n
Note: n is the row number for where your first data is
Hi. Is it possible to connect to sap and run recorded macros with office scripts?
Hi there, sorry for the late reply. My understanding is that office script is not able to work directly with SAP.
Hi Joel, I am getting "Complied error: Invalid outside procedure" And it looks like the step stops at the Options Explicit line. With the word "Explicit" highlighted. Sorry for all this trouble.
Never mind. I spelled options instead of option.
Hi Joel, i am facing the problem that where my sap not able to open the xlsx file, any idea for that?
Hi Jason, may I know what you mean by SAP not able to open xlsx file? It cannot export the spreadsheet?
Hi Joel, I want to download the automated report from ZM45, can you help me on that ?
Hi there. Is ZM45 a tcode? If it's a tcode, it seems to be a T-code customized for your organization. The steps of recording and developing the automation should be quite similar as what you see in the video, unless your organization disabled scripting for SAP.
Hi Joel, thank you for your video! Would like to ask If I can not find ocx file from reference library, may I know what should I do?
Hi there. Usually this file is installed together with other SAP software. May I know if you are accessing SAP via citrix? Is the directory "C:\Program Files (x86)\SAP\FrontEnd\SAPgui" available in your computer?
@@JoelTingsame error for me. As I mentioned in another comment I have SAP installed on my system.
Hi Joel, thanks for your video. Please tell me how do i make schedule of this VBA scripting so that it will get executed automatically at a certain frequency without any manual interference
Hi there. 1 option is to setup Power Automate to trigger the flow and have the flow run the macro in your Excel. However, I do not really recommend this as the automation usually do not covers the logging in of SAP. Having automation for SAP logon may open up to security risk as you need to have your username and password stored securely.
Hello Joel, thanks for video. Not so many people are giving lessons on Sap automation. Great job! I have a question, how can I automate the weekly extraction (i.e from last week Monday till this week Monday) that I do every Monday.
Hi Gio. Thanks! If you would like to automate, one of the way to do it is to use the task scheduler, where you can set it up to run Excel and run the specified SAP Script every Monday. For the SAP Script, you just need to push in the latest date with the Now() formula and the start date to be 7 days/8 days less using the DateAdd method. Then, you will be able to run the report with the date parameters that you require.
@@JoelTing Thanks for coming back to me. Task scheduler part is clear. Im not sure about the SAP part. Could please somehow show it? If we take the steps as you showed in this video instead of Path and Country name I can have dates there. One will be always Monday and the other Monday - 7 days. Is this what you mean?
@@giogobronidze7788 That's one way to do it, to have the field prefilled.
Another way to go about is just input the date based on your automation schedule. Let’s say your task is scheduled to run on 17th Jan, which will generate report from 11th Jan to 16th Jan. What I can try is to input the fields using Date() function, which will return the date of the day itself.
objSess.findById("wnd[0]/usr/ctxtDateFrom").Text = Date() - 7
objSess.findById("wnd[0]/usr/ctxtDateTo").Text = Date() - 1
If at the time of running the script, it is on 17th Jan, the above 2 lines will be filled with 11th Jan and 16th Jan respectively.
Hi Joel, What to do if I don´t have the sapfewse.ocx on my desk and have a company laptop were I can´t install anything.. Are there a work around
- Lasse (Denmark)
Hi Lasse, unfortunately, if you are not able to find the ocx file locally, most likely you are connecting to SAP GUI remotely, maybe via Citrix. In that case, I'm not aware of any workaround to execute SAP scripts via VBA. Not able to try it from my end, but you may try running scripts using Python or Power Automate if it's already installed in your company laptop.
@@JoelTing I found the file (windows search in all folders!) - In guess that I need new glasses in 2023.. but thanks for the support so far and looking forward to use the function for download of data ..
Getting a run time error "614" "the enumerator of the collection cannot find element with the specified index. While using the debugger it comes while trying to execute:
Set objConn = objGui.Children(0)
Set session = objConn.Children(0)
any help would be appreciated
Hey Justin, from my understanding, this error usually arise due to either 1 of the following reason:
1) your SAP is not logged on; or
2) Scripting have been disabled by your company's administrator.
For the 1st reason, you just need to make sure that there's an active session for SAP and it is logged on before running your script. If it's due to the 2nd reason, you need to get your company's IT to enable it for you.
Hi Joel thanks for the video! is working! Is it possible to run the button and have the data downloaded in the same excel?
Hi there. Glad that it is working for you. Yes, it is possible. You can try to read the newly exported workbook, then copy all the data into one of the worksheet in your current workbook using vba.
Hi Joel, do you have a video showing how to upload data into SAP using VBA? I have this repetitive task of loading data and don’t want to use LSMW. Please assist
Hi there, apologies for the late reply. Unfortunately, currently, I do not have a video showing that. The concept should be similar with what you see in the video. Record the script of the process of you loading your data, then use that script as a base in VBA and modify base on your needs.
I've got an error with User-defined type not defined pointed to objConn AS GuiConnection. I don't know what I can do
Hi there. Have you enabled SAP GUI Scripting API under "Tools" -> "Reference"?
Hi Joel, I want to copy paste a particular field value and paste it to excel cell. I have to do this multiple times can you tell how this is done.
Hi Kunal, that is possible, you first need to identify the field ID in SAP that you are trying to copy. You can do that by clicking on it then hit enter and trace back the field in the script.
Then, just set the value of the specified Excel cell to the field value.
Hi Joel, what can i do if i dont have the reference SAP GUI SCRIPTING API activated and when i look for it in the browser doesnt appear?
NIce explication.
You can check out this video to check if you can look for it in your folder.
ruclips.net/video/7Rxh10Kt5v4/видео.html&lc=Ugx_ZXLVMeYmkqr28Zl4AaABAg
Usually, you can find the file (sapfewse.OCX) in C:\Program Files (x86)\SAP\FrontEnd\SAPgui
How do you use this with SAP BI ( online)? We have a broadcaster function on our company SAP. To extract data to be used in Excel, we use a hyperlink which takes us to a prompt window and from there we choose the month for which we want to extract data for and the query presents itself in table format online. Then we export it to excel and work with it in excel. My question is how to automate this? How to get multiple reports set on broadcaster with dynamic prompt? Is there a way?
Hi Tara, SAP Scripting that is shown in this video only applicable for SAP GUI.
For SAP that works using a browser, you may want to explore web browser automation with Power Automate Desktop or Python or UI Path.
The file path to save the Excel is not getting recorded how to change the dialogue box so that it gets recorded by macro, please help
Hi there, may I know which Tcode that is giving you this result? Also, can you check if the option "Show native Microsoft Windows dialogs" is unticked under Options > Accessibility & Scripting > Scripting?
Hi Joel, nice presentation!
I was wondering if there is any way to easily copy the data from the exported file into another specific workbook? I would like to have multiple automated data exports from SAP into a KPI excel document. This also means copy pasting the data below already existing data. I was looking to creating a Excel Macro that would just copy the data and paste it below existing data, but i am worried that the source excel file might change "name" every time i export new data.
Hi Jeppe. Thanks! Yea, it's definitely possible. You can use vba to open the workbook exported, copy the data in it, then open the final destination workbook and paste it there. Usually that's how the flow works if you want to put the data in an existing workbook. When you say source excel, you mean the one from SAP right? What you can do is to let VBA handle the naming, then it can pass it on to the next step.
@@JoelTing Yeah exactly my thoughts! However, the data that I export automatically opens an Excel file with the name "EXPORT" from which I then try to copy the data into another document. However, the piece of code: "Set wsCopy = Workbooks("EXPORT.xlsx").Worksheets(1)" seems to bug as the "EXPORT" document doesn't open automatically when this code is written right after the SAP extraction code. When the SAP extraction code is run individually the "EXPORT" document opens just fine. The copy code also works fine by itself. I have tried with a wait command in between the two. Do you have any idea why this happens?
Thanks for the response!
Hi Jeppe, what I noticed is that the workbook is actually not opened upon export. Rather, it is opened at the end of the process, which means that however long you put in the wait function within the process, it wouldnt work.
Instead of "Set wsCopy = Workbooks("EXPORT.xlsx").Worksheets(1)", you can try working with the Workbooks.Open() method (ie Set wbCopy = Workbooks.Open("EXPORT.xlsx")). Then, you work with the worksheet in it (ie Set wsCopy = wbCopy.Worksheets(1)). This will work regardless of whether your workbook is open or not. See if this way works for you?
Thank you Joel , this is really useful. can you tell me how to give more than one value as input . Can we give with comma separated ?
Hi there, it really depends on how you can to set this up. It depends on how SAP takes in the values. If it's a list, you would be better off having your value in list format already. If you need to split your value with comma, that can be done with VBA as well before passing the values into the script.
Hi. This is working in my test Environment but not in Production. The error is highlighted in Set session = objConn.Children(0). Please help. The Script Recording feature is not activated in our Production only in Test. Thank you.
Hi Fatima Perez, if scripting has not been enabled for users by the admin, then we will not be able to work with SAP script as it will not be able to communicate with SAP GUI.
This worked for me! :) my tcode only allows one year at a time. How do I get it to run the same report a second time with a different year nonstop. I know I can press the button twice and do the years separately but is there a way to loop the script but with different parameter second time?
Hi Vi Ha, you can contain your recorded SAP script in a loop, repeating the steps and inputting the list of years one by one during every run. I'll be working on a video soon explaining the concept in probably a few days. I'll let you know once the video is published.
Hi Joel, I'm wondering if you could advise me on my problem.
I work with SAP in Master Data, I frequently have to do material investigations: checking a material in CS15, then checking each returned material in MM03 - Basic Data 2 to see if it has Document Number, I then have to check Additional Data - Document Data to see if that has a link to a .pdf for the document drawing.
Using what you have shown here would it be possible to run a script which iterates through the list of material numbers to check and then output the content of the fields I have mentioned?
I have hundreds of materials to check and each one returns around a hundred results to then check for document drawings, as you can imagine this takes forever.
Any advice would be great.
Hi there. The first part of the process sounds doable with scripts. But for the checking of PDF, let me check some possible solutions from my end. I'll get back to you again.
@@JoelTing Thanks for looking into this, I have managed to pretty much do everything I was looking to do with a little playing around with GUI recording and writing some additional vba script, wasn't sure I'd be able to do anything as my excel has no sapfewse.ocx (scripting API), but setting SaGuiAuto, Connection etc as Objects worked fine.
While I cannot get a direct link to the .pdf or automate printing it, I can retrieve if the material has one.
Now I can just look through my excel file, see which materials have a .pdf and reopen it to print it out.
The time saved by automating this data collection is ridiculous.
I'm definitely going to implement this as much as possible, the less time I spend repeatedly looking for the same information on different materials, the better.
Thank you for the excellent tutorial, and for looking into this issue.
Hello. After some trying from the SAP server I'm in, I don't really use the Document Data to store anything. However, I believe it is a field where we can extract the value from. So, if the link is actually the field value, we can actually pull that link out and put it in a table in Excel for your ease of reference. If you are interested to learn more, feel free to reach out to me via email and see if we can schedule a call on this.
Without sapfewse.ocx (scripting API), are you able to connect Excel to SAP? If that's the case, it seems like your SAP Scripting API is already enabled. Usually if that's not the case, I will not be able to run my script at all from Excel.
Hey Joel!
I'm getting an issue when uploading this file on SharePoint!
When I give the address to replace the file on my local drive, it works fine.
But when I'm giving the address on SharePoint or OneDrive... It keeps on showing the same file! That file on SharePoint is modified easily when I do it manually!
Hey tallalaazmi1287, are you pointing the address using https and it's not a local drive? I'm usually just work with local file. For sharepoint and onedrive file, I will have the file synced so whenever it is being replaced, it can be uploaded and replaced in the cloud as well.
Hey Joel!
Thank you for the video; it is very helpful. I do have an issue however when attempting to run the code, I keep getting a "Run-time error '614' "The enumerator of the collection cannot find an element with the specified index""
Do you know what would cause this and how to fix it?
This happens on the 4th line of code "Set session = objconn.Children(0)"
Hey Joshua, from my understanding, this error usually arise due to either 1 of the following reason:
1) your SAP is not logged on; or
2) Scripting have been disabled by your company's administrator.
For the 1st reason, you just need to make sure that there's an active session for SAP and it is logged on before running your script. If it's due to the 2nd reason, you need to get your company's IT to enable it for you.
I'm having this issue also. It's due to my company policy, unfortunately.
@@JoelTing If it's disabled by a company/administrator one would likely also see the "Script Recording And Playback" grayed out when trying to record.
Hi Joel, thanks a lot for simple and useful lesson. I am facing a Run time error 619: the control could not be found by I'd. Can you please help here. Thanks a lott
Hi Monika, if you troubleshoot it step by step, are you able to identify where does it go wrong? Usually this happen when the step it is trying to execute is not the same as the time when you record the macro.