Splitting data into multiple tabs with VBA - Interacting with Exported Excel Spreadsheet from SAP
ฝัง
- เผยแพร่เมื่อ 27 มิ.ย. 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!
ko-fi.com/s/c366da7e71
In this video, I will share how you can interact with the Excel spreadsheet exported from SAP and apply some steps to format and edit the spreadsheet based on your likings. All these can be done within a single process, which can saves you a lot of time.
Timestamps
00:00 Introduction
00:19 Overview of SAP Scripting and End Goal
00:56 Using function to obtain file directory
01:17 Building process to split exported data into multiple tabs
05:01 End result and Conclusion
#SAPScripting #ExcelSAPAutomation #SAPExcelIntegration #SAPVBA
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. We do not recommend the use of SAP Access from any particular provider.
WORKING FILE
docs.google.com/spreadsheets/...
MY FAVOURITE GEAR
💻 My Personal Laptop, Legion 5 Pro - amzn.to/3nZrn1I
✈️ My Travel Companion, Macbook Air M1 - amzn.to/43jLLuE
📷 My Camera Gear, Sony ZV-E10 - amzn.to/40T7uIj
🤝 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:
ko-fi.com/s/c366da7e71
Keep it up Joel! Thanks for the videos!
Thank you for the kind words!
This is massive helpful videos for my job. Thank you very much for your sharing.
Glad it was helpful!
Joel, you are the man
amazing!
Love these videos
Thanks you!
Excellent💯💯👏👏👏👏 I really love your sap GUI scripting videos. Please do more videos on SAP GUI SCRIPTING
From :India 🇮🇳 Hyderabad
Thanks for your kind words!
Thanks
Thank you very much, nice info!! I've subscribed you, keep the spirit for the next videos
Thanks for the sub! I'll try my best to work on more content.
@@JoelTing Sorry Sir, any sample script to combine sap macros and excel macros? because I want to export excel from sap, then automatic processing from excel macro at once..Thank you
@@titoyudatama5361 Hi, you can refer to the script used in the video in this workbook:
drive.google.com/file/d/1-DGsD1jSPlpNZLX845Isuc03mg_edtfR/view?usp=sharing
@@JoelTing I'm so thankful for your reference
Nice stuff Joel 👍👍 I am working on Sap ECC 6 version and now working on a Journal posting template in FICO. Any suggestions ? I can't use manual postings as an option as we post entries with more than 700 - 800 rows ...
Hi Abhishek, apologies for the late reply. I believe this can be done by using loop to help with the posting input. However, I do not have access to FICO modules, so I'm not able to test out from my end.
hi Joel, thank for your sharing, I found your video is really helpful for me.
would like to ask, if i extracted data from SAP, and my excel is automatically open. If it is possible, i want to continue in the workbook to remove the apostrophe symbol from the data number display from SAP?
Hi there. You can continue directly regardless of whether your Excel is opened. You just need to use the workbook.open method to bring that newly saved workbook into your script and continue working on the next your data processing steps within the exported workbook.
Hi Joel, can we automate the system to capture the SAP screen prints to excel file?
Hi santhosha, I have personally havent done that before. While researching, I found this video which might be helpful to you. But it prints to a word document instead of Excel.
Thanks for the video. I see that you have the option to get the extract in xlsx format but I dont. You have explained in of your videos but I dont really have that option. Is there something else I could do ?
Hi Gio Gobronidze, you can try right click on anywhere of the table in SAP and click on "Spreadsheet". It should give you an option to choose the spreadsheet format that you can export. The unavailability of spreadsheet options seems to be due to the "Always use selected format" option being enabled.
@@JoelTing "Always use selected format" its frozen in my case and not enabled. Looking at all available formats but I dont have xlsx, I do have xlm, MHTML
@Gio Gobronidze I see. If that's the case, you can try using MHTML. I tried it from my end and the method work on MHTML file type as well. I seems to be able to treat MHTML as a normal Excel workbook. And by the end of the process, if you want to convert the MHTML to XLSX, you can simply do that by using the save as method in VBA.
Thanks Joel
Hi Joel, please make a video on how to automate drag and drop using sap gui? My problem is whenever the box in FB03 appear where I save my attachment, hitlist is not working so I have to maually click the attachment and hit enter.
Hi Olly TV, I do not have access to FB03 to try out the process of drag and drop or saving attachment at the moment. Let me try out to see if I can simulate something similar in my test SAP server.
Make a playlist of SAP FICO module tutorial
Hi, thanks for the suggestion. I currently do not have access to the SAP FICO modules, so I'm only able to work with some basic concepts that we can apply when working with SAP GUI scripts.
I'll put that into consideration once it is financially viable for me to subscribe to an SAP server which provides me with all the SAP FICO modules. Thanks again!
@@JoelTing thank you
Hey joel how can we script if we want to take a Screenshot
Hey Gourav, I personally do not use the screenshot function myself. You can try check out this video: th-cam.com/video/W_J-9t_mWDU/w-d-xo.html&ab_channel=CsongorVarga
It seems to be working for most.
When exporting out of SAP, the excel file will automatically open after it exports. I need my script to wait for the export to open, and then to close the newly opened export file. If I try to activate the export file immediately following the command to export the file in SAP GUI, I get a "subscript out of range" error. I thought maybe I could loop the activate command until it stops erroring (while the export.xlsx file is opening) but that causes excel to crash. I also tried using a delay, using application.wait but the download hangs no matter what time I input into the delay; 5 seconds, 15 seconds, it doesn’t matter. Manually, when exporting, it opens in about 3 seconds. Having said that, when the error pops up, it triggers the export to immediately open. Seems like when scripting, SAP doesn’t have the ability to recognize the export. When I step through the code manually, I don’t have any issues activating the export after it downloads and automatically opens. I’m not sure what to do here, any suggestions?
Hi M4KE4W1SH,
I faced the same issue as you before this. I found out that only one of the exported spreadsheet will automatically open by the end of the process and not the end of the export. Instead of putting in a delay/activating it, you can try the open method to open the workbook based on exported file path and assign it to a variable. It may seems you are opening an already opened workbook, but this works for me and everything went through smoothly. Only at the end of the process, the workbook will be opened automatically again, but all the amendments and formatting would have been made.
how to exit from sap page?
Hi Chola, what do you mean by how to exit from SAP page? Closing SAP entirely?
How can I close the exported excel file ? Because it opens automatically after the extraction .Any tips
Hi, there's a workaround I found, but it involves quitting the whole Excel application. You can add the following line at the end of your script.
Application.Quit
You can refer to this video where I briefly touched on this.
th-cam.com/video/G5qc2gcpyW4/w-d-xo.html
thank you sir for responding .I tried it ,but it just close the macro file not the extracted one.please any other solution cuz i need to to complete my project.
@@user-de2mc5jq8q you can try some other methods suggested in stack overflow here:
stackoverflow.com/questions/40729968/exported-file-opens-after-macro-completes-unwanted
Hope this can resolves your issue.
Very much appreciate your content! It’s made a big difference in my life
Please share the file
The file is available here and in the description box:
docs.google.com/spreadsheets/d/1-DGsD1jSPlpNZLX845Isuc03mg_edtfR/edit?usp=sharing&ouid=102044540683497440975&rtpof=true&sd=true