Really weird one. I simplified my code to focus in on the problem. I'm pulling a recordset out of Access and putting it into an Excel sheet. It works perfectly. I then open the Excel sheet and create an array of the data. If I set all variables to nothing and close everything out - Excel is still running in the background. If I run the code again it fails. If I run it a third time, it works. Pressing End and closing Excel will close out the phantom exel.exe. Even stranger, if I forcibly close Excel with Task Manager, my code will still fail the second time, although with a new error message, and will work the third time.
It fails on the line allarray = .... If i do not include this line, the phantom Excel.exe does not show up in the task manager
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(name:="Agenda", Type:=RecordsetTypeEnum.dbOpenDynaset)
Dim rsdata As Recordset
Set rsdata = CurrentDb.OpenRecordset(name:="Data Call 2", Type:=RecordsetTypeEnum.dbOpenDynaset)
User = Environ("Username")
desktop = "C:\Users\" & User & "\Desktop\"
today = Format(Now(), "DD-Mmm-YYYY")
DoCmd.OutputTo acOutputTable, "Data Call 2", acFormatXLSX, desktop & "Data Call 2" & today & ".xlsx"
'create an excel object that we can control through access
Dim xlFile As New Excel.Application
Dim wb As Excel.Workbook
'Set xlFile = CreateObject("Excel.Application")
Set xlFile = New Excel.Application
' xlFile.Visible = True
Set wb = xlFile.Workbooks.Open(desktop & "Data Call 2" & today & ".xlsx")
'find the size of the datacall sheet so we can get everything into our array dynamically
Dim bottom As Long
Dim lastcolumn As Long
bottom = wb.Worksheets("Data Call 2").UsedRange.Rows.Count
lastcolumn = wb.Worksheets("Data Call 2").UsedRange.Columns.Count
' bottom = wb.Worksheets("Data Call 2").Cells.Find("*", Range("a1"), xlFormulas, , xlByRows, xlPrevious).Row
'actually put into an array
Dim allarray As Variant
Set allarray = Nothing
'ReDim allarray(bottom, lastcolumn)
'allarray = rsdata.GetRows
allarray = wb.Worksheets("Data Call 2").Range(Cells(1, 1), Cells(bottom, lastcolumn))
Set User = Nothing
Set desktop = Nothing
Set today = Nothing
ReDim allarray(10)
Set allarray = Nothing
wb.Close True
Set wb = Nothing
Set rs = Nothing
Set rsdata = Nothing
xlFile.Quit
Set xlFile = Nothing
Exit Sub
Without task manager:enter image description here
After closing phantom excel with task manager: enter image description here
Again, without the allarray line, there is no issue.
I'm about to rebuild the whole thing with a recordset instead of the allarray built from the worksheet but I'm worried the same thing will happen when I eventually throw it into a workbook.
I've moved from frustrated to genuinely curious why this is happening.
Wow thank you! When I read previously about explicity qualifying references I thought I was doing it correctly with first establishing xlfile then referencing like this wb.Worksheets("Data Call 2").Range(Cells(1, 1), Cells(bottom, lastcolumn))
But I see I really needed to explicity qualify each part. Here is the revised and working edition
Dim ws As Worksheet
Set ws = wb.Worksheets("Data Call 2")
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ws.Range("a1")
Set rng2 = ws.Cells(bottom, lastcolumn)
allarray = ws.Range(rng1, rng2)
And then of course setting all those to nothing.
Thanks a lot!