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
You can get the process ID vba get process id and make a cycle while there is a process.