Splitting data into multiple tabs with VBA - Interacting with Exported Excel Spreadsheet from SAP

Рет қаралды 12,540

Күн бұрын

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/
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.

Пікірлер: 46

@JoelTing 10 күн бұрын

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

@AmirHazwan 2 жыл бұрын

Keep it up Joel! Thanks for the videos!

@JoelTing 2 жыл бұрын

Thank you for the kind words!

@worapon 2 жыл бұрын

This is massive helpful videos for my job. Thank you very much for your sharing.

@JoelTing 2 жыл бұрын

Glad it was helpful!

@aperxmim 11 ай бұрын

Joel, you are the man

@TheJaebeomPark 4 ай бұрын

amazing!

@Xsessive182 2 жыл бұрын

Love these videos

@JoelTing 2 жыл бұрын

Thanks you!

@naiduvikas202 2 жыл бұрын

Excellent💯💯👏👏👏👏 I really love your sap GUI scripting videos. Please do more videos on SAP GUI SCRIPTING From :India 🇮🇳 Hyderabad

@JoelTing 2 жыл бұрын

Thanks for your kind words!

@titoyudatama5361 2 жыл бұрын

Thank you very much, nice info!! I've subscribed you, keep the spirit for the next videos

@JoelTing 2 жыл бұрын

Thanks for the sub! I'll try my best to work on more content.

@titoyudatama5361 2 жыл бұрын

@@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

@JoelTing 2 жыл бұрын

@@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

@titoyudatama5361 2 жыл бұрын

@@JoelTing I'm so thankful for your reference

@vijaygadhavi5122 2 жыл бұрын

Thanks

@abhishekchavan9651 2 жыл бұрын

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 ...

@JoelTing 2 жыл бұрын

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.

@limadidaslcy2993 6 ай бұрын

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?

@JoelTing 6 ай бұрын

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.

@ollytv3541 2 жыл бұрын

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.

@JoelTing 2 жыл бұрын

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.

@santhukail 2 жыл бұрын

Hi Joel, can we automate the system to capture the SAP screen prints to excel file?

@JoelTing 2 жыл бұрын

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.

@giogobronidze7788 2 жыл бұрын

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 ?

@JoelTing 2 жыл бұрын

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.

@giogobronidze7788 2 жыл бұрын

@@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

@JoelTing 2 жыл бұрын

@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.

@giogobronidze7788 2 жыл бұрын

Thanks Joel

@M4KE4W1SH 2 жыл бұрын

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?

@JoelTing 2 жыл бұрын

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.

@muhammaddanish9149 2 жыл бұрын

Make a playlist of SAP FICO module tutorial

@JoelTing 2 жыл бұрын

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!

@muhammaddanish9149 2 жыл бұрын

@@JoelTing thank you

@gouravdeshmukh0 Жыл бұрын

Hey joel how can we script if we want to take a Screenshot

@JoelTing Жыл бұрын

Hey Gourav, I personally do not use the screenshot function myself. You can try check out this video: kzfaq.info/get/bejne/jcV6YJylxNO7dIk.html&ab_channel=CsongorVarga It seems to be working for most.

@user-de2mc5jq8q Жыл бұрын

How can I close the exported excel file ? Because it opens automatically after the extraction .Any tips

@JoelTing Жыл бұрын

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. kzfaq.info/get/bejne/fZuhlpWYyNbdh2g.html

@user-de2mc5jq8q Жыл бұрын

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.

@JoelTing Жыл бұрын

@@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.

@MrSexualnapalm219 4 ай бұрын

Very much appreciate your content! It’s made a big difference in my life

@cholakarthick3304 2 жыл бұрын

how to exit from sap page?

@JoelTing 2 жыл бұрын

Hi Chola, what do you mean by how to exit from SAP page? Closing SAP entirely?

@DataPrasadS Жыл бұрын

Please share the file

@JoelTing Жыл бұрын

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