Automatically update Excel ODC connections and collapse without opening a PowerShell file

Question

I have several Excel files larger than 20 MB, and they need to be updated every week before starting a business (Monday, 8am).
These files contain one data sheet, and the data comes through an external connection (ODC file) from the SQL Server view.
They also have one summary sheet, which also needs to be updated after updating the data sheet.

I am trying to find a solution (Windows PowerShell) to automate updating data and pivot sheets without having to touch files. Refresh on open and other Excel options are not viable since it takes up to 20 minutes to upgrade all connections.

I also do not want to update ALL sheets, because the file has a custom coloring for charts, and “Refresh” resets it in Excel by default, which cannot be.

I tried this, but it doesn't seem to work with an ODC connection? At least he does nothing:

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, Windows 7

Answer 1

Perhaps the answer to this question may help. A perl script is also available as a precompiled exe file.

Answer 2

I would apply this problem using Excel VBA and create an Excel file in .xlsm format.

Then update the files with Excel VBA commands and functions to update your odbc connection, and then save it as a new file for distribution.

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