vba 无需打开文件 PowerShell 即可自动刷新 Excel ODC 连接和数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26098013/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Automatically refresh Excel ODC connections and pivots without opening the file PowerShell
提问by KathyBlue
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.
我有几个 20 MB 以上的 Excel 文件，每周都需要在业务开始前（周一早上 8 点）刷新。
这些文件包含一个数据表，数据来自 SQL Server 视图的外部连接（ODC 文件）。
他们还有一个数据透视表，在刷新数据表后也需要刷新。
我试图找到一种解决方案（Windows PowerShell）来自动刷新数据和数据透视表，而无需接触文件。“打开时刷新”和其他 Excel 选项不可行，因为刷新所有连接最多需要 20 分钟。
我也不想刷新所有工作表，因为该文件具有图表的自定义颜色，并且“刷新”将其重置为 Excel 默认值，这是不可能发生的。
I tried this, but it doesn't seem to work with ODC connection? At least, it doesn't do anything.:
我试过了，但它似乎不适用于 ODC 连接？至少，它没有做任何事情。：
Windows PowerShell:
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
Office 版本：2010，在 Windows 7 上
采纳答案by Eris
Possibly the answer on this questioncan help. The perl script is also available as a pre-compiled exe file.
也许这个问题的答案会有所帮助。perl 脚本也可用作预编译的 exe 文件。
回答by Tony Tsang
I would approach this issue by using Excel VBA, and create your Excel file into a .xlsm.
我会使用 Excel VBA 来解决这个问题，并将您的 Excel 文件创建为 .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.
然后使用 Excel VBA 命令和函数更新文件以刷新 odbc 连接，然后另存为新文件以进行分发。
