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
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