☰

如何在不打开文件的情况下更新 Excel 文件（数据刷新和更新公式）？

tag-icon • tag-icon microsoft-excel script automation microsoft-excel-2010
如何在不打开文件的情况下更新 Excel 文件（数据刷新和更新公式）？

我有一个 Excel 文件，想自动更新并保存，而无需打开或手动交互。手动操作时，我打开文件并点击数据刷新，然后执行 SQL 查询，然后按 F9 更新公式，最后关闭/保存即可。

（然后我会使用 perl 脚本将文件邮寄给人们，或者使用 SAS JMP 运行一些数字/图表并将它们邮寄出去。基本上，我需要编写一些需要更新 XLS 文件的脚本。）

答案1

我的解决方法是在 Excel 文件中录制一个宏（因此您必须使用 .xlsm 文件扩展名）（首先您需要转到程序 >> Windows Powershell 并在提示符下键入Set-ExecutionPolicy RemoteSigned以允许脚本运行）：

Sub AutoUpdate()
'
' AutoUpdate Macro
' data refresh from MSQuery connection

'
    Sheets("Feuill1").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

然后我使用 Powershell 脚本来完成这个任务：打开 Excel 文件，调用宏，保存并关闭文件。在这个例子中，我复制了原始文件以用于安全/备份目的，但当然你也可以保存原始文件本身。

$objectExcel = new-object -c excel.application
$objectExcel.displayAlerts = $false # don't prompt the user
#$objectExcel.visible = $True;
$Classeur = $objectExcel.workbooks.open("source_filepath", $null, $true)
$objectExcel.run("AutoUpdate")
# $objectExcel.run("RemoveODBC") # another custom macro for removing data connexion
$Classeur.saveas("destination_filepath")
$Classeur.close($false)
#$objectExcel.visible = $False;
$objectExcel.quit()
spps -n excel

因此，我的 Excel 报告每天都会通过调用上述脚本的 Windows Planified 任务刷新，无需任何人工干预。

答案2

我最终发现Win32::Excel::刷新它符合要求。它是一个 perl mod，但它还附带一个 windows 可执行文件，如果您不了解或不想处理 Perl，可以运行它。

答案3

您可以使用以下方式操作 Excel 文件自动识别，这里是一个用于执行此操作的库。您甚至可以使用 AutoIt 自动执行 SQL 查询并将数据传递到 excel 文件（取决于您使用的数据库引擎）。AutoIt 甚至还可以发送电子邮件。

相关内容