with xlWS
.
.
.
r = Range(Cells(1, 1), Cells(1, .UsedRange.Columns.Count - 1)).address
.
.
.
end with
works -- for weeks -- until it throws an "Application-defined or object-defined error" error. Thereafer, the following seems to work (please not the "dot-Range/dot-Cells" syntax):
with xlWS
.
.
.
r = .Range(.Cells(1, 1), .Cells(1, .UsedRange.Columns.Count - 1)).address
.
.
.
end with
?Por que? Because it's probably only a matter of time before this syntax starts throwing the error, and I have to do it the other way again. (Note that when the macro editor creates the code, it's "Range('blah...')" NOT "dot-Range('blah...')"
Note, this actually takes place in a sort routine. I omitted that to keep the example simple.
[edit] I wouldn't say I solved the problem, but it does seem to be related to having an existing instance of Excel open when the code is run. When that's not the case, all is well. When it is, all is not. I suspect this has something to do with the way I instantiate it (from Access) -- something I admit I've never been completely clear on. Hence I turn to the www for clues. Here's how it appears, minus some bells and whistles:
Dim xlApp As Object, xlWB As Object, xlWS As Object, IStartedXL As Boolean
'''if no excel app is running, this throws an error. just move on and open it
On Error Resume Next
Set xlApp = GetObject(, "excel.application")
On Error GoTo err_
If xlApp Is Nothing Then
Set xlApp = CreateObject("excel.application")
IStartedXL = True
End If
Set xlWB = xlApp.Workbooks.Open(theFile_, , False, , True)
Set xlWS = xlWB.Worksheets(1)
xlApp.DisplayAlerts = False
With xlWS
.Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count).Select
.sort.SortFields.Clear
.sort.SortFields.Add Key:=Range(.Cells(1, 1), .Cells(1, .UsedRange.Columns.Count - 1)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .sort
.SetRange Range(xlWS.Cells(1, 1), xlWS.Cells(xlWS.UsedRange.Rows.Count, xlWS.UsedRange.Columns.Count))
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End With
End With