A
amiga.user
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
(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