Trying to refresh Excel data

PowerShell Help
1

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.

$file = 'W:\path to the file .xlsm'
$x1 = New-Object -ComObject "Excel.Application"
$x1.Visible = $false
$wb = $x1.workbooks.Open($file)
$wb.RefreshAll()
#$xl.CalculateFull()
#$xl.DisplayAlerts = $False
$wb.Save()
$wb.Close()
$x1.Quit()
Remove-Variable wb,x1
2

I would suggest including the errors and also use the </> button to format your code.

3

Jay,
Welcome to the forum. :wave:t4:

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

You don’t even need an installed Excel to use it. :wink:

4

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.

5

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

Update.
Calculate() was not working as I was calling it against the workbook.
The fix is to define a worksheet and call calculate() on that

$ws1 = $wb.Worksheets.item("Sheet2")
$ws1.Calculate()

Now if I could find code that would loop through all the worksheets and calculate() or even better a method that simply calculates all formulas in all worksheets for a given workbook…

TADA!

$file = 'C:\temp\Book3.xlsx'
$x1 = New-Object -ComObject "Excel.Application"
$x1.Visible = $false
$wb = $x1.workbooks.Open($file)
$wb.RefreshAll()

foreach($sheet in $wb.Worksheets) {
    $sheet.calculate()
    }

$wb.Save()
$wb.Close()
$x1.Quit()
Remove-Variable wb,x1

Last step is to stop the “do you want to overwrite” prompt from Excel. DisplayAlerts = $False doesn’t seem to work

6

Hey hey. Great you’ve found a solution and thanks for sharing. :+1:t4:

7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.