Excel in Power Automate for Desktop (Full Tutorial)

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.

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

🖥️ My Monitor (Amazon affiliate):

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

[andersjensenorg]

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

[dharmateja]

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

[mrnock]

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

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

[craignorton]

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

[williambell]

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!

[markkurautanen]

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

[adamshults]

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.

[daner]

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!

[Cosecci]

Thanks for the lesson, very interesting.

[yahyafares]

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

[georgewinters]

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

[markdenton]

Good work sir. Well throught out and presented.

[KT-uqbs]

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

[davi.constante]

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!

[efthimiosspiridopoulos]

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?

[alfredpereira]

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.

[ravivishwamitra]

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?

[king-tauro]

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?

[chrisholden]

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