I have an Excel workbook that has a data connection. It also has many formulas.
I am working on a script that will refresh the data and recalculate formulas based on the refresh.
I’m running into issues (as in data does not refresh) and am not sure why.
Am I going about this the right way and, if so, can anyone see errors in my code. It runs fine, it just seem to do anything. The last modified date on the file does not change. I have commented out 2 lines that were throwing errors. DisplayAlerts is apparently not a method of Excel.Application and CalculateFull() gave a null reference. I removed them as they were not part of the original script, just a couple of commands I found via Google.
May I ask why you want to do that with PowerShell? If I recall that right Excel is able to refresh external data when opening an Excel file with extrernal connections and the recalculation happens automaticall as well?
If you run a kind of workflow incorporating external data and exporting them to Excel you may take a look at the great module from Doug Finke
Hi, the whole point is that we don’t want to open the file manually. Long story short, this huge spreadsheet has more formulas than I have ever seen. The whole point of this spreadsheet is to populate a word document with calculations. The first worksheet has a connection to external data and all the formulas are recalculated based on this refresh. That is the sole purpose for the file and other than refresh, the user makes no edits etc.
When the user opens the file it takes ages to “do it’s thing” and locks up their PC, when they save it does it all again and becomes very painful.
Hence the idea to get a script that will “open” the file (but not Excel), do the work for them and then save/close it.
Hold fire, this may be (partially) working.
I created a new spreadsheet with just the data connection (no 1000+ formulas) and the code refreshes it perfectly. Now I just need to work out how to recalculate the formulas as step 2. Will report back if successfully for future googlers
Calculate() was not working as I was calling it against the workbook.
The fix is to define a worksheet and call calculate() on that