I need Excel VBA to wait until Qlikview finishes working-CodePudding

First question I leave here, so I hope it is correct.

My problem seems to be simple: I want to know the best way to keep excel waiting until another application (Qlikview) is done loading graphics and tables.

I have a code that controls what Qlikview does outside Qlikviews script, and works fine, but my problem is that sometimes after a certain order Qklikview takes some time to reload, and excel continues before this loading is finished.

I leave here an example of this code I'm using:

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

I've done quite a bit of searching, and the best I've found was the "Application.Wait (Now TimeValue("0:00:02"))" trick.

The problem is that sometimes, the loading last for more than 2 seconds. I guess that adding more seconds to "Application.Wait" would solve this problem, but I would like to know a more sofisticated way, maybe a loop with the method "sleep" that makes excel wait untin Qlikview has finished

I've tried using "qvDoc.GetApplication.WaitForIdle 1000" after some research (see here), but it doesn't work.

Can anybody help me? I would be more than happy to give more info about this if needed.

Best regards,

Mike

CodePudding user response：

You can get the process ID vba get process id and make a cycle while there is a process.