Access VBA Holding CreateObject in Memory

[jccrvb]

The following code is the OnClick even on a form in my Access database. (I also show my public functions that are in Module1.) It works well the first time only. After the first time it runs, the excel file genereated by CreateObject is still in memory and either the KillFile function fails or the excel window is not viewable on the screen. Task Manager shows the EXCEL.EXE instance and when I end that task and manually open Excel, the second instance is "recovered". Help

Public Function GetUserName()
    GetUserName = Environ("UserName")
End Function
 
Public Function KillProperly(Killfile As String)
    If Len(Dir$(Killfile)) > 0 Then
        SetAttr Killfile, vbNormal
        Kill Killfile
    End If
End Function
 

Private Sub test2_Click()
 
Dim myPath As String
Dim myDocName As String
Dim xl As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlws As Excel.Worksheet
 
myDocName = "dpt_ron_schd"
myPath = "C:\Documents and Settings\" & GetUserName & "\My Documents\" & myDocName & ".xls"
 
KillProperly myPath
 
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
           "tbl_dpt_ron_schd", myPath
 
Set xl = CreateObject("Excel.Application")
Set xlwb = GetObject(myPath)
 xl.Visible = True
 xlwb.Windows(1).Visible = True
 xlwb.Save
 
Set xlws = xlwb.ActiveSheet
 
'THIS IS WHERE CODE IS TO CREATE PIVOT TABLES WITHIN THE EXCEL FILE
 
Set xl = Nothing
Set xlwb = Nothing
Set xlws = Nothing
 
End Sub

 

[honda882000]

try also adding:

xlWb.Close
xl.Quit

to the end

 

[jccrvb]

Although ultimately I would need the file to remain open, I did add the “Close/Quit” lines to see if it released the file and still the same result..."EXCEL.EXE" still listed in Task Manager...I end process, reopen Excel and it is "recovered".

What I am trying to do is let Access VBA create an excel file using a table as the record source. (seems to be no problem here). Then I need to "do stuff" in the excel file through VBA and have it remain open until the user closes it because they may need to do other stuff too (no problems here). I don't want to have to have a " dummy-code" excel file sitting in all of the user's "My Documents" folders to do this. I learned about using the Excel Object Library from within Access and truly, all has gone well with that, except for this "holding in memory" thing...It only seems to work once…and does not release the file after it is closed.

Any other thoughts?

 

[wheelchair]

No need to create an excel application for this case as
Set xlwb = GetObject(myPath) will create an excel instance. Remove all codes about xl then Try below code:

xlwb.Application.Visible = True
xlwb.Windows(1).Visible = True

 

[jccrvb]

THANK YOU SO MUCH!!!!!!!!!!
Creating the extra instance of Excel was the problem!!!!!!!!!!!!!
You rock!!!