Excel Macro Trouble

[dmonney]

I am having trouble with a macro code that is run from access onto a worksheet in Excel. The part I'm having trouble with is when I try to apply a sort. I get a runtime 91 error. SRange does work to select the right area. the error comes on the sort

Set xlapp = CreateObject("Excel.Application")
    xlapp.Application.Visible = True
   
    Set xlwkb = xlapp.Workbooks.Open(sfile)

*****************lots of Macro code that already works is here*******
 
xlapp.ActiveSheet.Range(SRange).Select
 
xlapp.Selection.Sort Key1:=ActiveSheet.Range("H1"), _ 
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

*****************more Macro code that already works is here*******

 

[chergh]

Just use:

SRange.Sort Key1:=ActiveSheet.Range("H1"), _  
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _ 
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

If you want to keep selecting things (this is completely unnecessary) you would use:

xlapp.ActiveSheet.SRange.Select

At some point you will have something along the lines of:

set SRange = thisworkbook.worksheets("Sheet1").range("A1:A4")

So with your original code:

xlapp.ActiveSheet.Range(SRange).Select

This translates to:

xlapp.activesheet.range(thisworkbook.worksheets("Sheet1").range("A1:A4"))

And thats why it didnt work.

 

[dmonney]

What did you have SRange declared as? I had it as a string
ex: SRange = "A1151"

 

[dmonney]

I figured it out. SRange is declared as a range

    Set SortRange = ActiveSheet.Range("A1", "O773")

      SortRange.Sort Key1:=ActiveSheet.Range("H1"), _
        Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Thanks a bunch

 

[chergh]

I would have declared srange as a "range".

To get this working for you you need:

SRange = """A1:P151"""