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?
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.
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.
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?
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...
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...
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.
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.
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
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.)
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.
There are some known issues and limitations stated by MS. Please refer below link :-
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
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.
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?
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.
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)
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.
Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.