PDA

View Full Version : Excel not properly closing



mtechserv
03-21-2007, 02:10 PM
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.


'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


Any advice would be greatly appreciated.

Thanks in advance

Ivan F Moala
03-21-2007, 02:52 PM
Set Bomsheet = Excel.Worksheets.Add

is creating a ghost reference

try setting your Bill Of Matrials Sheet like;



Set Bomsheet = oExcel.Worksheets.Add

Brandtrock
03-21-2007, 04:52 PM
Give this article (http://www.thecodenet.com/articles.php?id=14)a look see as well.

Regards,

mtechserv
03-22-2007, 11:13 AM
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