Ok just started cleaning up our AutoCAD/Excel Macro's for Excel 2007
(still running AutoCAD 2004).

I have a macro that runs from AutoCAD VBA. It calls Excel and starts
populating cells with data from AutoCAD. At the end of this AutoCAD's
object reference to Excel calls for an AutoFill routine that is giving
me problems.

The big mystery is that the AutoFill works on the first pass,
completely. Only when I run the Macro from AutoCAD again does the
Range.AutoFill step fail.
_
I have to enter the AutoCAD VB Editor and press the stop button (to
reset the VBA?) before I can run the macro again successfully._

I am trying to make some small sample code but the project has gotten
big and needs to be cleaned up once I can see through this problem.

Here is some outline...

Within my created AutoCAD Class wrapper for Handling Excel, named
objMyExcelClass

Option Explicit
Private objExcel As Excel.Application
Private objWrkSht As Excel.Worksheet

Private Sub Class_Initialize()
Set objExcel = CreateObject("Excel.Application")'refrence to excel
object
objExcel.Workbooks.Add
Set objWrkSht = objExcel.Worksheets(1)'refrence to current worksheet
objExcel.Visible = True
End Sub

Other routines print to objMyExcelClass, then I try to select a small
range on the second row (just below my 1srt row for headers) and
AutoFill that down to my last row where "lngLastRow" is a long that I
get with another function for finding the last used row on the
worksheet. With in my class...

Dim objRange as Range ' excel range object

Set objRange = objWrkSht.Cells.Range("B2:E2")
objRange.AutoFill Destination:=Range("B2:E" & lngLastRow),
Type:=xlFillDefault

I can't find anything wrong with the syntax for the above three lines of
code???

I have double checked to make sure that I set all my object references
to Excel to nothing with...

Set objExcel = Nothing ' within my excel class wrapper
Set objMyExcelClass = Nothing 'when I am done with my excell class
wrapper

as the object references are not needed at the end of my macro.

So if the macro passes without error on the first call but fails on the
second, wouldn't you think that it is an object reference problem? Like
I am not fully closing my object references?

What exactly happens when you press the Stop button on the VBA editor?
Is it possible that AutoCAD's VBA is not releasing a reference to Excel
even when you can clearly see that objExcel and objMyExcelClass objects
are switching to Nothing on the watch window.

If AutoCAD's VBA was having trouble creating new references to Excel I
thought this might help:

Private Sub Class_Initialize()
On Error Resume Next

Set objExcel = GetObject("Excel.Application")

If Err Then
On Error GoTo 0
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
End If

Set objWrkSht = objExcel.Worksheets(1)

objExcel.Visible = True

End Sub


This code works but the the GetObject never finds a reference to an
active Excel session. Even if Excel is open the AutoCAD's VBA does not
have a reference (which is what I want, one reference at a time only).

Can anyone give me a clue here. Not looking for someone to code for
me. This is just odd behavior and needs some insight from someone with
more experience.

-John

amiga.user wrote:
Ok just started cleaning up our AutoCAD/Excel Macro's for Excel 2007
(still running AutoCAD 2004).

I have a macro that runs from AutoCAD VBA. It calls Excel and starts
populating cells with data from AutoCAD. At the end of this AutoCAD's
object reference to Excel calls for an AutoFill routine that is giving
me problems.

The big mystery is that the AutoFill works on the first pass,
completely. Only when I run the Macro from AutoCAD again does the
Range.AutoFill step fail.
_
I have to enter the AutoCAD VB Editor and press the stop button (to
reset the VBA?) before I can run the macro again successfully._

I am trying to make some small sample code but the project has gotten
big and needs to be cleaned up once I can see through this problem.

Here is some outline...

Within my created AutoCAD Class wrapper for Handling Excel, named
objMyExcelClass

Option Explicit
Private objExcel As Excel.Application
Private objWrkSht As Excel.Worksheet

Private Sub Class_Initialize()
Set objExcel = CreateObject("Excel.Application")'refrence to excel
object
objExcel.Workbooks.Add
Set objWrkSht = objExcel.Worksheets(1)'refrence to current worksheet
objExcel.Visible = True
End Sub

Other routines print to objMyExcelClass, then I try to select a small
range on the second row (just below my 1srt row for headers) and
AutoFill that down to my last row where "lngLastRow" is a long that I
get with another function for finding the last used row on the
worksheet. With in my class...

Dim objRange as Range ' excel range object

Set objRange = objWrkSht.Cells.Range("B2:E2")
objRange.AutoFill Destination:=Range("B2:E" & lngLastRow),
Type:=xlFillDefault

I can't find anything wrong with the syntax for the above three lines
of code???

I have double checked to make sure that I set all my object references
to Excel to nothing with...

Set objExcel = Nothing ' within my excel class wrapper
Set objMyExcelClass = Nothing 'when I am done with my excell class
wrapper

as the object references are not needed at the end of my macro.

So if the macro passes without error on the first call but fails on
the second, wouldn't you think that it is an object reference
problem? Like I am not fully closing my object references?

What exactly happens when you press the Stop button on the VBA
editor? Is it possible that AutoCAD's VBA is not releasing a
reference to Excel even when you can clearly see that objExcel and
objMyExcelClass objects are switching to Nothing on the watch window.

If AutoCAD's VBA was having trouble creating new references to Excel I
thought this might help:

Private Sub Class_Initialize()
On Error Resume Next

Set objExcel = GetObject("Excel.Application")

If Err Then
On Error GoTo 0
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
End If

Set objWrkSht = objExcel.Worksheets(1)

objExcel.Visible = True

End Sub


This code works but the the GetObject never finds a reference to an
active Excel session. Even if Excel is open the AutoCAD's VBA does
not have a reference (which is what I want, one reference at a time only).

Can anyone give me a clue here. Not looking for someone to code for
me. This is just odd behavior and needs some insight from someone
with more experience.

-John
I hate leaving a thread unresolved.. I picked up an answer from
another forum:

/from A.Poulsom:/

/Make sure you fully qualify all your references. For example: /

objRange.AutoFill Destination:=Range("B2:E" & lngLastRow),
Type:=xlFillDefault

should be:

objRange.AutoFill Destination:=objWrkSht.Range("B2:E" &
lngLastRow),
Type:=xlFillDefault


This solved my errors. My code works on the first pass and subsequent
passes.