Excel file in OneDrive, PAD running macro to refresh queries sometimes doesn't work.

(0) Share

Report

Posted on by Cayshin

314

Hi all,

I have an issue that's been driving me bonkers for the past few months.

I have an Excel file stored in OneDrive. In that file I have multiple queries to a SQL database. I've created a macro in that file to refresh these queries, it started as something simple like:

For Each objConnection In ThisWorkbook.Connections
objConnection.Refresh
Next

I then created a PAD flow to open this file, run the macro to refresh the queries, then close the file. I've also set up a Flow to run this PAD Flow as "Attended" every day @ 4am (outside active business hours).

90%-95% time, the entire process runs fine and I can see that the file successfully refreshed. The rest of the time, I'll come into work and see that the file didn't refresh at all or only some of the queries refreshed. When I check the Flow gateway run history it shows everything Succeeded.

I've tried changing the Excel macro to loop multiples times, but it still sometimes fails to refresh. The frustrating part is that I've never been able to replicate the issue when running this process manually or triggering the PAD manually, it only ever happens when it runs overnight via PAD (and then notice in the morning when it didn't work). I'm actually having this same problem with a few others Excel files too that I refresh daily via PAD.

Could this be an issue with PAD? It is an issue with OneDrive? I'm hoping someone can give insight into what might be causing this problem.

Thanks

Categories:

Power Automate Desktop

I have the same question (0)

All responses (2)

Answers (1)

VJR 7,635 on at

Like (0)

Report

This is not what you may have asked 😊 but since it is happening for several months and because it is an Excel on OneDrive you may want to try writing an OfficeScript and call it from Power Automate Cloud Flows. Sample here.

Was this reply helpful? Yes No
Verified answer

Cayshin 314 on at

Like (0)

Report

I think I finally figured out my issue and it was a OneDrive problem.

As part of my PAD Flow, I would download a CSV file to OneDrive and overwrite the previous file. The Flow would then open Excel and refresh a query that is connected to that CSV in OneDrive.
The problem: my query's source was the ONLINE path (Csv.Document(Web.Contents("https://xxxxx.sharepoint.com/sites/....), not the local path. This means, OneDrive sometimes didn't sync yet/didn't finish syncing the file by the time my refresh ran.

Solution: There are two ways to fix it, one being change to the local file path, but since other people may use this file I went with another method. In my PAD Flow, before running the Excel refresh, I had it close out of OneDrive, re-open OneDrive to force a sync, then add a wait of 2min to insure the file finished syncing before continuing the rest of the PAD Flow.

Was this reply helpful? Yes No