I have a batch script which downloads some information to a csv file, then opens an Excel spreadsheet, which uses this csv as the source for a Power Query which refreshes whenever the spreadsheet is opened. After it has loaded information from the csv, I want the batch script to delete the csv.
To check if a file is locked for writing, I can use this:
:deletecsv 2>nul ( >>test.txt (call ) ) && del info.csv || (goto :deletecsv)
However, surprisingly Excel does not seem to lock files for writing while a power query is searching them. At any rate my script just deletes the csv straight away, leaving an empty file for Excel to look at.
Can I detect what applications are reading the csv? Another route would be for the Power Query to delete the original file at the end, but I’m not aware that’s possible. I could just timeout for an arbitrary duration, but in future there may be a lot more to download so not sure how long query will take. VBA is not an option.