Excel & DataTables in Power Automate Desktop - Including VBA and VBScript

Learn how to use Excel in Power Automate Desktop, including VBA and VBScript. Follow along as we prepare data, perform Excel calculations, and use dynamic file naming. Discover how to easily incorporate VBA and VBScript into your workflows for more efficient automation.
📂 Course Page: andersjensen.org/microsoft-po...
VIDEOS FOR YOU:
🔹 Advanced Excel in Power Automate Desktop: • Advanced Excel in Micr...
🔹 Save Web Data to Excel with Power Automate Desktop: • Save Web Data to Excel...
POWER AUTOMATE DESKTOP GROUPS:
👨‍👩‍👧‍👦 Discord: / discord
- Join my network with 6000+ Automation/RPA developers, where we solve Automation/RPA problems, network and help each other upgrade our careers. It's free!
👩🏻‍🎓 LinkedIn: / 12566435
👨🏻‍💻 Facebook: / 754059285247921
FOLLOW ME:
💼 LinkedIn: / andersjensenorg
📸 Instagram: / andersjensenorg
👨 Facebook: / andersjensenorg
🐦 Twitter: / andersjensenorg
💌 Email Newsletter: andersjensen.org/email-newsle...
00:00 Intro
We do Excel calculations in a range of data with Microsoft Power Automate Desktop.
00:41 Prepare Data
Download the Excel data from the course page. Then, place it inside the project folder.
02:38 Read Excel Data
Launch an Excel instance and remember to close it again; otherwise, Power Automate Desktop can and will lock the Excel book.
9:37 Excel Calculations
Use the Insert column to Excel worksheet to insert a column between two existing columns. With a For each action, we loop the data and do Excel operations. Remember to convert the text to numbers before you can do mathematical operations on them. With the Resize columns/rows in Excel worksheet action Power Automate Desktop lets you resize a range of Excel columns.
23:53 Dynamic File Naming
We use the date and time of the flow runtime for dynamic file naming.
26:10 VBA
We can easily use VBA in Power Automate Desktop with the Run Excel macro action.
31:01 VBScript
VBScript, Visual Basic Script, is smarter than VBA, Visual Basic since it's not dependent on running in an Excel book. VBScript and VBA are very similar.
#powerautomate #powerplatform #microsoft365

[@andersjensenorg]

Thanks for all your likes and support 🙌🔥 You should watch this video next: kzbin.info/www/bejne/kKfFn6iHpZyra8k

[@PowerAutomateDesktop0322]

@Anders Jensen, regarding power automate desktop how to pass variables from one flow to another flow. For example flow1 variable values to flow2. Can you please help me for this.

[@andersjensenorg]

@@PowerAutomateDesktop0322 Sure, you can use the Run desktop flow action with input/output variables 🙂 Kind regards, Anders

[@budgetappliancespares9411]

You are an exceptionally talented teacher and for someone not English, you speak better English than the English themselves. I love you man!!! Have learnt loads from you.

[@andersjensenorg]

Haha, thanks a lot, my friend 😍😍😍 I have many more PAD videos to come during the next months. Have you seen my new PL-500 course (only the first lesson so far): kzbin.info/aero/PLXXz88_TPiHrnu0itj7At0K-xEZyl2XNr Kind regards, Anders

[@jmathew6988]

Dear Anders, I started watching your videos on Power Automate since about 2 weeks ago, starting with the beginner's tutorial. I want to say that all of them are wonderfully explained, making the learning experience really enjoyable and easy to understand. I am new to this application, but by now I have successfully created some very helpful automate flows for my work. I want to dedicate a big salute to you and wish to thank you for all the help so far. 🙏

[@andersjensenorg]

Appreciate the love, thanks a ton, j 🥳😍 These comments make it easy to find the motivation to create more videos. Please let me know, if you see something I can do better. Kind regards, Anders

[@jose00033]

I’m all here for this! Thanks Anders! Keep sharing the knowledge

[@andersjensenorg]

Hey Jose Carlos! Thanks a ton for the nice message, highly appreciated. Have a great weekend ☀🙂 Kind regards, Anders

[@tonberryking3368]

4:20 You can end the hanging excel process by ending the open excel task in task manager > details. Always a good place to look if an automated file isn’t opening

[@andersjensenorg]

Thanks a lot, tonberryking. I prefer either cmd/PowerShell because it can also be implemented in PAD. Have a great day. Kind regards, Anders

[@DIY-Investors]

Another extremely useful video… thank you Anders. My rate of knowledge growth (in PAD) is rapid, thanks to your guidance and very clear tutorials! Best wishes, Mick.

[@andersjensenorg]

Hey Mick, thanks a bunch for the kind words 💪🙂

[@yahyafares7515]

Thanks again Anders for this amazing tutorial. keep going and thank you for sharing best practices in Power Automate desktop

[@andersjensenorg]

Hey @yahyafares7515. 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: kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@stineandtherobots]

I love the intro (and automation) 😍🤖

[@andersjensenorg]

Haha, thank you 😍

[@entertainmentgalaxy971]

hi thanks for another amazing video, i have a question, why did you add insert column F which i think dont need to add as there is no column after E.

[@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 11,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): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@ALVINMAN452]

Thank you for the video. Do you have any course about VBScript?

[@andersjensenorg]

Unfortunately not anything else than in this video, but I can recommend ChatGPT (even the free version GPT 3.5). Kind regards, Anders

[@ALVINMAN452]

@@andersjensenorg Once more time thank you for the tip. Best regards.

[@faheemsyed4852]

Hi Anders How can I remove blank Rows from a data table?

[@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 11,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): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@Pipiziu]

Regarding the vbscript, I was having error Unterminated string constant for "%ActiveSheet%" where it used to reference the worksheet name. but where I use "Products" , it works. how to resolve this so it can reference the variable instead of the name of the worksheet .

[@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 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): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@jimmyfal]

When I download the Candy.xlsx sheet the first 2 columns data are not in decimal format. For example: Cost says: 365, Price says: 694, Units Sold says: 315. However on your video is shows decimals. Am I doing something wrong? Or is your data not formatted correctly on your course files site?

[@andersjensenorg]

Thanks a bunch, @jimmyfal 🤩 I've created a Discord. We're more than 11,000+ RPA developers networking and solving problems here: kzbin.info/www/bejne/roipq2CJbpuLfdE You're more than welcome. Kind regards, Anders

[@KaushikMHegde]

When I Read from excel worksheet... there is no data table that is showing up... Can someone help me with this?

[@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): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@fagbolaayobola]

@andersjensenorg Hi! Great video. I am unable to run the Macro when I have the Excel instance as invisible, do you know why?

[@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 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): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders