Late Binding - SORT driving me crazy

D

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
 
D

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!)
 
D

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
 
D

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.
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert to Number 3
Sorting help !!!!!!!!!!!!!!!!! 1
Range object causing Sort to fail 2
Sort Columns using lastrow 8
Help in sorting data 2
Sort Errors 4
Suppressing a screen 4
Writing a Sort Macro 4

Top