Late Binding - SORT driving me crazy

[David]

Hello All,
I am trying to write some late binding Excel automation code, but
having problems with line# 235 below. Error is Number 1004,
Application-defined or object-defined error.



Dim objXLApp As Object
Dim objXLBook As Object
Dim obj_Sheet1 As Object
Dim obj_Sheet11 As Object
Dim LastRow As Integer
Dim Rng As Object
Const xlAscending = 1
Const xlYes = 1



180 Set obj_Sheet1 = objXLBook. _
Worksheets("Sheet1")

190 Set obj_Sheet11 = objXLBook. _
Worksheets("Sheet11")



200 LastRow = obj_Sheet11.UsedRange.Rows.Count
210 If LastRow > 1 Then
220 obj_Sheet11.Range("A1:N" & LastRow).Copy

230 obj_Sheet1.Range("A1:N1").PasteSpecial -4163

235 With obj_Sheet1
.Range(.Range("A1"), .Cells(.Rows.Count, "N").End(xlUp)).Sort _
Key1:=.Range("A2"), Order1:=xlAscending, _
Key2:=.Range("B2"), Order2:=xlAscending, _
Key3:=.Range("D2"), Order3:=xlAscending, _
Header:=xlYes
End With

240 obj_Sheet1.Range("1:1").autofilter


Thanks in advance for your assistance.
Dave

 

[Dave Peterson]

This is a guess...

xlAscending doesn't mean anything without a reference to excel.

If you open excel, go into the VBE and hit ctrl-g to see the immediate window,
you can type this and hit enter:

?xlAscending
You'll see that that constant is = 1.

?xldescending
and you'll see that it's = 2.

Kind of the same way that you'll see that
?xlpastevalues is -4163
(and you got that one!)

 

[David]

Dave,
Thank you for the quick response. Unfortunately, I have just pieced this
bit of code together so not sure I follow. I re-wrote the section, but still
get the same error. Is this how it should look?

235 With obj_Sheet1
.Range(.Range("A1"), .cells(.rows.Count, "N").End(xlup)).Sort _
Key1:=.Range("A2"), Order1:=1, _
Key2:=.Range("B2"), Order2:=1, _
Key3:=.Range("D2"), Order3:=1, _
Header:=xlYes
End With

 

[Tim Williams]

Also need to replace xlUp and xlYes with their numeric values.

Tim

 

[David]

THANK YOU Dave and Tim, very much appreciated.

Also need to replace xlUp and xlYes with their numeric values.

Tim

 

[Dave Peterson]

You have very good eyes! <vbg>

To the other David...

I'm not sure where you're automating excel from, but if it's from VBA (MSWord or
Access????), you can add:

Option Explicit
to the top of your module.

Then all those constants will look like undeclared variables and they'll be
easier to find. Yes, you will have to declare all your "real" variables,
though.

 

[Dana DeLouis]

Hi. Just something to keep in mind about the use of "UsedRange"

200 LastRow = obj_Sheet11.UsedRange.Rows.Count
210 If LastRow > 1 Then
220 obj_Sheet11.Range("A1:N" & LastRow).Copy

This "assumes" you have data in A1.
For example, if you only had data in A10:N12, the "UsedRange" would set
LastRow to 3. (The # or Rows). Then, you would be coping blank data
(A1:N3) to Sheet1.

Having said that...with 1 cells selected, Sort "assumes" the current
region. Would that help?

Sub Demo()
Dim Obj_Sheet1 As Object
Set Obj_Sheet1 = Worksheets("Sheet1")

Obj_Sheet1.Range("A1").Sort _
Key1:=Range("A2"), Order1:=1, _
Key2:=Range("B2"), Order2:=1, _
Key3:=Range("D2"), Order3:=1, _
Header:=1
End Sub

THANK YOU Dave and Tim, very much appreciated.