VBA/OLE strange, semi-repeatable error

[rocheey]

Ive written a routine that runs from AutoCad 2007. It scans the drawing for information, using early binding, starts up an instance of Excel, creates a new workbook, and writes the data. Works perfectly every single time.

But now Im trying to sort the columns (and then format the cells visually), and the routine tanks in the same place, using the same error, on the same line of code - BUT - it tanks *every other* time.

If I run the routine several times from the same autocad drawing, it gets the same information, writes the same valid data to Excel, and it runs great on 1st run of the code, always tanks on run #2, always works well on run #3, always tanks on run #4, etc... even though the same data is written correctly, the same way, every single run.

It is not only a large program, but it also reverse engineers the file format of a commercial product (grin),
so theres no way I can post all the code here. But I can post a snippet. The error message that is always generated is :

"Method 'Range' of object 'Global' failed"

and it always tanks on this line of code:

.SortFields.Add key:=Range(ERange$), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal


I've also added some custom comments inline here to help follow what's going on.
'--- snip ------- snip ------ snip -------


' format a string to represent the last used row #
RowStr = LTrim$(Str$(CurrentMatlRow))
' format the first column for 2-digit decimals
MatlSheet.Range("A" & RowStr).NumberFormat = "0.00"


' build "range" strings to highlight different Columns

' first, define total used area in worksheet
TotalRange$ = "A2:I" & RowStr
' define used cells in column "E"
ERange$ = "E2:E" & RowStr
' define used cells in column "C"
CRange$ = "C2:C" & RowStr
' define used cells in column "B"
BRange$ = "B2:B" & RowStr

' activate the workbook object
reportBook.Activate

' activate the worksheet object
MatlSheet.Activate

' I sprinkled some doevents in in hopes it
' would help; it doesnt
DoEvents

' select the range
MatlSheet.Range(TotalRange$).Select
DoEvents

With MatlSheet.Sort
.SortFields.Clear
DoEvents
' ******* next line always errors out********
.SortFields.Add key:=Range(ERange$), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
DoEvents
.SortFields.Add key:=Range(CRange$), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
DoEvents
.SortFields.Add key:=Range(BRange$), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
DoEvents
.SetRange Range(TotalRange$)
DoEvents
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' --- snip--- snip----- snip-----
any help would be appreciated. I have a sneaking suspicion that it may not even be 'directly' code related... call it a hunch ...

 

[PHV]

Replace this:
key:=Range(ERange$),
with this:
key:=MatlSheet.Range(ERange$),

and so on for ALL non full qualified Range in your code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rocheey]

It worked like a charm; thanks a boatload. I still dont understand why, by not fully qualifying the range(s), that the code did not tank consistently, though.

 

[xlbo]

probably to do with the sheet in the workbook that is left active at the end of each run. Would suggest that the active sheet at the end of a run is different when the code worked compared to when it didn;t. Therefore any unqualified range references will bomb out in 1 instance but not the other (when the code happens to end on the "right" sheet)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question