Sorting in Excel from Access

[leishtheman]

Hello

Having tried unsuccessfully to find a solution to the below issue on both google and old threads in this forum I kindly request any suggestions as to how to solve this problem.

I'm simply trying to sort a range in an Excel file from Access, but it's erroring with everything I try. My code is below with my attempts commented out. None of them work. I need to connect to Excel via late binding as our user community as various different versions of MS Office. Any ideas what I'm doing wrong????

Many thanks in advance,

Andy




Dim xl As Object 'late binding
Dim xlb As Object 'late binding
Dim xlw As Object 'late binding
Dim xlr As Object

Set xl = CreateObject("Excel.Application")
Set xlb = xl.Workbooks.Open("C:\Users\leishmana\Desktop\q_PermRecruitment_AP11.xls")
Set xlw = xlb.worksheets("q_PermRecruitment_Periodic")
Set xlr = xlw.range("i2:i14")

xl.Visible = True
xlb.Activate
xlw.Activate

With xlb.worksheets(xlw.Name).Sort
.sortfields.Clear
'.sortfields.Add Key:=.range("I2:I14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
'.sortfields.Add Key:=xlb.Worksheets(xlw.Name).range("i2:i14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
'.sortfields.Add Key:=xlr, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
'.sortfields.Add Key:=xlw.range("i2:i14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
'.sortfields.Add Key:=activeworkbook.xlw.range("i2:i14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
'.sortfields.Add Key:=activeworkbook.worksheets(xlw).range("i2:i14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
'.sortfields.Add Key:=activeworkbook.worksheets("q_PermRecruitment_Periodic").range("i2:i14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
'.sortfields.Add Key:=xl.activeworkbook.worksheets("q_PermRecruitment_Periodic").range("i2:i14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
'.sortfields.Add Key:=xl.xlb.xlw.range("i2:i14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.Apply
End With

 

[xenou]

With late binding none of those named constants can be used. You will need to figure out the integer values. I.e., as here:
http://www.smarterdatacollection.com/Blog/?p=374

 

[leishtheman]

It works, thank you. I should have through about constant integer values.
Andy