Sorting Excel object?

[Guest]

Hi. I'm having trouble sorting an Excel spreadsheet that I create as an
object (see code below).

The code creates the spreadsheet and populates it from an outside data
source just fine.

However when I try to sort objExcel.Worksheets(1) (see the code), I get an
error message saying "Sort method of Range class failed". Thing is, when I
copy the sorting portion code into the VB editor window for the
objExcel.Worksheet, strip out "objExcel." then the sort works fine.

Any ideas on how I can sort the Excel object I've created? Thanks...

Public Sub PopulateWorksheetWithContacts()

Dim conn As Object
Dim contactSearch As Object
Dim i As Integer
Dim objExcel As New Excel.Application

Set conn = CreateObject("InterAction.Connection")

conn.Login

If conn.IsLoggedIn Then
Set contactSearch = conn.NewContactSearch
contactSearch.FolderId = "Firm Personnel"
contactSearch.Execute
Set contacts = contactSearch.Results
End If

objExcel.Workbooks.Add

If Not contacts Is Nothing Then
For i = 1 To contacts.Count
With objExcel.Worksheets(1)
.Cells(i, 1).Value = contacts(i).FirstName
.Cells(i, 2).Value = contacts(i).LastName
.Cells(i, 3).Value = contacts(i).Department
' .Cells(i, 4).Value = contacts(i).Phones(1).Phone
' .Cells(i, 5).Value = contacts(i).Eaddresses(1).Address
End With
Next i
Else
MsgBox "There has been a problem populating your Authors list"
End If

objExcel.Worksheets(1).Range("A1").Sort _
Key1:=Worksheets(1).Columns("A"), Order1:=xlAscending, _
Key2:=Worksheets(1).Columns("b"), Order2:=xlAscending, _
Key3:=Worksheets(1).Columns("c"), Order2:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

End Sub

 

[Ed]

Key 2 and Key 3 are both Order2. Try changing one to Order3 and see if that
helps.

Ed

 

[Guest]

Thanks for catching that typo re Order2 -- changing Key3 to Order3 didn't
help. I also tried replacing the sort code in the original post with the
following (setting a range and then sorting the range) but got the same error
message:

Set rngRange = objExcel.Worksheets(1).Range("A1:e416")
rngRange.Sort

Any thoughts greatly appreciated...

 

[Guest]

Thanks for catching that typo -- I tried changing the Key3 Order to Order 3
but I got the same error message. I also tried to set a range and sort the
range (see below), but again got the same error...

Set rngRange = objExcel.Worksheets(1).Range("A1:e416")
rngRange.Sort

Any more thoughts?

 

[Ed]

Chuck, here's a code snippet I use to set a sort. The only other thing I
can see off the top of my head is that you've only got one cell selected,
rather than a range. Read the Sort Method help topic. I think if you only
specify one cell, you only get one Key, which is that column. If you want
more columns, they shold be included in your range to sort. Don't forget to
specify headers, too, if you've got them.

Ed

ActiveCell.SpecialCells(xlLastCell).Select
Range(Selection, Cells(1)).Select
Selection.Sort _
Key1:=Range("AA2"), Order1:=xlAscending _
, Key2:=Range("X2"), Order2:=xlAscending _
, Key3:=Range("Y2"), Order3:=xlAscending _
, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom

 

[Guest]

Thansk for the suggestion, I modified my code as you suggested but still got
the error message "Sort method of Range class failed".

I don't think it's the sort portion of the code that's the problem -- it
works if I copy it to the worksheet I'm trying to sort.

I think the problem is in sorting an *object*, that's the only explanation
that I can think of if the code works when it's in a module in the
spreadsheet but not when it's run against an object.

So anybody have thoughts about sorting an object?

 

[Tom Ogilvy]

With objExcel.Worksheets(1)
.Range("A1").CurrentRegion.Sort _
Key1:=.Range("A1"), Order1:=1, _
Key2:=.Range("B1"), Order2:=1, _
Key3:=.Range("C1"), Order3:=1, _
Header:=2, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=1
End with

End Sub

 

[Guest]

Hi Tom

Your code solved the problem, many thanks.

As an only moderately experienced/self-taught coder, I've got a couple of
questions if you have the time -- why did your suggested code work and mine
didn't? What's the rationale for using CurrentRegion in this case?

Thanks for any light you can shed...

Chuck