CODEWITHSUNDEEP
sqlexcelvbapowershell
KathyBlue
KathyBlue

Reputation: 366

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

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

Upvotes: 10

Views: 9451

Answers (2)

Tony Tsang
Tony Tsang

Reputation: 32

I would approach this issue by using Excel VBA, and create your Excel file into a .xlsm.

Then update the file w/ Excel VBA commands and functions to refresh your odbc connection, and then save as a new file for distribution.

http://www.vbforums.com/showthread.php?675977-Auto-Open-Refresh-Pivots-Save-Close-Excel-files-using-VB

Upvotes: -1

Eris
Eris

Reputation: 7638

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

Upvotes: 2

Related Questions