Topic: Running Excel from ACAD

[MikeJarosz]

I am reading block attributes from a drawing titleblock and dumping them into Excel. I use the following code:

dim Excel as Excel.Application 'early binding
Set Excel = CreateObject("Excel.Application")
Excel.visible = true

Trouble is, it starts two instances of Excel, one visible, one not. After every run I have to use the task manager to kill the silent instance.

I do not end the Excel session from the program. The visible statement gives it the focus, so when the VBA program in ACAD ends, the Excel file is open for the user to fiddle with and save.

What am I doing wrong?

[Bob Wahr]

try this

Code: [Select]

Dim Excel As New Excel.Application 'early binding
Excel.Visible = True


[MikeJarosz]

Found it!!!!

It seems that implicit references to methods and properties of an object are not allowed. They must be explicit. Dumb as this seems, it worked. I had been using with/endwith.

It turns out, according to Microsoft, that every reference to a  property without an explicit reference to it's object was starting a different instance of Excel other than the one in the dim statement! MS calls it an "unqualified call". So when I released Excel, I was only releasing the instance I had started. The other instance, started by VB, was not under my control.

Here's the Knowledgebase article:
http://support.microsoft.com/kb/178510/

Here's a portion of the final code that worked:

i = DwgCount 'rows
j = IssueCount + 5 'columns

Dim Excel As Excel.Application
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet

Set Excel = CreateObject("Excel.Application")
Set Book = Excel.workbooks.Add
Set Sheet = Excel.Sheets("sheet1")

'move array to worksheet
Sheet.Range(Sheet.Cells(1, 1), Sheet.Cells(i, j)).Value = RevisionTable

'create header
Sheet.Rows("1:1").Insert Shift:=xlDown
Sheet.Rows("1:1").RowHeight = 25
Sheet.Cells(1, 1).Value = "SHEET"
Sheet.Cells(1, 2).Value = "TITLE"
Sheet.Cells(1, 3).Value = "SCALE"
Sheet.Cells(1, 4).Value = "CURRENT ISSUE"
Sheet.Cells(1, 5).Value = "CURRENT DATE"
Sheet.Columns("A:IV").AutoFit 'set variable column widths
Excel.Visible = True
Excel.UserControl = True
Set Excel = Nothing

This is part of a larger program that reads nearly 500 sheets, grabs the title blocks and the revision blocks, figures out which of multiple revisions is the latest, adds everything to a variant array ("RevisionTable") and sends it out to Excel.

One interesting fact is the code line above "sheet.range.value = RevsionTable". This is a neat feature where an entire VB variant array can be moved onto a spreadsheet in a single line of code. No looping necessary. It's amazingly fast. Reading 500 sheets and generating the spreadsheet takes about 19 seconds, mostly because there is no save.