Power Automate Desktop: How to Read Excel, Do an Online Search, and Write the Result Back to Excel

This video tutorial shows how to read data from an Excel sheet, do an online search, web scrape the results, and write it back to Excel with Microsoft Power Automate Desktop. Web automation and web scraping error handling are also covered.

VIDEOS FOR YOU:

- Join my network with 12,000+ RPA Developers, where we solve RPA problems, network, and help each other upgrade our careers. It's free!

FOLLOW ME:

MY GEAR (Amazon affiliate):
🖥️ My monitor

💡 Lights

⌨️ Keyboard

📹 Camera

🎤 Microphone

0:00 Intro
We want to read data from Microsoft Excel, perform an online search (Google), web scrape the data, and write the result back in the Excel sheet. All made by Power Automate Desktop.

1:24 The simple solution
We start by creating a simple solution in Power Automate that does a static web search and scrape the data (= we'll wait by adding the Excel part). Then, use the Launch new Microsoft Edge Action to open Edge with a start URL. Next, we can send a text to our search field with a Populate Text field (it's straightforward to work with UI elements). To send an Enter click, use the Send Keys actions. Learn to use variables as well.

9:10 The complete web scraping solution with Excel
Use the Launch Excel action in Power Automate Desktop to define the Excel scope and open your .xlsx document. Remember to use the Close Excel activity to prevent your Excel sheet from being locked after the automation has run. Next, read the Excel data into a DataTable with a Get first free column/row from the Excel worksheet action and a Read from Excel worksheet action. Remember to specify your Excel sheet has headers. Next, use a For Each (loop) to iterate through each Excel sheet/Data Table row. Remember to go to the start page at the start of each iteration (Go to the web page). The Write to Excel worksheet is used for writing the value to the next column (a specific cell).

19:12 Error Handling
Use a Power Automate Desktop On block error to make sure an error (business exception) doesn't break the robot.

#office365 #microsoft #powerautomate

[JeffyMonsterEsq]

Just got this working on a more complex set of look-ups and writes. Took hundreds of rows of cut & paste effort and gave it to the bot to do while I went to watch a concert. Came back and the work was all done. Rewarding to see I could make this happen. Finally. Your instructions gave me important keys to unlocking my final output. Glad I persevered through my tweaking of your example to get a script that worked for me. Lots of errors I had to figure out. Good learning, as failures often are. I'm no programmer, so getting to my end goal was not a sure thing. To those who follow this video, I'll just say, "If I can do it, you can do it."

And to Anders -- I really love the calm, reassuring tone of your video and voice. They were a real asset for me as I tried to learn something that doesn't come as naturally to me as I'd expect. I am 100% certain this video has unlocked a new world of time savings for me and my colleagues. Thank you!

[bogdangdumitru]

Great job! I started learning UiPath in March 2021, WITH YOU and I have already managed to automate a large part of my activity. Now I'm starting to learn Power Automate and your videos are so clear that learning is a pleasure. Thank you for everything you do! 🙏👏

[alexheiderscheidt]

Thank you so much for this video!! it was just what I needed to automate a daily task!
Hugs from Brazil and success for the channel 🙌

[jimmybaldbird]

Great video and great channel— helps me tons at work. Thanks!

[akshayp]

My first successful loop! Thanks mate!

[bibhasdebroy]

I was looking for this to use for project update. Every block u created is useful for me. Thanks a lot!! Owe you a coffee..

[luissebastianramirezamelia]

This is the most useful video I have ever seen- Thank you

[daveys]

Seriously powerful stuff. I have a load of things to look up for a project and this will help immensely. Thanks!

[TheAyushlankiFusion]

Best video on internet for power automate

[Hy_Jay]

Thanks Anders Jensen, your tutorial help me a lot and saved my time thanks for making amazing video

[motooors]

Very useful and clear steps, thanks a lot

[SanjayChandraS]

Dude you saved me 2 days of work thanks man

[MrSports]

Thank you for this video, it has helped me alot.

[nomahlubidlamini]

Hi Anders. Thank you so much for the tutorial, especially the error handling part. I followed your example to the T, literally every step but I had a few errors that seemed to be related to the font size the data is presented in the web browser. For Example i got an error with Mark Zuckerberg, Parag, Daniel. Of course the flow ran completely because of the error handling step but this means that the success rate is lowered. What can I do to increase the success rate in this particular instance?

[pedyrokgaming]

This was extremely helpful, thanks! Instead of increasing the variable by 1 is there a way to do a search and match it to another excel document. For Example, I have an Excel Document "Scanned Invoices" which has the columns PO# & Invoice # then I have another Excel Document "Downloaded Invoices" It only has the Invoice# Column. I need to search the "Scanned Invoices" for the invoice# then I need to match that invoice# to the invoice# in the "Downloaded Invoices" Document and enter the PO# in the cell next to it. Any ideas?

[jordaneason]

Jensen, thank you so much for the great video. How could I handle a situation where I am pulling data tables off the internet (a table of 12x12 words or 12 x 2 words) and then instead of putting them in one cell in one column, I would want to transpose the table into various rows of data?
for example the first column could be a verb like "to work" - i then search a verb conjugator website - it returns a table of various conjugations of that verb, then my robot would return (in rows beside the first column) all the versions of the verb to work. Is this possible? Thanks!

[Jeo_Thomas]

Thanks for sharing this is really awesome :). Can you guide on how to add delay after few iterations. I am getting error in data extraction after running the flow for some time and the data does not get saved either.

[chillibang]

Hi Anders, very helpful material. In this example, is it possible to just pick some of the CEO names in Ur example ( not all) to perform on them only the online searches and then save a screenshot into word document?

[luisdanielpinargotepico]

Hi Anders, I really like your videos! I have a problem with Google Vision(Handwriting detection) with Uipath, Could you help me with this?
Thanks a lot!!

[FineTuningAnalysis]

Hi Anders, great video thanks.

Quick question, is there a way for Power Automate Desktop to read only the new rows of data since the last time it ran? For some context, I have a client that wants to tranfer user details from a Microsoft Form into their CRM system when they sign up for a course but I want to avoid the flow trying to readd users that were previously added if you get me?