Excel in Power Automate for Desktop (Full Tutorial)

Рет қаралды 27,535

Күн бұрын

This video tutorial shows how to automate Excel with Microsoft Power Automate for desktop. With this full use case, you'll learn all the basics and advanced features, such as running macros/VBA and dynamic file naming.
📂 Course materials: andersjensen.org/microsoft-po...
🔹 Complete Power Automate for Desktop Course: • Power Automate Desktop...
👨‍👩‍👧‍👦 Discord: / discord
- Join my network with 2600+ RPA Developers, where we solve RPA problems, network, and help each other upgrade our careers. It's free!
FOLLOW ME:
💼 LinkedIn: / andersjensenorg
📸 Instagram: / andersjensenorg
👨 Facebook: / andersjensenorg
🐦 Twitter: / andersjensenorg
💌 Email Newsletter: andersjensen.org/email-newsle...
🖥️ My Monitor (Amazon affiliate):
Samsung 49-inch CRG9 Curved Monitor = geni.us/MDpejc
- If you are buying this, I can recommend the Mi Monitor Light Bar = geni.us/3Wwad
0:00 Intro
Automating Excel with Microsoft Power Automate for desktop is very easy. This use case takes you through all the basic features and advanced ones, such as dynamic file naming and running Excel macros (VBA).
3:37 Read Excel data with Power Automate for desktop
We'll use a Launch Excel to open an Excel book (sheet). That will produce an Excel instance we can refer to. Remember to use a Close Excel action, so your book/sheet won't get locked to read-only. Use a Get first free column/row to get your data range. Read the Excel data into a Data Table by a Read from Excel worksheet action. Iterate through the data with a For each action. Convert the Excel data with a Convert text to number action, so we can make calculations. Finally, write the data back to Excel with a Write to Excel worksheet - remember to create an index variable to write back to the right Excel cell.
18:36 Use Excel macros/VBA in Power Automate for desktop
We use the Run Excel macro to run an Excel macro created in Visual Basic for Applications, VBA. It's potent because sometimes, the actions in Power Automate are not enough. It's shown how to use arguments. Remember to change the parameters in Launch Excel to load the macros.
23:57 Dynamic file naming
The Get current date and time and the Convert DateTime to text actions are used to create dynamic file names = the date and time when the robot runs. This prevents us from overwriting data because we'll always have a new file name.
26:48 Set active worksheet
Remember to use the Set active worksheet to make your Excel automating more stable.
#microsoft #office365 #powerautomate

Пікірлер: 97

@andersjensenorg 2 жыл бұрын

What do you think of the video? Please let me know here in the comments 🖐😊

@SehazArtworks 2 жыл бұрын

Very Good Sir

@andersjensenorg 2 жыл бұрын

@@SehazArtworks Thanks a lot, Sehaz 🙂

@vamsimantripragada Жыл бұрын

Hello Jensen, I want to connect .xlsm files into power automate web, can you help me?

@mrnock2 10 ай бұрын

Excellent video again Anders. The activity of running macros is of great use. Thanks for your continuous support!

@andersjensenorg 6 ай бұрын

Hey @mrnock2. Thanks a lot 😊 Have a great day. You're more than welcome at my Discord, where we're 11,000+ RPA developers networking and solving problems: kzfaq.info/get/bejne/rr12rZCEnpy8d6M.html Kind regards, Anders

@dharmateja51 3 ай бұрын

You are an excellent tutor sir. Your way of explanation, picking up topics and quality is excellent. Really helpful for people who can't afford premium trainings! Keep doing for us..❤

@andersjensenorg 3 ай бұрын

Your appreciation means a lot to me ❤️ I'll keep creating content to help as many people as I can 😊 Kind regards, Anders

@craignorton8678 Жыл бұрын

Another great video from Anders. Nice and clear. Keep up the great work and awesome videos.

@andersjensenorg Жыл бұрын

Thanks a ton, my friend 🥰

@adamshults9810 Жыл бұрын

FANTASTIC! Starting with no PA experience I was able to develop a real-world solution in just a few hours. One thing I learned is that the browser element selector does not work well when browser is maximized.

@andersjensenorg Жыл бұрын

Hey Adam. Thanks a lot 😀 With that said, it sounds very weird with the maximized window. Do you have a link and what you tried to click/get? Kind regards, Anders

@obakenglebisi Жыл бұрын

Thank you. This was very insightful. It really is a guide for beginners, everything was well explained and I even learned new excel tricks with ease.

@andersjensenorg Жыл бұрын

Hello Obakeng - I'm so glad to hear. A pleasure to create videos, when nice persons like you are commenting :) Have a great day. Kind regards, Anders

@markkurautanen1368 Жыл бұрын

Thanks Anders! Your videos helped me very much to get started.

@andersjensenorg Жыл бұрын

Hey Markku. I'm so happy to hear, thanks a lot for letting me know 🙂 Kind regards, Anders

@williambell4591 Жыл бұрын

This was a very nice tutorial, thank you so much for developing this training! Coming from UiPath, I've been tasked w/getting some experience with PAD to get familiar w/its capabilities. I LOVE the fact you can actually perform SYSTEM related functions - working with files (renaming, moving, deleting etc.) I actually feel Microsoft should INCLUDE Power Automate Desktop w/the operating system - starting at say, Windows Pro and above - it's a GREAT utility to have as a Windows power user - also a nice way for ANYONE to get some exposure to programming / software development!

@andersjensenorg Жыл бұрын

Hey William, thanks a bunch and I 100% agree with you 🙂

@litvin_china 3 ай бұрын

Great job 👍👍👍👍👍👍👍👍 only watching u

@andersjensenorg 3 ай бұрын

Thank you so much, Litvin ✔😊 Kind regards, Anders

@90daner Жыл бұрын

your lessons are great sir! never change you speed voice it is great beacause i dont have to press pause to do the same things that you are doing! thanks!

@andersjensenorg Жыл бұрын

Hey Andre - thanks a bunch for your kind words 💯😍 Have a great weekend.

@stop007michaela 3 ай бұрын

Perfect! Thank you very much 🙂

@andersjensenorg 3 ай бұрын

My pleasure 😊🙌 Kind regards, Anders

@marcuscato1704 2 жыл бұрын

Thank you very much. Great tutorial!

@andersjensenorg 2 жыл бұрын

Thanks a ton, Marcus Cato 🤩 Have a great day. Kind regards, Anders

@anthonyluan3511 7 ай бұрын

Helpful to me a lots, thanks Mr.

@andersjensenorg 7 ай бұрын

Thanks for the big support, kind regards, Anders

@AndrewLee-nz4yk 4 ай бұрын

Thank you for this educational video.

@andersjensenorg 4 ай бұрын

Thanks for the feedback, Andrew! Glad you found the video helpful 🌱 Kind regards, Anders

@andersjensenorg 2 жыл бұрын

Find my complete Power Automate for desktop course here: kzfaq.info/sun/PLXXz88_TPiHpY_5bfeV8RAOHnBKgc-rtw 🙂

@Cosecci 2 жыл бұрын

Thanks for the lesson, very interesting.

@andersjensenorg 2 жыл бұрын

It's my pleasure, Corrado - thanks for commenting 🙌😊

@yahyafares7515 5 ай бұрын

Very interesting tutorial , thank you for the quality of the content

@andersjensenorg 5 ай бұрын

Many thanks, happy to hear 😍 Kind regards, Anders

@healthyman01 9 ай бұрын

Amazing tutorial.

@andersjensenorg 9 ай бұрын

I'm so glad to hear, thank you 😊 Kind regards, Anders

@georgewinters2066 11 ай бұрын

What a fantastic video !! More More More please!!

@andersjensenorg 11 ай бұрын

Hey @georgewinters2066. Thanks a lot 😊 Have a great day. You're more than welcome at my Discord, where we're 8000+ RPA developers networking and solving problems: kzfaq.info/get/bejne/rr12rZCEnpy8d6M.html Kind regards, Anders

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

Very good demo.

@andersjensenorg Жыл бұрын

So kind of you, thanks, Michael 🙂

@markdenton8027 Жыл бұрын

Good work sir. Well throught out and presented.

@andersjensenorg 11 ай бұрын

Thank you so much, Mark 😎🙌

@markdenton8027 11 ай бұрын

@@andersjensenorg My company runs on a bespoke web app we developed for the last 17 years. Today I added performance charts measuring load times for our main screen to our monitoring system. This is the first time we have ever had proper stats. I'm an infrastructure engineer.... I even struggle a bit with powershell. Power Automate ROCKS.

@joseeduardohuertaornelas9864 Жыл бұрын

Great Video !!!

@andersjensenorg Жыл бұрын

Thanks a ton, Jose 🙌🙂

@KT-uq3bs 2 жыл бұрын

Very very interesting Anders. I am starting exploring RPA development using UiPath however I would like to learn Power Automate as well. What are the main differences between these two apps? strengths and weaknesses of those ones? Thanks

@andersjensenorg 2 жыл бұрын

Hey KT. That's a big question, but: UiPath: The best and expensive. Microsoft: Good and cheap (already in your O365 package). Kind regards, Anders

@davi.constante 2 жыл бұрын

Hello, Anders! Thank you for all your lessons! Wouldn't be easier to use Power Query instead of Power Automate in this specific use case? Thank you for your help and attention! I'm sorry for any inconvenience!

@andersjensenorg 2 жыл бұрын

Hey Davi. Thanks a lot. Sure. Most processes don't use Excel only, but a range of programs, and then you can't use PQ 😊

@efthimiosspiridopoulos9531 Жыл бұрын

Great guide, makes things a lot easier. Thank you Anders! Btw, I cannot manage to run a macro from my personal.xlsb (PERSONAL.XLSB!Consolidate) instead of one being stored on the current instance. Any ideas on how to do that?

@andersjensenorg Жыл бұрын

Hey Efthimios Spiridopoulos Thanks for writing. I'm getting more than 50 questions/comments every day and having difficulty answering everyone (I try my best). I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: kzfaq.info/get/bejne/lbeCfJCK1JjMaIk.html Have a great day. Kind regards, Anders

@ildiandaprincipo163 Жыл бұрын

xlsm, I think for extension file

@ravivishwamitra723 Жыл бұрын

Hi Anders, great content as usual. I have a question. I wanted to fetch data from excel and then write into a textbox(s) on a website. For example: login credentials for a website OR filling Credit/Debit Card details. How do you achieve that?

@andersjensenorg Жыл бұрын

Hey Ravi. Thanks a bunch for your nice words. Please check this video: kzfaq.info/get/bejne/Z72VrdV0ttLUco0.html Kind regards, Anders

@chrisholden6658 Жыл бұрын

Hi Anders, what's the best way to set up a process to automate the refreshing of power queries in Excel? Is it to trigger a vba macro in the file or is there another way. Thanks

@andersjensenorg Жыл бұрын

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 6000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzfaq.info/get/bejne/rr12rZCEnpy8d6M.html Kind regards, Anders

@LapaConsult_Ex_Macraris 2 жыл бұрын

Hello Anders, thank you I have learned how to activate macro from PAD. I am wondering why my Variable ExcelData do not show the Headers as yours but Column 1 Column2 and so forth. .. thank you

@andersjensenorg 2 жыл бұрын

Hey Macraris - thanks a lot. In your Read from Excel worksheet, go to Advanced and tick 'First line of range contains column names'. That will work 😊 Kind regards, Anders

@LapaConsult_Ex_Macraris 2 жыл бұрын

@@andersjensenorg Thank you again it works!

@andersjensenorg 2 жыл бұрын

@@LapaConsult_Ex_Macraris Great to hear 😊

@alfredpereira9152 Жыл бұрын

Anders, hats off to your content! It's pretty good for one who already knows the basics! Also one recommendation. Please try to improve your English, more would be able to follow is what I believe, because more than a year ago I could not follow along with you(beginner). This is out of love for you I follow a lot of your content please don't take me wrong I believe a friend's rebuke is a blessing.

@andersjensenorg Жыл бұрын

Hey Alfred, thanks a lot for the great feedback - in what ways do you think I can improve the English best? 🙂

@king-tauro1166 9 ай бұрын

thx for the video I had a problem saving the excel, I disabled Read-Only option. I edit some rows in the excel, and when i want to save the excel it says that it cant save the excel, aprox a min a message button appears on screen "File available, {File} is available to edit. Choose ReadOnly and Write to open and save it " Do i need something to set before to skip this message?

@andersjensenorg 8 ай бұрын

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 10,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzfaq.info/get/bejne/rr12rZCEnpy8d6M.html Kind regards, Anders

@MrNelvaro 2 ай бұрын

@andersjensenorg irrelevant question: if I stop my code say after 3 records and the total is 10 for example, and I want to continue where I left off instead of from the beginning, how would I go about it

@andersjensenorg 2 ай бұрын

Great question. Either build the logic in Excel or use Work Queues: kzfaq.info/get/bejne/pseJgsJy2LuYlZ8.html Kind regard, Anders 😊

@waveboardboy 7 ай бұрын

good

@andersjensenorg 7 ай бұрын

Thanks a lot 😀

@lookman06 2 жыл бұрын

any idea how to open the excel to maximized or normal size after using launch excel function? instead of it just opening in the shortcut tab?

@andersjensenorg 2 жыл бұрын

Hey Take the L Thanks for writing. I'm getting more than 50 questions/comments every day and having difficulty answering everyone (I try my best). I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: kzfaq.info/get/bejne/lbeCfJCK1JjMaIk.html Have a great day. Kind regards, Anders

@CP-zb3ky Жыл бұрын

Can I use Power Automate Desktop to just automatically make a few clicks on various buttons and options for single sign-on, refresh entire workbook, log off single sign-on, then save? Then set the Power Automate to run on its own once a day?

@andersjensenorg Жыл бұрын

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 7000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzfaq.info/get/bejne/rr12rZCEnpy8d6M.html Kind regards, Anders

@henriquebedin9819 Жыл бұрын

Hello Anders, Could you pls tell me if it's possible just to: Open the excel, update the data and close the file. It's a vry simple procedure, however I coudn't find the right path to take this action. Would you pls give me a hand on this? Thank you sirt

@andersjensenorg Жыл бұрын

Hey Henrique. Sorry, for the late reply. Yes, it's straightforward to do with Power Automate Desktop. Can you post your exact problem on my Discord (the link is in the video description), and then we solve it? Kind regards, Anders

@rvallejosguerrero 9 ай бұрын

HI please, could you help me? how can I open a Excel file that ever is changing it name?

@andersjensenorg 9 ай бұрын

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 9000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzfaq.info/get/bejne/rr12rZCEnpy8d6M.html Kind regards, Anders

@rogeryang18 2 жыл бұрын

Hi Anders, thanks for your lesson! Just one concern about this -- the iteration looks pretty slow. If I have an excel file with 50000 rows, the whole process will take hours to complete?

@andersjensenorg 2 жыл бұрын

My pleasure 😊 Great question. It's in debug, so it's slower, you can either run it from outside the development space (e.g. the overview or cloud) OR just change the Run delay to 1 in the lower right corner. Have a great day. Kind regards, Anders

@rogeryang18 2 жыл бұрын

@@andersjensenorg Thank you so much! Make perfect sense to me.

@erikeringaad8638 2 жыл бұрын

@@andersjensenorg If I put a breakpoint on Close Excel and run.. it took 17 sec. if I run it with 10 in delay it took 7 sec. Then run it from Overview it gives 3 sec. so this works .. be carefull to put delay to low because even this powerfull tool can become dizzy. specialy when communicate with other applications. Coming from VBA I'm surprised by the possilities of this tool(s). @anders keep up the good work. I'm going to like you 👍

@andersjensenorg 2 жыл бұрын

@@erikeringaad8638 Thanks a ton for taking your time giving us this feedback 😊😊

@HippieP629 Жыл бұрын

@@andersjensenorg Ahh, thanks

@SrdjanS_Automations Жыл бұрын

I've noticed something. There are 2 sheets in the excel file "andersjensenorg" and "Employees". How does PAD know which sheet we want since we didn't specify? EDIT: Nvm my comment was before last part haha

@andersjensenorg Жыл бұрын

Hahaha, thanks Srdjan and sorry for the late reply - I hope you're still here (at the channel)? Kind regards, Anders

@gydkam Жыл бұрын

Connections to External Data Sources popup window Resolution

@andersjensenorg Жыл бұрын

@nghiepnguyen6241 Жыл бұрын

I dont know why in my case, when i lanch excel (step1) my excel's window always appears, but in your case, its not. Sorry for my bad english

@andersjensenorg Жыл бұрын

@eduardciobanu1380 Ай бұрын

What if you wanted to save excel as... I got error when I tried it.

@andersjensenorg Ай бұрын

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 12,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzfaq.info/get/bejne/rr12rZCEnpy8d6M.html Kind regards, Anders