Excel not properly closing

[mtechserv]

I've written several VBA programs that dump information from an AutoCAD drawing into an Excel spreadsheet. I always seem to have a problem with Excel objects being left open after I close Excel. My programs will always work the first time. However, they will often crash after I try to run them a second time.

The VBA program is run from AutoCAD 2007. The data is being transferred to Excel 2003. Below is a sample of the code that I use to start Excel, create the workbook, and worksheet and close Excel.

Of course, there is a lot of code in between that retrieves info from AutoCAD and dumps it into specific cells of the spreadsheet. I didn't include this code because it is very lengthy. I typically declare my Excel related variables as Public, because there are several procedures and functions within the overall program that need to write info to the spreadsheet.


[VBA] 'Open Excel
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
If Err Then
Err.Clear
Set oExcel = CreateObject("Excel.Application")
If Err Then
MsgBox "Could not start Excel, exiting program...", vbCritical
Exit Sub
End If
End If


oExcel.Visible = True

Set wkbx = oExcel.Workbooks.Add

Set Bomsheet = Excel.Worksheets.Add
Bomsheet.Name = "TAFCO PARTS LIST"

wkbx.SaveAs ("c:\cad\support\tafco\orders\" & sSoNum & " PARTS LIST.XLS")
wkbx.Close
oExcel.Quit

Set Bomsheet = Nothing
Set wkbx = Nothing
Set oExcel = Nothing[/VBA]


Any advice would be greatly appreciated.

Thanks in advance

[Ivan F Moala]

Set Bomsheet = Excel.Worksheets.Add

is creating a ghost reference

try setting your Bill Of Matrials Sheet like;

Set Bomsheet = oExcel.Worksheets.Add

[Brandtrock]

Give this article a look see as well.

Regards,

[mtechserv]

Thanks to both of you. I made some of the changes based on the advice from both of you and it seems to have resolved most of my issues. I still need to finish double checking all of my programs for ghost references, but I believe most of them are fixed.

Thanks again