Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Late Binding - SORT driving me crazy
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Late Binding - SORT driving me crazy
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Late Binding - SORT driving me crazy
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 wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,560
Default Late Binding - SORT driving me crazy
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



"Dave Peterson" wrote:

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 wrote:

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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 298
Default Late Binding - SORT driving me crazy
Also need to replace xlUp and xlYes with their numeric values.

Tim

"David" wrote in message
...
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



"Dave Peterson" wrote:

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 wrote:

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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,560
Default Late Binding - SORT driving me crazy
THANK YOU Dave and Tim, very much appreciated.


"Tim Williams" wrote:

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

Tim

"David" wrote in message
...
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



"Dave Peterson" wrote:

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 wrote:

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




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Late Binding - SORT driving me crazy
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.

Tim Williams wrote:

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

Tim

"David" wrote in message
...
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



"Dave Peterson" wrote:

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 wrote:

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


--

Dave Peterson
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 690
Default Late Binding - SORT driving me crazy
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


David wrote:
THANK YOU Dave and Tim, very much appreciated.


"Tim Williams" wrote:

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

Tim

"David" wrote in message
...
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



"Dave Peterson" wrote:

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 wrote:
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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Driving me Crazy Joel Excel Programming 9 May 31st 09 12:11 PM
From Early binding to Late binding Henk Excel Programming 1 February 12th 09 11:37 AM
Driving me crazy! RobEdgeler[_7_] Excel Programming 0 October 3rd 05 10:19 PM
Late Binding examples of binding excel application HeatherO Excel Programming 13 March 17th 05 08:19 AM
EARLY binding or LATE binding ? jason Excel Programming 6 February 26th 04 04:57 PM


All times are GMT +1. The time now is 07:17 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement