I have been working on exporting data from a 3rd party app (in this case, Qlikview) using VBA in Excel, and automating the process of finding the exported data and copying the values from there into the target workbook. I can do all this, but sometimes the export opens in a new instance of Excel and I need to find it. I adapted the answer from here to find what I needed, but when I find the exported workbook using that solution I am finding the VBA Project associated with the exported workbook remains after the workbook is closed. The full code opens and closes a lot of these and it causes performance issues; it seems the projects remain in memory.
I have found similar questions and most answers would suggest that setting the object reference to Nothing will address the issue, but it does not help in this case. I suspect that it has something to do with the dll functions I am calling. Here is the code I am using which has been able to reproduce the issue:
Sub getQlikDataToExcel()
Dim qlikTableName As String
Dim qD As QlikView.Document
Dim qApp As New QlikView.Application 'connects to running QlikView app
Dim srcWb As Workbook
Set qD = qApp.ActiveDocument 'use for testing purposes
qlikTableName = "Document\CH78" 'name of table in Qlik app
Set srcWb = tableToExcel(qlikTableName, qD)
srcWb.Close False
Set srcWb = Nothing
End Sub
Function tableToExcel(tName As String, qD As QlikView.Document, Optional waitIntervalSecs As Long = 180) As Workbook
Dim success As Boolean, wbNew As Boolean
Dim timeout As Date
Dim openWbs As New Collection
Dim wb As Workbook, openWb As Workbook
Dim xlApp As Application
' create a collection of open workbooks
' will check for multiple Excel instances
For Each xlApp In xlInst.GetExcelInstances()
For Each wb In xlApp.Workbooks
openWbs.Add wb
Next wb
Next xlApp
wbNew = False
success = False
timeout = DateAdd("s", waitIntervalSecs, Now())
DoEvents
qD.GetSheetObject(tName).SendToExcel
' loop through all workbooks until the new book created by qlik is generated
Do
DoEvents
For Each xlApp In xlInst.GetExcelInstances()
For Each wb In xlApp.Workbooks
' check if workbook name contains the table name from qlik
If InStr(1, wb.Name, tName) > 0 Or _
InStr(1, wb.Name, Replace(tName, "Document\", "")) > 0 Or _
InStr(1, wb.Name, Replace(tName, "Server\", "")) > 0 Then
' set flag to new
wbNew = True
' if workbook already existed flag it as not new
For Each openWb In openWbs
If wb Is openWb Then wbNew = False
Next openWb
' if new workbook, function returns workbook
If wbNew Then
Set tableToExcel = wb
success = True
End If
End If
Next wb
Next xlApp
' loop terminates when workbook is found or after a timeout
Loop Until success Or Now() > timeout
Set wb = Nothing
Set xlApp = Nothing
' function returns Nothing if timeout
If Not success Then Set tableToExcel = Nothing
End Function
In the xlInst module is the following code:
#If VBA7 Then
Private Declare PtrSafe Function AccessibleObjectFromWindow Lib "oleacc" ( _
ByVal hwnd As LongPtr, ByVal dwId As Long, riid As Any, ppvObject As Object) As Long
Private Declare PtrSafe Function FindWindowExA Lib "user32" ( _
ByVal hwndParent As LongPtr, ByVal hwndChildAfter As LongPtr, _
ByVal lpszClass As String, ByVal lpszWindow As String) As LongPtr
#Else
Private Declare Function AccessibleObjectFromWindow Lib "oleacc" ( _
ByVal hwnd As Long, ByVal dwId As Long, riid As Any, ppvObject As Object) As Long
Private Declare Function FindWindowExA Lib "user32" ( _
ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
ByVal lpszClass As String, ByVal lpszWindow As String) As Long
#End If
' Source:
' https://stackoverflow.com/questions/30363748/having-multiple-excel-instances-launched-how-can-i-get-the-application-object-f
'
Public Function GetExcelInstances() As Collection
Dim guid&(0 To 3), acc As Object, hwnd, hwnd2, hwnd3
guid(0) = &H20400
guid(1) = &H0
guid(2) = &HC0
guid(3) = &H46000000
Dim AlreadyThere As Boolean
Dim xl As Application
Set GetExcelInstances = New Collection
Do
hwnd = FindWindowExA(0, hwnd, "XLMAIN", vbNullString)
If hwnd = 0 Then Exit Do
hwnd2 = FindWindowExA(hwnd, 0, "XLDESK", vbNullString)
hwnd3 = FindWindowExA(hwnd2, 0, "EXCEL7", vbNullString)
If AccessibleObjectFromWindow(hwnd3, &HFFFFFFF0, guid(0), acc) = 0 Then
AlreadyThere = False
For Each xl In GetExcelInstances
If xl Is acc.Application Then
AlreadyThere = True
Exit For
End If
Next
If Not AlreadyThere Then
GetExcelInstances.Add acc.Application
End If
End If
Loop
Set xl = Nothing
Set acc = Nothing
End Function
Note that I do not have the issue if I stick to the current instance of Excel; ie. I modify the code so that it does not use the xlInst module. This works fine unless the 3rd party app decides to export into a new instance; not sure if I can force that somehow.
--Edit--
In case it's not clear, I can run the program and avoid using the xlInst module if I removed the For Each xlApp In xlInst.GetExcelInstances() lines and just set xlApp to the current instance. When I do this, the VB Projects do not persist when I run it.
