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

Dont know what version of Office you have but maybe BackroundRefresh is off for that connection. Have a look here on how to set it. .RefreshAll method on refreshes only when "BackgroundQuery property set to True" as per MSDN — Matt
Hi Matt, background refresh still refreshes every time a user opens the file and will have to wait 20+ minutes to get fresh data. I am using Excel 2010 on Win7. Updated the original question with this info, thanks for pointing out. — KathyBlue
I would setup a cron job using Windows Task Scheduler to run a .bat file every Monday morning. In the .bat file, I would pass in an argument identifier saying "hey, I'm running from a cron job", then in the ThisWorkbook Workbooks.Open function, I would check for the argument and run the required refresh & close the excel book as necessary. The following link (from the way back machine) talks about opening Excel using CLI and passing in an argument mrexcel.com/archive/VBA/15657.html — Michael

Eris Eris · Accepted Answer · 2014-11-25T07:32:12

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

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

2 Answers