I have a powershell script that goes through all xlsx files within each child folder, refreshes their data and saves a CSV file for each of them (also overwriting the original xlsx files).
I'm currently looking for a way for the powershell script to only create CSV files when the refresh data-source actually exists.
If it doesn't, excel wouldn't create the CSV file (in other words, when the "Excel cannot find the text file to refresh..." warning happens, it would not save a CSV file).
I'm not certain that the Excel COM object is able to "see" the data source status, to be honest. That's what you'd need to figure out, but it's a VERY old piece of code that hasn't been updated in almost a decade.