如何让Excel VBA等待Qlikview完成图表与表格加载？

问题背景 #

现有Excel VBA代码可外部控制QlikView操作，但QlikView执行字段选择等指令后，常会需要较长时间加载图表和表格，而Excel会在加载完成前继续执行后续代码。目前采用Application.Wait (Now + TimeValue("0:00:02"))固定等待2秒的方式，但有时加载时长超过设定值，导致后续操作出错。曾尝试qvDoc.GetApplication.WaitForIdle 1000但未生效，需寻求更可靠的等待方案。

用户当前代码示例：

Set f = QvDoc.Fields("Tarifa")
    f.Select "(0)"
    Application.Wait (Now + TimeValue("0:00:02"))
    Set f = QvDoc.Fields("nom_mar")
    f.Select Mar
    Application.Wait (Now + TimeValue("0:00:02"))
    
    'Set the path where the excel will be saved
    SA = Ruta & ".xls"
    CD2 = CP & SA
    'Create the Excel spreadsheet
    Set ExcelFile = CreateObject("Excel.Application")
    ExcelFile.Visible = True
    Application.Wait (Now + TimeValue("0:00:02"))
    'Create the WorkBook
    Set curWorkbook = ExcelFile.Workbooks.Add
    'Create the Sheet
    Set curSheet = curWorkbook.Worksheets(1)
    'Get the chart we want to export
    Set tableToExport = QvDoc.GetSheetObject("CH444")
    Set ChartProperties = tableToExport.GetProperties
    tableToExport.CopyTableToClipboard True
    'Get the caption
    chartCaption = tableToExport.GetCaption.Name.v
'    MsgBox chartCaption
    'Set the first cell with the caption
    curSheet.Range("A1") = chartCaption
    'Paste the rest of the chart
    curSheet.Paste curSheet.Range("A2")
    ExcelFile.Visible = True
    'Save the file and quit excel
    curWorkbook.SaveAs CD2
    curWorkbook.Close
    ExcelFile.Quit
    'Cleanup
    Set curWorkbook = Nothing
    Set ExcelFile = Nothing
'    Set XLApp = Nothing

可行解决方案 #

1. 循环检查QlikView文档的忙碌状态

利用QlikView文档对象的IsBusy属性，循环等待直到文档完成加载。这种方式能精准匹配实际加载时长，避免固定等待的不确定性。

首先在VBA模块顶部声明Sleep函数（兼容32/64位Excel）：

#If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

然后将原固定等待代码替换为循环检查：

Set f = QvDoc.Fields("Tarifa")
f.Select "(0)"
' 等待QlikView文档结束忙碌状态
Do While QvDoc.IsBusy
    DoEvents ' 避免Excel假死，保持界面响应
    Sleep 500 ' 每次等待500毫秒，可根据实际调整
Loop

Set f = QvDoc.Fields("nom_mar")
f.Select Mar
Do While QvDoc.IsBusy
    DoEvents
    Sleep 500
Loop

2. 检查目标Sheet对象的就绪状态

针对要导出的表格/图表对象，额外检查其是否完全就绪，避免文档整体空闲但目标对象未加载完成的情况：

Set tableToExport = QvDoc.GetSheetObject("CH444")
' 循环等待目标对象可正常获取属性
Dim objProps As Object
Do
    DoEvents
    Sleep 300
    On Error Resume Next
    Set objProps = tableToExport.GetProperties
    On Error GoTo 0
Loop Until Not objProps Is Nothing

' 后续执行复制、导出操作
tableToExport.CopyTableToClipboard True

3. 修正WaitForIdle的用法

若坚持使用WaitForIdle，可循环调用并结合应用对象的IsBusy属性，同时设置最大等待时长防止无限循环：

Dim qvApp As Object
Set qvApp = QvDoc.GetApplication

Dim maxWaitTimes As Integer
maxWaitTimes = 20 ' 最多等待10秒（20*500ms）
Do While qvApp.IsBusy And maxWaitTimes > 0
    qvApp.WaitForIdle 500
    maxWaitTimes = maxWaitTimes - 1
    DoEvents
Loop

' 若超时可添加错误提示
If maxWaitTimes = 0 Then
    MsgBox "QlikView加载超时，请检查操作或延长等待时长"
    Exit Sub
End If

注意事项 #

• DoEvents必须添加，否则Excel在等待过程中会处于假死状态，无法响应其他操作
• Sleep的等待间隔可根据QlikView的加载速度调整，建议在300-1000毫秒之间
• 所有循环等待逻辑都应添加最大时长限制，避免因QlikView异常导致无限等待

内容的提问来源于stack exchange，提问作者MikeWasos