Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SetRange Help
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.SortFiel ds.Clear .ActiveWorkbook.Worksheets("Sheet4").Sort.SortFiel ds.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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SetRange Help
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) -- Cheers, Ryan "EAB1977" wrote: 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.SortFiel ds.Clear .ActiveWorkbook.Worksheets("Sheet4").Sort.SortFiel ds.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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SetRange Help
Oops, ignore my "." correction. That will not work.
-- Cheers, Ryan "Ryan H" wrote: 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) -- Cheers, Ryan "EAB1977" wrote: 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.SortFiel ds.Clear .ActiveWorkbook.Worksheets("Sheet4").Sort.SortFiel ds.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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SetRange Help
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 wrote: 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.SortFiel ds.Clear .ActiveWorkbook.Worksheets("Sheet4").Sort.SortFiel ds.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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SetRange Help
Dave, your solution worked. Thank you.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|