Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Excel object?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Excel object?
Key 2 and Key 3 are both Order2. Try changing one to Order3 and see if that
helps. Ed "Chuck" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Excel object?
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... "Ed" wrote: Key 2 and Key 3 are both Order2. Try changing one to Order3 and see if that helps. Ed "Chuck" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Excel object?
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" wrote: Key 2 and Key 3 are both Order2. Try changing one to Order3 and see if that helps. Ed "Chuck" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Excel object?
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 "Chuck" wrote in message ... 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" wrote: Key 2 and Key 3 are both Order2. Try changing one to Order3 and see if that helps. Ed "Chuck" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Excel object?
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? "Ed" wrote: 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 "Chuck" wrote in message ... 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" wrote: Key 2 and Key 3 are both Order2. Try changing one to Order3 and see if that helps. Ed "Chuck" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Excel object?
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 -- Regards, Tom Ogilvy "Chuck" wrote in message ... 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? "Ed" wrote: 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 "Chuck" wrote in message ... 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" wrote: Key 2 and Key 3 are both Order2. Try changing one to Order3 and see if that helps. Ed "Chuck" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Excel object?
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 "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Chuck" wrote in message ... 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? "Ed" wrote: 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 "Chuck" wrote in message ... 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" wrote: Key 2 and Key 3 are both Order2. Try changing one to Order3 and see if that helps. Ed "Chuck" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Label Options - Forms Object vs Control Box Object | Excel Discussion (Misc queries) | |||
returning pivottable object from a range object | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming |