Automate SAP data extraction to Excel with Power Automate Desktop - No Coding Required.
Вставка
- Опубліковано 31 лип 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.
An IT colleague of mine mentioned about his automation process on Power Automate and thinking 'omg this is absolutely nuts!' It is so user friendly and it is easy to set up your own automation process, even if you are still a beginner.
In this video, I'll break down exactly how I use Power Automate Desktop to automate the process of extracting data from SAP.
Timestamps
00:00 Introduction
00:23 SAP GUI Scripting Basics & Recording Tool
02:26 Build a new flow in Power Automate Desktop
03:16 Obtain User Input using Message Box
03:58 Handle event when users click on "Cancel"
04:35 Copy and Paste SAP GUI Script into "Run VBScript" Block
05:14 Attach to Excel Instance Exported by SAP "Worksheet in Basis (1)"
05:35 Save Excel file as .xlsx in desired file path
06:14 Generating unique file names with current date and time
06:59 Create Message Box to inform users after flow is completed
07:10 OPTIONAL: Error handling for VBScript Error
07:35 Completed flow test run
08:54 Summary/Recap of SAP Data Extraction Automation
#powerautomateSAP #ExcelSAPAutomation
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.
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. - Наука та технологія
Hi everyone,
If your encountered syntax error, be sure to check if your recorded vbscript contains % symbol. Make sure you substitute % with %% for it to work.
Hello Joel thank you so much. It worked. I'm so very greatful for your help!!!
Hello again....our SAP is set to automatically stay open when we export Z tables. If you have access to z tables I would love to see a demo how to get the excel file to close to create a flow using the sap VBA script. I can only run the script right now I'm having an issue incorperating with excel it saves as an HTML file. Appreciate any feedback. I export many inventory variant/ layouts daily ...then run the export export through a power query connecton to generate final reports
Hello Lisa. Do you mean after the export, it gets stuck because your excel file stays opened?
Since you would know what name will be the Excel file when you are exporting, you can try using "Attach to running Excel" action to create an Excel Instance. After that step, use the "Close Excel" action to close the instance that was created in the previous step.
If you are running the export through Power Query, you can have Power Automate to open the workbook and run a Macro which will automatically refresh the data sources.
That`s insane man! Thank you very much, I was almost about to give up
hi joel! thank you so much for doing this tutorial.
i did the same thing. when i try executing the attach the running file, it doesn't recognize the title "worksheet basis (1)" and i get an error.
Amazing delivery - simple, concise and well executed! Thank you Joel
This is amazing. Thank you, Joel, for your kindness in sharing this with us! I'm new to Power Automate and am looking forward to automate my SAP data extraction like the one you did.
Amazing. I'm new on Power Automate too. This is an amazing vídeo
This is sick bro. Didnt even realise there was a macro recorder in SAP and the fact you can link it to Power Automate is very clever. I can think of so many ways where this could be used. Brillinat stuff!
Me too so many possibilities...less monkey job more clever work
But it seems like the Script Recorder is deactivated for me 😥
@@patriks.7815 Same with me, does anyone know how to activate it?
Admin role can open
Love this video, greetings from Spain! Keep working on this channel, your content is gold!
Muchas Gracias!
Joel, thank you so much for sharing your knowledge with us. The universe will see the good you are doing!!!
Was looking for a second bell icon to double subscribe. Fantastic use case; great Job!
I really like your video regarding power automate desktop, and I think other people too.
I hope you can keep creating it if you have time !
Wow, just wow. The possiblities ... I am blown away.
This is a very good summary! Much appreciated
Thanks for this video and detail narration on SAP and power Automate features. Its Brillant
Thanks for the nice tutorial! It is easy to follow and helped me a lot.
This is awesome. Thank you so much for taking the time to share your knowledge
It is my pleasure. Thanks for your kind words.
You are an excellent tutor, sir. Thank you for the video!
Glad it was helpful!
Hi Joel,
Are you planning on doing more Power Automate Videos? This one is amazing and it helped me to bring Power Automate to my workplace. So your video is responsible that I am now the key User for Power Automate in a Company with more than 4000 employees. We already automated tasks which would need hundreds of hours manuel work every day. So you had a huge impact with this video and I hope to see more of you :)
Hi Dennis. Thanks for your kind words. I'm really happy that this video helped you in some ways. Apologies for not uploading any videos for the past few months as I had been quite busy with my current role in my company. I'll try to upload more videos consistently.
I have just posted a video about automating the SAP logon process prior to running the scripts and how to handle your username and password securely. Feel free to check it out. Hopefully it will compliment the process that was explained in this video.
Thank you once again for your support.
You are the greatest! Thank you very much. Really useful.
i am grateful. I created this one button in my excel which extract from SAP and copy&paste to excel...when i looked at my colleague's template, it seemed like very complicated coding skill hahahaha in the end, that was not the case
Hi Degi Gerel, hahaha yes! I'm not seasoned coder as well. I just make the script simple to fulfill my own personal use to save time and I had a lot of fun trying to play around with it.
Wow amigo this is really awesome!!! i got some ideas for my daily work to test it and implement it. Thanks for this Great tips if you happen to share some other scenario we would really appreciate it!!!!
Hi Joel,
Thanks for the usefull tips.
Thanks for the nice tutorial!
You are great! Great solution 👍.
Wow this is awesome, but so above my non-tech brains. I'll need to watch it perhaps at least a hundred times before I'll get it all :)
Gracias, muy buen video. Se abren grandes expectativas para automatizar algunos procesos.
exactly what i want! thank you so much
Excellent video. Thank You!
Glad you liked it!
Great training. I liked it very much. I put it into practice. I had sytnatx error in the code, I found the solution on the Internet. Just double the "%" sign.
OMG... SO SO SO SO Thank you.... It really helped a lot.
Hey Lee Jason. Glad that I'm able to help 🙂
Thank you for sharing this.
very informative, thank you
This is nice! Thanks and subscribed!
Thanks for the sub!
I'm looking forward to trying this
It's amazing!!!!
Very Nice !!
Amazing 👏
Ualllll. That is a good tutorial.
Loved it..!
Subscribed bro. I am also in MNC and doing monthly reports and my boss give me via email the SAP extracted excel file as "Databases". I need to do alot of manipulation of these extracted "Databases" to fill in various other files. Seems like this RPA gives me an idea on how to start simplying my tasks.
You can record your manipulation actions on Excel. Read about "Excel Macros and VBA" it will solve your problem.
Fantastic!
Olá, muito bom parabéns!
Excellent. Thank you for the vídeo.
I love you Joel!!!
Great video
great job....im doing it with vbs directly and task scheduler, but this looks much profesional...i bookmarked this!
Hey Javier Perez. Thank you very much and nice to meet you! 😀 Warmest regards, Joel Ting
gratefully!!!
And here is that other video I promised ua-cam.com/video/uEgJLLHBlZU/v-deo.html
This one automatically organizes music album files into their respective and automatically created folder. And it does this for all the alubms. So amazing to see it execute and do it all automatically. Hopefully I have this walk through video for other users and anyone who is interested in Windows Automation. Let me know if you get any questinos that I can help with.
Cool, thanks
just what i was looking for, you should make also a video on how to auomate the logon experience on sap with PA
Hi there. I have a video talking about this, but not sure if it fits what you are looking for.
ua-cam.com/video/C_7UD_l-zwA/v-deo.html
awesome, need more videos like these, like for example mas uploads on SA38 t code
Hi XSessive, thanks for your suggestion! However, I have no access to the SA38 T-code currently in my company and therefore unable to work on the demo.
Thank you!!!
Good content
Great Work. Can you show how to get data from SAP to Power BI
Hi Joel - your videos are truly amazing and has great content for learning. I did a flow in SAP GUI 750 to modify data in fields using populate text function. But sometimes due to the speed of net the flow continues without the action being actually performed in SAP and this gives error. Which option do i need to choose to ensure the previous steps are completed before the flow can move to the next one. Many thanks and do keep uploading your valuable videos. Cheers!!!
Hi Freeshavacadoo.
May I understand from you if the text are populated within the script or you are using the action in Power Automate to "type in" the text? If it is being populated using the script, it should eliminate the error that is caused by timing issues.
Hi Joel, hope you're well. Your video was very insightful, and has helped me a great deal. Everything has worked! I am however facing one snag. I have to rename/delete the file that I selected as the "attaching to running Excel", which I could be saving extra time on. Do you perhaps have any tips for this?
Hey, sorry for the late reply. Perhaps you can try to add a Save Excel action and use the Save document as mode to save it in the folder that you desire?
Joel, thanks for the great video
One question, how do I use clipboard to copy paste entries in 2 different fields like Company code (100,102)and Period(1,2,3)?
I was able to use clipboard for one field, but how do I store period in clipboard at the se time?
Hi MG, you can try to use copy cells from Excel worksheet to copy your first field, then run vbscript to paste from clipboard in SAP.. After that, repeat with the same action again, copy cells from Excel (this will overwrite your clipboard) for the 2nd field and run vbscript to paste from clipboard again in SAP.
Hi Joel, thank you for the video. I have a simple question. Is there daily auto flow play in Power automate desktop? I couldn't find how to do that..
Hello. You can schedule with the cloud version of Power Automate, start off with a scheduled cloud flow and use the action "Run a flow built with Power Automate Desktop". However, do note that this requires you to purchase a license to use as this is a premium feature.
Detailed documentation can be found in the link here docs.microsoft.com/en-us/power-automate/desktop-flows/link-pad-flow-portal
Hola es posible hacer otro ejemplo con SAP , pero que inserte información ,gracias compañero por el vídeo.
Hi Joel, Thanks for your valuable tutorial. I have one question for you.
From your video, it means that i have to open SAP before i start the PowerAutomate script. Can we create a PowerAutomate script to open SAP if SAP is not open ? Thanks
Hi poor mong. Thanks for watching!
From my understanding, that's possible. You can simply use the run application block to start the SAP front end application and run the script accordingly. The main concern for me is that if you are storing your login details (username & password) in the script in plain text, it would raise some security concerns.
I hope that answer your question.
Hello Thank you very much for the great tutorial.
I have one question, would it be posible with power automate to pull the data extracted and run a excel macro? Thanks.
Hi Carlos. Yes you can, right after the running the script, you can use the run excel macro action to target the extracted xlsx and work on the data. Or better still, you can run the SAP script with VBA/Macro with better control over the sub-process. New video coming up on that very soon.
Hi , thanks for the amazing tips. Would like to know for the SAP extraction is it possible to automate extraction from Tcode SM37 as well?
Hi Leafy, sorry for the late reply. I think the extraction process is similar as long as it is using SAP UI. Just got to be clear with the steps and design the process with either Power Automate or with VBA.
Hi Joel, this is the first video where I was able to follow the steps!! Can you please say what should be the action button in Power Automate when the original SAP report is SAVED in a folder and not just OPENED directly?
Hi there, if you are able to save it in a folder using just by using SAP script, then you don't really need it to be opened and save like in the video. Or do you want to amend something on the report before closing?
Hi Joel! Thanks for the video and the knowledge. For auditing purposes, is it possible to include in the automation process, the recording of the extraction? Or maybe extract printscreens of the parameters that were chosen to generate the report? Thanks again!
Hi there, is a log of what is being passed into the script sufficient for the audit purpose? If that's the case, you can create a text file containing the time of running, the different parameters passed in for the script and the outcome of the script (could be the final file path of the export).
Hi, I would like to know whether SAP auto logon process (the video you posted after this) can be related to SAP data extraction process. I would like to make a flow which start with auto logon and extract data from SAP. Thank you!
Yes. What you need to do is login to SAP using the steps in the SAP logon process, then start the extraction flow with vbscript.
Great Video Joel Ting :) Do we have any option integrating SAP BAPI/ RFC to upload or download data from SAP via Power apps ?
Hi Chaitanya. Thanks! I am not very familiar with SAP BAPI/RFC though. I have not work on it before, so I might not be the best person to advise you on this.
For Power Apps, I am not sure how that would work to download data from SAP. But personally, I think setting up the data with Power App itself may not be efficient as there's some limitation on how it handles data. That being said, I may be wrong as I have only explored basic functionalities of Power Apps.
One question. I'm trying to record how I print some orders. But the UI-button for printing has a different name, because in the window there is the ordernumber mentioned in the window-title. How to solve that? Or how can I handle with different titles in the window?
Hi Rene. Sorry for the late reply. I am not very sure about the printing process from SAP. So far I have not work with Windows UI Automation with Power Automate yet.
Hi joel, thanks for posting great video. I have one question when we are using playback option and recording date from calendar the recorded script got change in the date selection. If i want to make it dynamic i am using the variables declares like%selectedyear%....!!
And date selection in the recoded script is yy.mm.dd format and its default we can't change from user profile. So how to make it dynamic in power automte please suggest something..!!
Hi, you can try getting the subtext of your selected year or month based on what you need. Then, concatenate them together to achieve the end result you are looking for.
amazing video. I am wondering if there is anyway to run the script in background without need to log in SAP ( background job ) in daily timing
Hi Khaled. It's unlikely you will be able to set it up to run in the background entirely. The process can be scheduled using Power Automate Cloud, running the process at a specific timing. The process of logging into SAP can be automated, but it would interrupt the users if they are currently using the system.
Can Power Automate online perform same thing? I cannot find the action section listed in your left screen as selection when using online version and our company doesn't have desktop version. Thanks!
Hi Angela. Thanks for watching.
No, Power Automate Online is not able to perform the same thing. The cloud version uses API to interact with different applications. Power Automate Desktop interacts with the SAP user interface directly without going through API.
Power Automate Desktop and the cloud version serves different purposes in the automation process.
@@JoelTing Thanks a lot for your clarification👍👍
This is what i was exactly looking for. Thanks for sharing great idea. Is it possible to add daily time set up? I want to automate this flow works every day at specified time.
Hi there. In the case that you are using Power Automate, you can schedule it using the cloud Power Automate. Then from there, run the Power Automate Desktop via on premise gateway.
Hi Joel Great video! question: is there a way to add the option to download all months and all years to downlaod a full report formthe same flow?
Hi, it really depends on how you structure your script. Variables can be passed in based on what you want (Maybe you can set it up from period 1 to 12 for example), it will be different depending on your requirement.
Hi Joe, how to revise the script to let the user input the period freely instead of selected from the message box?
Hi Mandy, you can use the "Display input dialog" action to capture user's input as a text. But one thing to note is that user can input any value in this case. So, if they just input an invalid string and that get passed into the script, it would result in error when the script is being ran.
excelente video, podrías hacer un ejemplo pero para insertar información a SAP
Thanks for your suggestions, I'll try to work on it :)
Hi Joel, thank you for the video!! It is very helpful . Have a question, after the loop, when we get the SAP output, is there any other alternative to "Attach to running excel" step ? Iam attempting to work on other excel files, while running the flow and iam hoping to avoid an error, while saving the Worksheet in Basis (1) file.
Hi there. Another workaround may be to change the SAP output into a spreadsheet that you can save the output on a specific path. That way, you can avoid having to deal with Worksheet in Basis (1) file.
@@JoelTing Hi Joel, thank you for the reply. Do you mean recording the output path in Sap script itself, if possible could you please make another video with the above step?
Hello Joel, Firstly, thank you for your great video! Though I have a question about VBA. I copied exact same way as you shown in your video, but my SAP didn't even react a little. Power Automate flow cannot trigger to my SAP at all. Do you have any idea for this problem?
Hi JoonSun, I'm not sure what would be happening there though. Is there any error being triggered when the flow is ran?
Hi, I have the exact same problem, i think it could be due to the licence with rpa functions that are not enabled. But i dont know
Could someone explains to me which is the trigger of this flow? He needs to open power automate app every time ?
Hi Bruna, thanks for your comment.
As of now, there's no trigger for Power Automate Desktop.
After you have created and tested a desktop flow, you can add it to an automated flow in Power Automate (cloud) and setup your trigger there.
Hello Joe, First of all thank you for your videos i had learned a lot! is there any way to run a script that add data to a transaction? not only extract information?
Hi Sebastian Vaazquez, yes, you can definitely run script to add data, and using loops, you can do it in bulk. it should be of similar procedures, identifying the SAP element in SAP GUI and changing those with your variable so that you can do bulk inputs.
I have been wanting to work on a video on this. Problem is, I do not have access to those T-codes which allow for data input. So I'm unable to test it out. I'm trying to work on having a subscription for the full system for SAP.
@@JoelTing Hi Joel thanks for your responce, so the variables should be on Power automate or in the Script?
Mind = BLOWN !!
Sadly, in our company, the 'Script Recording & Playback' option is greyed out. :(
If that's the case, that is being blocked by the company. Then, you will not be able to automate the processes with this method
Hi Joel, thanks for sharing with us. I have a Question, is there a way to record script on SAP for multi windows activity in same tcode?
i had a case when i recorded the script activity, the recording didn't record and it stopped when i move to other window of SAP, can you give me tips about that ? thanks in Advance !
Hi. Yes, you actually can connect to multiple windows or in SAP terms, I believe you are referring to different session. In that case, you need to assign the different sessions to different variables, then depending on which session that you want to connect to, execute the script accordingly. Below is a quick example:
Set session1 = connection.Children(0)
Set session2 = connection.Children(1)
session1.findById("wnd[0]"/tbar[0]/okcd"_.text = "SE16"
session2.findBYId("wnd[0]"/tbar[0]/okcd"_.text = "KO01"
So in this case, 1 window will enter SE16 in the navigation bar and the other window will enter KO01 in the navigation bar.
Hi. Good. I have used background with schedule and running with selected variant and layout. And every morning goto SMX and download. Finally I found way to automate download by LSMW and create TCode for that and scheduled. I have tried in my QAS server. It's works. Only condition if XLS it has limited row and colours only downloaded. To avoid that i have downloaded in the .TXT file. I have done those process to avoid SAP connector to my Qlik BI. In Qlik i have created cleansing APP to remove noise of the data like page breaks, header and fooders. Now cleansed data ready to my Qlik sense BI.
Hi Jayasri, thanks for watching.
I'm not very familiar with LSMW and Qlik BI. I do not have access to LSMW. The access I had is only the T codes that I used for generating reports. After the extraction, usually the data will be fed to my Power BI workflow for data cleansing.
I believe LSMW may lead to a more efficient data extraction process. Thanks for your insights. 🙂
@@JoelTing thankyou friend. Actually LSMW is the Tcode upload the data to SAP may be called as data migration from legacy to SAP. But i was found that and created LSMW for TCode SMX. This will do my manual download work as per scheduled timing. Aby way thankyou for your KT.
Hi Joel, can I use power automate to extract a single report which is a mix of Ekbe, ekpo, eban and lfa1 reports in SAP
Hi Vinay Kumar, I think you can.
Theoretically, you just need to have the script to extract different reports one after another, then combine them either by working on it via actions in Power Automate, or triggering a macro that was previously prewritten to combine the reports that you have mentioned.
Hi Joel, thanks for this awesome tutorial. Just wanted to ask, can this work for someone else's SAP software, which is running on a different laptop? It will be great to have a response from you regarding this. Thank you!
hi pukhraj suthar, I have not done that personally before. From my understanding, that's workable as long as you can share the flow with the other person. Usually not much is change is needed.
Excellent tutorial, very straightforward. I was able to automate the Spreadsheet I needed and then link it to PowerBI.
My only issue is that I can't seem to update the file. Everytime I ran the program I always get the same information (from the day I first created it). Even when I use the date selector I get the same result. I have edited the scrip to follow the date DD.MM.YYYY that I input when I run it, but still it always pulls the information of the same date 03.10.2023, which is the day I created the script.
Any advice?
Also, I don't see SAP running automatically when I execute my flow. It's like nothing is happening.
So it's like the .export file is always the same, and the file I create it's always only a copy of that export file, the data does not update with the date
Hi Eric, you can try going through step by step to debug what went wrong. Ie. is the date being input correctly captured and passed into the script? Is there something overriding the variable before it is being passed into the script?
Hi Joel, great video and great explanation. Thank you so much. Yet, Even if I can create a new excel file, it comes empty every single time. I tried different script, the result did not change. I assume there can be multiple reason about that but what do you suggest mostly?
Hi Engino, are you exporting with XXL format? or are you exporting into other spreadsheet format? It really depends how you have set up your configuration during your export. If you are exporting in XLSX format, you may even just incorporate the export steps into the vbscript directly without going through the open Excel, save Excel steps.
@@JoelTing That was my problem! I did not notice that the format you used in the video. Now, it's working. Thank you so much!
Thanks Joel for sharing this useful information. Does it mean the SAP has to be log on before the script can execute? What if SAP is not log on, can we automate the SAP logon? Thanks
Hi Kia Ming, in the video, it has already been logged on.
Based on my understanding, It is possible to automate the logon by starting to record the script before you login, and run the script as a vbscript block.
However, you may want to be aware that the username and password are passed in as a string, which may pose a risk of exposing your credentials.
I personally have not done it before but I found 1 potential way to mitigate the risk is using Azure Key Vault.
You can check out the video in the link below for using sensitive text in Power Automate:
ua-cam.com/video/gpgcH0hxIAc/v-deo.html
@@JoelTing got it, thanks Joel!!
Hi Joel, if I want to get the period range such as from Jan 2020 to Mar 2021 what command do I need to change in Message box?
Hi Mark, you can get the message box to obtain input from users for the start and end of the period then. Everything that has been selected/input is just passed into the script. In this specific example, what is in this video can actually be changed into just 1 line in the msg box, getting the month and the period in the same step, capturing the input of the user in the "/ format directly.
Hi Joel, Thank you!
Quick question: error pops up - "excel document name export not found" its seems as the flow is too quickly and tries to save before the excel file has been open by sap - Does it make any sense? Thanks a lot!
Hi Anea, I think I understand where you are coming from. I checked from my end, it seems that the issue is resolved if I export with the spreadsheet type "Excel (In Existing XXL Format)" instead. Hopefully this can resolved your issue with the error.
@@JoelTing Hello, my problem is the same as Anea. But I dont understant what I need to do to solve this problem. Can you please write it more clearly. Thank you Joel
by the way, I have already exported with the type of XXL Format. But I still have this problem anyway
Hello Really good.... but what if I get a file where there are many ID from a input file for example 50 different ID and based on those ID I need to extract data, and every day the IDs would be changed, based on the input file we received? Can make a video on such scenarios?
Hello Adarsh, noted on that. That is possible to be done. I will try to make a video on this soon. Just a brief overview of what can be done, in the flow you can use copy cells from Excel worksheet action to copy it into the clipboard (assuming the IDs are in 1 Excel column), and paste it over using "Shift + F12" (Upload from clipboard) in SAP. You should be able to record what is the script for that specific action.
@Ajay Kumar One example I have is below,
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtP_SYST1").text = "xxx"
The script above is generated by SAP with their recording function. However, to utilise it in Power Automate, you need to switch it to the following.
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtP_SYST1").text = "xxx"
Hope this clarifies.
Hi Joel, thanks for very good tutorial, it helped me alot. Just one question, my SAP script stops at save file as excel window and i have to manually choose to save file. Can it be done in a similar way as you did when I click on spreadsheet button in SAP.
Hi Vasil Baig, if you would like the spreadsheet button to open up Excel, you would need to change the default format it is exported to. For the example in this video, it is exported as ".XXL", while yours should be another format.
To change it, you can right click anywhere in SAP table, then click on spreadsheet. SAP should give you a choice to choose the format option to be exported.
@@JoelTing Thanks Joel, you helped me resolving this issue. 👍
Joel, I am facing one issue, report is extracting with file name worksheet in basis (1), but the layout and data which is extracted is not the same which I have chosen in SAP.
@@m_vab The format .XXL will change the layout of the data. If there's filter for your SAP table, it would be ignored as well.
If having the layout and filters exported to Excel to be exactly the same as SAP is important for you, I think you should go ahead with .XLSX, saving the file in one location and open it manually.
In my latest video, I recorded a script, exporting it with XLSX format. Feel free to check it out and I hope that can solve some of the problem that you are facing.
ua-cam.com/video/ISDX5LwcVPQ/v-deo.html
Hello!
This is amazing. I have a question: There is a way for including the login in SAP in the flow?
Hello Renzo!
Yes, It's possible to include login in SAP but it really depends on how the login is configured. Some company uses the default SAP login, which is much simpler to automate. If it's involves Single Sign On, then things gets a little more complicated. It's doable, but it might break.
@@JoelTing Possible to show us how to use default login in flow?
Hi. How to run the script in background. Since i want update Business partner in mass. There is no standard FM or BAPI available for my requirement. So i want to use this step but i want to run in background. How it is possible?.
Hi Vijay Kumar, I believe it can be ran in the background with VBScript.
From what I have tested, the SAP window do not need to be in focus when the script is being ran.
Dear Joel, when i try to automate project actuals (CJI3) - Sap is giving a save as pop up when extracting to excel. This reports doen not auto open in excel. Doe you have a work around for reports that does not auto open in excel?
Hi there, usually when it is a save as pop up, then most likely you are saving it in a different format (XLSX format). To change the format, you can right click on the table you are exporting, then click on spreadsheet. It should give you a variety of file type to choose from. The file type used in the video is in XXL format. If you are saving in XLSX format, you should be able to record the action of saving the file at a specified path. Then, you can just change the specified path accordingly based on the date and time.
bro I am using oracle instead of SAP. can you think of some ways to do it on oracle. that would help me a lot!!
Similar to another person below, I have a syntax error with the application object that gets auto generated from the SAP script. The script works perfectly if kicked off directly from SAP, but fails from VBScript function (syntax error points at row 2 below). The other person said their issue was that SAP was in Citrix which is not the case with me. I've read other places that maybe it's because I have a 64 bit system but SAP is 32 bit. Do you know anything about that? Do you run windows/office 64 bit too?
Thank you
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
Hi Radu. Apologies for the late reply. I'm not very sure about that.
To clarify on the environment that I'm running on, I'm running Windows 10 64 bit and Office 64 bit.
when i try to save & close the excel , the error message always Argument 'Instance' must be 'Excel instance'.. I follow the same steps per the youtube.
Hi there, can you check if the Excel Instance parameter under close Excel is mapped to the output of Attach to running excel. The output should be the one that you reference to it.
Is it possible to run it as a background automation so I can work on something else in different programs
Hi YugoRr, unfortunately, SAP GUI Script and Power Automate Desktop are automations based on graphical user interface. So they need to rely on the screen that it is working with. As a result, it will at some point of time interfere with your work if you are working on other things while running your scripts/flow on parallel.
Hi Joel, as i have various country codes to use but keeping the other parameters the same. Is the loop function applicable in this case?
Hi there. I think it would be still applicable. Each loop will input the same parameters assuming you are running the same script except for the country code field.
Hi I am looking to download report from SAP . I have the script recording option available. But the next step to creat flow in power automate there are different options. I am confused. Can you please help me?
Hi Vaibhav, I believe once you have recorded the script, you can simply copy and paste into Power Automate to run it with the run vbscript block. If you need more advanced functionality like changing the dates for each run, you just need to incorporate variables into your flow.
Hi Joel, does it have any SAP licensing implications if we use Power Automate Desktop with VB Script as shown in your video? SAP license Audit?
Hi Sunil, I believe there is no SAP licensing implications because it is using the same logon as what the user is using. Essentially, the script is just imitating what you are doing and you are using the same SAP session or instance.
@@JoelTing thanks Joel for a very quick response.
Thank you for your very GREAT informative video. However, I am unable to play back the script successfully. 1st. I always get a message dialog box that says "A Script is attempting to access SAP GUI." I would like to have the script automatically select "OK" without intervention. 2nd After I hit OK. I get a message "The control could not be found by id. -". And the script fails. What am I doing wrong? Any help is greatly appreciated.
Hi Steve Johnson,
1) You can click on the last icon of the SAP Menu Bar and click on "Options". Under "Accessibility & Scripting" -> "Scripting", untick "Notify when a script attaches to SAP GUI" and "Notify when a script opens a connection". The message dialog box will be disabled.
2) It seems like it may be that when the script is running, at some point, it is unable to read the field that you have previously recorded. The field that you have identified previously was not there after a series of steps. Do you mind sharing your script so that I can take a look?
@@JoelTing Hi Joel, Thanks for getting back to me. I was to uncheck the boxes and the message went away. As for the script error. I now try to tab to the various fields during the recording and the error has went away. THANK YOU FOR YOUR HELP. Keep up the great work. I look forward to watching more of your very informative videos.
Can you add a video with SQL DB automation?
Hi Luis Harms, Thanks for watching. I'll try to work on SQL DB automation with Power Automate soon.
Sorry for the late reply. Had been busy with some other projects.
Thanks for the video. Could be done in the opposite direction? I mean, read values from Excel and send to SAP througt script
Hi Javier. Yes you can. Just start with reading the information/values in Excel, then pass it into the script as a variable, like how we did with the date in the video.
@@JoelTing ok, thanks. I will try