data connection refresh excel with flow

[jendebett]

Hello,

I have an excel workbook saved in a document library on sharepoint. The excel file contains a table with data from a .txt file.

every day a new .txt file is emailed, and I have used flow to save the file in sharpoint.

I have the data connection set to refresh in the background, and refresh on opening.

so every day I open excel, allow the table to refresh, save and close.

i'd really like to have flow do this data refresh without opening excel... is this possible?

[driekes]

I've taken screenshots from the flow in 2 parts. Whe I do a testrun a look at the details, I can su non-updated data is being fteched from the tables in the Excel file. So adding a 2 min. wait before the email-step won't help I think.

Part1Part2

[driekes]

Hi,

I tried your solution. But is is not quite working. In the excel app, my excel containts the correct data. When I open the same workbook in Ecel online I get the security warning as per picture. I think the sheet cannot refer to my other workbook, nor can Powerautomate use this workbook, as long as this warning persists.

[12flow]

Same issue here.

An Excel file (saved in SharePoint) containing open planner tasks is updated weekly  by a flow. Due to the fact that management needs the priority of the tasks (which cannot be extracted by a flow), there is a connection to another excel file to get the priority.

So the flow runs every monday, deletes all rows of the excel with all open tasks, extracts all Task IDs from planner, writes thos into this list (which is connected to the list with the priorities via the Task ID) and at the end, sends the list of open tasks in the attachment via mail. unfortunately, the connection between the excel files doesn't refresh automatically, you have to do it manually by clicking on "enable content", as it is mentioned above.

Any idea how to fix this or workaround?

[driekes]

I called in support. "Excel online is another product than excel app" was the answer.  (please have other expectations).

OK, that was clear. That being said, I managed to avoid using links. I used Powerautomate For Desktop to copy stuff (tables, cells) from file A to file B. So I avoid the problem of the "Data connection not enabled" message . I hope this can help you in your workflow. 

That being said, it is an absolute hell to work with excel files from, let's say12MB with the onedrive sync client to Sharepoint. Im' even looking at GPO level what is giving me hell.  The file is mostly not accessible by PAD. No idea why. I have to try a few testruns, then it works. 1 day later and bingo, the flow fails. No other person but me is using the file. The struggle is real...

[driekes]

I am a noob. But no, you will probably have to open the file with Powerautomate For Desktop to have some automation and refresh your workbook. (Or take steps in Dataverse and PowerBI, but as said, I'm a noob) That works. Maybe a macro to refresh dataconnection, pivot tables... Build in some waits. That works great but: I have trouble to even access the **bleep** file with PAD. There could be many reasons. I'm even looking at GPO's to find out why. The file seems locked by something, and I'm the only one using the file. I'm still testing...

[12flow]

Thanks for your reply!

[teespolyglot]

If the flow hits a wall because the file is locked when only you are using it, try the 'Check out file' action at the start of your flow (before performing any other operations on the file) and see if that makes a difference?

The data refresh issue could possibly be something do with authentication, as that's what the issue was in my case, although I was fetching data from a SharePoint list. I needed the authentication embedding, so the Excel could access the SharePoint list, despite the two being located in the same SharePoint.

It's worth pointing out, for anyone else facing the same issue, that a file created using an exported query.iqy file from the SharePoint would not allow the Excel to even open in a browser, returning the "Unsupported features" message. On the other hand, using the Get Data > Other Sources > OData Feed option allowed me to open and use the Excel file in the browser, but still failed due to the aforementioned authentication issue.

[teespolyglot]

I may be misunderstanding the flow. It sounds like File A is updated with data from Planner and File B, but how is File B updated?

[12flow]

File B is updated manually by a diffrent department. On thing they add is e.g. the "task priority" which is the crux of my process due to the fact that it cannot be extracted by a flow.

[12flow]

I will try that, thank you.

[teespolyglot]

Rather than rely on the file being updated automatically, could you not write File A in the flow and then use the get table function to get the data from File B and write it in that way? That would avoid the need for a dynamic link

[driekes]

This will only work when @12flow  uses .xlsx and no .xlsm files. Just to make sure. In that case Powerautomate for desktop can help. (Premium function to call from PA webapp.)

[bu8qnth]

Hi,

I am trying to refresh data in one of the excel file(destination excel) which has reference to other excel files(source excel) kept at share path, However when I manually open my both the excel file(I need to keep open both the excel file since I am using INDIRECT function which works upon when files are open) data gets refresh and populated at destination excel but when I try to automate it using PA destination excel file data doesn't get refresh/populated, even all referenced files are kept open. I have gone through multiple article and forum but no luck, Below are the links I have referred to, If any one has come across same issue and found the solution please guide me. 

Referencing value in a closed Excel workbook using INDIRECT? - Stack Overflow

Links to Other Workbooks - Updating / Refreshing - INDIRECT vs Hardcoded | MrExcel Message Board 

[Ronak83garg]

There are some known issues and limitations stated by MS. Please refer below link :-

https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#run-script-(preview)

[JamesRobson]

I have an .xlsx file that contains a query into the DB via and ODBC connection and returns a single table with Delivery Date and Shipment Numbers, nothing fancy.

I want to be able to refresh the table at midnight every day, so using the logic stated earlier I Created a Sharepoint location dumped the .xlsx into that then created a Flow to open the file and run my Script

function main(workbook: ExcelScript.Workbook)

{

  // Refresh all data connections

    workbook.refreshAllDataConnections(); 

}

The flow seemingly runs without error but when I open the file to check the data/table hasn't been updated?

Refresh data on desktop works fine so query is good.

I believe the script and flow to be good because if I add some fake data and make a pivot table it updates the pivot fine.

I tried completing the steps manually but notice when I click the 'Refresh All Connection' within Excel online nothing happens (works fine if I open in Excel app)??

I promise I have read the above comments and checked the external links but I don't seem to be getting anywhere!

Or is there a better solution that doesn't involve me leaving my laptop on and using the UI feature (yes that does work but its not practical).

TL;DR - Is there a specific workaround I need to be aware of for refreshing data/query using an ODBC connection with PA and Excel online.

Note: I don't want to use refresh on open as I need midnights data

[Ronak83garg]

Hi @JamesRobson 

It's mentioned by Microsoft that the below piece of Office Script will not refresh the data.
Although, It runs without giving any error but the data refresh doesn't happen. I have faced the similar issue.

function main(workbook: ExcelScript.Workbook)

{

  // Refresh all data connections

    workbook.refreshAllDataConnections(); 

}

[JamesRobson]

Damm, I'm guessing there is no other script that can complete this function or did you find a workaround to refresh data tables within Excel online using Power Automate?

Thanks,

[Ronak83garg]

Hi @JamesRobson 

I didn't find any workaround for this one.

Explored Macro which can refresh the data in excel, but not able to execute Macro from Power Automate.

Thanks,

[JamesRobson]

Best work around I've managed is to turn on 'refresh upon opening' for the query then use a batch file to open, wait then save combined with windows scheduler (also tried turning off background refresh and turn on quick load so I didn't need to define the wait period and although this did work it seems a little...... strange when running so use with caution)

[Ronak83garg]

Yeah, I tried the similar option but in our organization we need to change registry settings to add a task in windows scheduler.

Hence, dropped it 😀

But thanks a lot for the update.