Automatically refresh Excel ODC connections and pivots without opening the file PowerShell

Question

I have several 20+ MB Excel files, and they need to be refreshed every week before business starts (Monday 8 AM).
These files contain one Data sheet, and data comes via external connection (ODC file), from an SQL Server view.
They also have one pivot sheet that also needs to be refreshed after the Data sheet is refreshed.

I am trying to find a solution (Windows PowerShell) to automatize the refreshing of Data and Pivot sheets without the need to touch the files. "Refresh on opening" and other Excel options are not viable because it takes up to 20 minutes to refresh all the connections.

I also don't want to refresh ALL sheets because the file has custom coloring for charts and "Refresh" resets it to Excel default which cannot happen.

I tried this, but it doesn't seem to work with ODC connection? At least, it doesn't do anything.:

Windows PowerShell:

$ExcelApp = new-object -ComObject Excel.Application
$ExcelApp.Visible = $false
$ExcelApp.DisplayAlerts = $false

$Workbook = $ExcelApp.Workbooks.Open("c:\test\ref_test.xlsx", 3, $false, 5, $null, $null, $true)
Start-Sleep -s 30
$Workbook.RefreshAll()
$Workbook|Get-Member *Save*
$Workbook.Save()

$ExcelApp.Quit()

Any ideas?

Office version: 2010, on Windows 7

Answer 1

Possibly the answer on this question can help. The perl script is also available as a pre-compiled exe file.