SetRange Help

[EAB1977]

Hello, I am using Access 2007 to automate Excel 2007. I am trying to
accomplish a simple sort feature in one of my workbooks, but the code
appears to crash on the line .SetRange Range("A1:B" & intRow).

I am still learing the 2007 Object Model, so any info on why this does
not work would be greatly appreciated.

P.S. This workbook may be used by some users who use Excel 2003.

.Sheets("Sheet4").Select
intRow = .ActiveCell.Row - 1 'getting the last row that has
data
.ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Clear
.ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Add
Key:=.Range("B1:B" & intRow) _
, SortOn:=0, Order:=2, DataOption:=0
With .ActiveWorkbook.Worksheets("Sheet4").Sort
.SetRange Range("A1:B" & intRow) 'bombs here
.Header = 0
.MatchCase = False
.Orientation = 1
.SortMethod = 1
.Apply
End With

 

[Ryan H]

The .SetRange property can only be used in Excel 2007. Is it only crashing
in 2003? What is the error description?

It seems you forgot the "." before that line. Try this:

..Range("A1:B" & intRow)

 

[Ryan H]

Oops, ignore my "." correction. That will not work.

 

[Dave Peterson]

The syntax that xl2007 records a sort won't work in xl2003 or below.

But xl2007 can use the syntax that xl2003 uses:

Dim LastRow as long
Dim LastCol as long
dim RngToSort as Excel.range
dim xlWkbk as excel.workbook

set xlwkbk = ...

with xlwkbk
With .worksheets("Sheet4") 'no need to select
'I used column A to determine the last row
lastrow = .cells(.rows.count,"A").end(xlup).row
'and row 1 to determine the last column
lastcol = .cells(1,.columns.count).end(xltoleft).column
set rngtosort = .range("A1",.cells(lastrow,Lastcol))
end with
end with

with rngtosort
.Cells.Sort _
Key1:=.Columns(2), Order1:=xlAscending, _
Header:=xlno, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
end with

========
I'm guessing that since you didn't qualify the Range("A1:B" & intRow) portion,
Access didn't know how to handle it.

 

[EAB1977]

Dave, your solution worked. Thank you.