Advice please - Use Power Query to replace/remove the need for RPA and Access DB

[threw001]

Hi all,

I have a process that uses a mix of Access Database, Power Automate Desktop and Power Automate Cloud. I would like to see if there was a way to come up with a similar output using a different process, more specifically without the need of having RPA and an online/available enviroment for the automation to run (cutting out Power Automate Desktop).

Please see below process steps:

Step 1) I have data that is sent to me via email attachment (xlsx), Power Automate Cloud is triggered whenever this email is received to take the attachment and save it into a certain location under a certain file name (write-over).

Step 2) This file is plugged in as a data source for Access Database, a bunch of joins and logic tests are done in Access and there are output queries set up.

INFO I have a master excel file that uses Access Database Queries as data source (my final data outputs) into multiple worksheets within the same workbook.  

Step 3) Power Automate Desktop flow is triggered, it logs into my machine which is specically set up to be online at all times and available. It logs into my user, opens the excel file, navigates to the ribbon and presses "Refresh All".

Step 4) The refresh happens, the file is saved.

Step 5) Power Automate Cloud then picks up the saved file, saves a copy of this file into a SharePoint folder location

Step 6) Power Automate Cloud then sends me an email notifying me that the flow has ran successfully.

The main output of the process is to produce a file that takes data (from the email), use set custom rules/logic and apply to the data. Manipulate the output and then save this into multiple locations.

My main objective is to get away from having to use Power Automate Desktop where RPA happens. I am finding that sometimes the flow fails due to either not having an enviroment available for the flow to run in, etc. I would like the automation to be fully cloud based to increase reliability.

Can this be done in Power Query with a mix of Power Automate Cloud?

Please advise 🙂

Thanks,

[m_dekorte · Accepted solution]

Hi @threw001 

You will have to see which trigger is most suitable for your Cloud flow, but you can always schedule a recurrence... Next run an OfficeScript to Refresh All Connections within your Excel file. Learn how to Run Office Scripts with Power Automate, here.

 Here's the script. You can find OfficeScripts on the Automate tab of the ribbon in Excel.

function main(wb: ExcelScript.Workbook) {
	// Refresh all data connections
	wb.refreshAllDataConnections();
}

I hope this is helpful

[threw001]

Hi @m_dekorte 

The OfficeScript is working great!

The only thing I am finding now is that Excel is asking me to sign in everytime I open the file. (I think due to the data connection). 

Is there an office Script to delete all data connections?

Thanks! 

[m_dekorte]

Hi @threw001 ,

Just copy the output from your worksheet into a new xlsx file with OfficeScripts and Power Automate

Doesn't match your scenario exactly but for inspiration check out this post:

https://exceloffthegrid.com/move-data-between-workbooks-power-automate/

I hope this is helpful

[threw001]

Thank you! I am now using this with Power Query, cutting out RPA and DB Access