VBScript无法刷新XLSM文件OLEDB连接问题排查

问题情况 #

使用VBS脚本刷新XLSM文件的外部数据，脚本运行无报错，但文件没有任何变化，推测是脚本在数据刷新完成前就执行了保存、关闭等后续操作。

已尝试的方案 #

• 禁用后台查询更新
• 使用Application.CalculateUntilAsyncQueriesDone
• 用WScript.Sleep强制等待（当前设置30分钟，实际需要15分钟内完成）

原脚本 #

Option Explicit

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("name_of_my_file.xlsm")

xlBook.RefreshAll
WScript.Sleep 1800000

xlBook.Save
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo "Finished test v2, i.e. refreshing data"
WScript.Quit

解决方案 #

核心问题是部分查询可能仍保留后台刷新设置，导致RefreshAll后脚本提前执行后续操作，内置等待方法未生效。以下是修改后的脚本，确保脚本等待所有刷新完成后再执行后续步骤：

修改后的脚本 #

Option Explicit

Dim xlApp, xlBook, qry

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False ' 关闭Excel提示框，避免脚本被打断
Set xlBook = xlApp.Workbooks.Open("name_of_my_file.xlsm")

' 强制禁用所有查询的后台刷新
For Each qry In xlBook.Queries
    qry.BackgroundQuery = False
Next

' 处理工作表中的ListObject（表格）查询
For Each qry In xlBook.ListObjects
    If Not qry.QueryTable Is Nothing Then
        qry.QueryTable.BackgroundQuery = False
    End If
Next

' 执行全量数据刷新
xlBook.RefreshAll

' 等待所有异步查询计算完成
xlApp.CalculateUntilAsyncQueriesDone

' 循环检查计算状态，确保所有刷新彻底完成
Do While xlApp.CalculationState <> 0 ' xlDone对应数值0
    WScript.Sleep 1000 ' 每秒检查一次状态
Loop

xlBook.Save
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo "数据刷新完成"
WScript.Quit

关键改进点 #

1. 强制关闭所有后台刷新：遍历文件内所有Queries和ListObjects，确保每个查询都以同步方式执行，避免后台刷新导致脚本提前继续。
2. 双重等待机制：先用CalculateUntilAsyncQueriesDone等待Excel内置的异步查询结束，再通过循环检查CalculationState状态，确保没有遗漏的刷新任务。
3. 关闭提示干扰：设置DisplayAlerts = False，避免Excel弹出保存提示、错误提示等打断脚本运行。

内容的提问来源于stack exchange，提问作者G H Hardy second acc