Connecting PowerAutomate to Excel File

[Dharber]

Hello - 

I have been trying to get an excel file stored in SharePoint (Saw on another forum this needs to be the case, cannot be saved locally) to automatically refresh my data from a sql database connection.  To do this, we have made a Script to refresh the data which does work when running it in the excel file itself, however the issue we are having is that PowerAutomate doesn't seem to be connecting to the file, so nothing is refreshing even though the Tests are successful.  In PowerAutomate we have selected the correct file and path, so we are not sure why they aren't making that connection. 

Any help would be greatly appreciated!

[trice602]

Hi @Dharber ,

I think there are lots of people on here that will help; can you share your flow, errors, etc?  It's hard to just start guessing what the issue is without seeing anything.  Otherwise, you most likely will get a handful or very little engagement of replies that don't really address the issue.  On the surface, this sounds very resolvable. 

[Dharber]

Hello @trice602 - 

Apologies, haven't used this forum very often.  

No errors to show, below is the Flow in PowerAutomate and the script we are using in BI. Like I said the script works, we just can't get the connection to excel.  The crossed-out part is the path which we believe to be correct and not the issue (covered for privacy).  

[Dharber]

Sorry, Script we are using in Excel**

[trice602]

Hi @Dharber ,

Thanks for sharing, very helpful.  The issue is this isn't supported from Power Automate and there's a super user that replied here that offers some good alternatives: Workbook.refreshAllDataConnections() is not suppor... - Power Platform Community (microsoft.com)

My preferred approach is a quick Power Automate Desktop unattended flow that simply opens up this excel worksheet and presses the refresh all button, saves, closes, returns to your cloud flow.  Given that, I'm using premium licensing and is the easy button.  A longer approach, but also works well, is to put your sql connection in a dataflow and you do have the ability to refresh a dataflow from your power automate cloud flow.  If you haven't used dataflows, its essentially the PowerQuery for the Power Platfrom (looks and feels exactly the same).

Dataflow option:

Desktop flow option:

That said, I hope someone else can provide some alternatives, I'm also following this post to see replies and solutions. 

[Dharber]

With this dataflow option, it looks like it is refreshing the data set in PowerBi, which is the same data set, however this doesn't translate into the Excel file.  Is there a way to make this happen or do you need to desktop (paid) version?

[trice602]

Hi @Dharber ,

Dataflows is a premium action and after your refresh your content you can create an Excel file.  It is largely identical to Power Query.  As an example, you would start with a completely blank table and then your sql query would run and populate a table.  If you needed an excel file, you can create that with the refreshed content.

[Dharber]

Hi @trice602 
It looks like someone on the team has the premium version. With this, it should be a simple desktop flow as you outlined above, correct? 

I appreciate your help on this!!

[trice602 · Accepted solution]

Hi @Dharber ,

Yes.  The desktop flow may look like this or you can actually click on the buttons to refresh.  Here I am showing you the Send Keys version where:

Alt A R A //refreshes all

Control S // saves

[Dharber]

Awesome, appreciate it!

[trice602]

Happy Friday!  A great way to start the day 🙂