Powershell脚本-打开Excel，更新外部数据，另存为 [英] Powershell Script - Open Excel, Update External Data, Save as

问题描述

有人可以帮助我使用此脚本吗?

Is anyone able to help me with this script.

$file = 'C:\Scripts\Spreadsheet.xlsx'
$x1 = New-Object -ComObject "Excel.Application"
$x1.Visible = $false
$enddate = (Get-Date).tostring("dd-MM-yy")
$filename = 'C:\Scripts\Spreadsheet ' + $enddate + '.xlsx'
$wb = $x1.workbooks.Open($file)
$wb.refreshall
$wb.SaveAs($filename)
$wb.Close()
$x1.Quit()
Remove-Variable wb,x1

该工作簿将打开，并通过ODBC连接使用外部数据更新其数据透视表.当您手动打开工作簿时，它会刷新.使用脚本打开它时，它只是打开而不会刷新数据.

The workbook opens and updates its pivot table with external data through an ODBC connection. When you open the workbook manually it refreshes. When you open it with the script it just opens and does not refresh the data.

我尝试了以下操作:

• 选中复选框始终使用连接文件"
• 在excel中保存数据源的密码
• 禁用启用后台刷新" 和所有其他刷新选项
• 创建了一个宏，以在工作簿打开时自动刷新数据源

• Checking the checkbox "Always use connection file"
• Saving the password for the data source inside excel
• Disabling "Enable background refresh" and all the other refresh options
• Created a macro to automatically refresh the data source when the workbook opens

任何帮助将不胜感激，谢谢！

Any help would be appreciated, thanks!

推荐答案

更改

$wb.RefreshAll

到

$wb.RefreshAll()

这篇关于Powershell脚本-打开Excel，更新外部数据，另存为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！