Solved

Secondary Sort

Posted on 2004-09-10
15
368 Views
Last Modified: 2013-12-18
Does anyone know what the code would be to do a secondary sort when exporting to Excel. I want it to first sort by column A and then by column B.

      Set xlSheet = xlApp.Workbooks(1).Worksheets("Sheet1")
      Set range1 = xlsheet.Range("A2: B2000")
      Call range1.Sort(xlsheet.Columns("A"), , , , , , , 1)
      
0
Comment
Question by:jforget1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 

Author Comment

by:jforget1
ID: 12027160
Also does anyone know how to get both values on a checkbox to export to a spreadsheet. I have a field which has both items selected but only the first value is exporting.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12027196
It should be like this

     Call range1.Sort(xlsheet.Range("A1"), xlsheet.Range("B1"))

~Hemanth
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12027208
jforget1,
> Also does anyone know how to get both values on a checkbox to export
> to a spreadsheet. I have a field which has both items selected but only
> the first value is exporting.

No.. you can't ! The checkbox might contain multi values.. but those are selected are the one associated for that doc. You can set another field which lists all the choices and use that to export
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 63

Expert Comment

by:Zvonko
ID: 12027289
Second column is sorted like this:
   Call range1.Sort(xlsheet.Columns("A"), , xlsheet.Columns("B"), , , , , 1)

0
 

Author Comment

by:jforget1
ID: 12027295
It is still not sorting by the B column.

As far as the second item, the field can have 2 values, can't it pull both if selected? It is column C, the peripherals field.  

here is all the code

Sub Click(Source As Button)
      
      Dim session As New NotesSession
      Dim workspace As New NotesUIWorkspace
      Dim uidoc As NotesUIDocument
      Dim db As NotesDatabase
      Dim view As NotesView
      
      Dim Mgrdc As NotesDocumentcollection
      Dim MgrDoc As notesDocument
      
      Dim Repdc As NotesDocumentCollection    
      Dim RepDoc As NotesDocument
      Dim mgrview As NotesView    
      Dim RepView As NotesView
      Dim txt_Agency As String  
      Dim tmp_NotesName As NotesName
      Dim MgrKey, RepKey, ADKey As String
      
      Dim xlApp As Variant
      Dim xlSheet As Variant
      Dim i As Integer
      Dim x As Integer
      Set db = session.CurrentDatabase
      Set Mgrdc = db.UnprocessedDocuments
      Set Mgrdoc = Mgrdc.GetFirstDocument
      On Error Resume Next
      
      RepKey = "ibpc_order_form"         
      
      Set Repview = db.GetView( "(approved)")  
      Set RepDoc  = Repview.GetDocumentByKey( RepKey )    ' form name must be first colum and sorted
      
      Print "Creating Excel Workbook..."
      Set xlApp = CreateObject("Excel.application")
      Print "Creating Excel Worksheet for Refresh..."
      xlApp.Workbooks.Add
      Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
      
      XlApp.Columns("A").Select
      With XlApp.Selection
            .ColumnWidth=6
            .WrapText=1
            .HorizontalAlignment = xlCenter
      End With
      
      XlApp.Columns("B").Select
      With XlApp.Selection
            .ColumnWidth=6
            .WrapText=1
            .HorizontalAlignment = xlCenter
      End With
      
      XlApp.Columns("C").Select
      With XlApp.Selection
            .ColumnWidth=10
            .WrapText=1
            .HorizontalAlignment = xlCenter
      End With
      
      XlApp.Columns("D").Select
      With XlApp.Selection
            .ColumnWidth=15
            .WrapText=1
            .HorizontalAlignment = xlCenter
      End With
      
      XlApp.Columns("E").Select
      With XlApp.Selection
            .ColumnWidth=30
            .WrapText=1
            .HorizontalAlignment = xlCenter
      End With
      
      XlApp.Columns("F").Select
      With XlApp.Selection
            .ColumnWidth=15
            .WrapText=1
            .HorizontalAlignment = xlCenter
      End With
      
      XlApp.Columns("G").Select
      With XlApp.Selection
            .ColumnWidth=12
            .WrapText=1
            .HorizontalAlignment = xlCenter
      End With
      
      XlApp.Columns("H").Select
      With XlApp.Selection
            .ColumnWidth=5
            .WrapText=1
            .HorizontalAlignment = xlCenter
      End With
      
      XlApp.Columns("I").Select
      With XlApp.Selection
            .ColumnWidth=6
            .WrapText=1
            .HorizontalAlignment = xlCenter
      End With
      
      XlApp.Columns("J").Select
      With XlApp.Selection
            .ColumnWidth=10
            .WrapText=1
            .HorizontalAlignment = xlCenter
      End With
      
      xlSheet.Cells(1,1).Value = "Office"     ' change your column headings
      xlSheet.Cells(1,2).Value = "Model"
      xlSheet.Cells(1,3).Value = "Peripherals"
      xlSheet.Cells(1,4).Value = "Contact"
      xlSheet.Cells(1,5).Value = "Address"
      xlSheet.Cells(1,6).Value = "Address2"
      xlSheet.Cells(1,7).Value = "City"
      xlSheet.Cells(1,8).Value = "State"
      xlSheet.Cells(1,9).Value = "Zip"
      xlSheet.Cells(1,10).Value = "Cost Center"
      
      x = 3
      While Not ( RepDoc Is Nothing )                 ' as long as it has a handle on a document do the following
            If  RepDoc.Form(0) = "ibpc_order_form" Then               
                  
                  xlSheet.Rows("1:1").Select                  
                  xlSheet.Rows(1).WrapText =True
                  
                  xlSheet.Cells(x,1).Value = RepDoc.office_num_adjusted
                  xlSheet.Cells(x,2).Value = RepDoc.model
                  xlSheet.Cells(x,3).Value = RepDoc.peripherals
                  xlSheet.Cells(x,4).Value = RepDoc.contact
                  xlSheet.Cells(x,5).Value = RepDoc.address
                  xlSheet.Cells(x,6).Value = RepDoc.address2
                  xlSheet.Cells(x,7).Value = RepDoc.city
                  xlSheet.Cells(x,8).Value = RepDoc.state
                  xlSheet.Cells(x,9).Value = RepDoc.zip
                  xlSheet.Cells(x,10).Value = RepDoc.ccc                  
                  
                  RecNum = x - 3 ' optional
                  Print "Gathering Data.  Record Number: " + RecNum  ' optional
                  x = x + 1                    
            Else                                    
                  Set RepDoc = Repview.GetLastDocument                  ' While not (RepDoc) is nothing
            End If                  
            Set RepDoc = Repview.GetNextDocument(RepDoc)
      Wend
      
      Print "Data Collection Complete."
      
      Set xlSheet = xlApp.Workbooks(1).Worksheets("Sheet1")
      Set range1 = xlsheet.Range("A2: J2000")
      Call range1.Sort(xlsheet.Range("A1"), xlsheet.Range("B1"))
      
      
      Msgbox "Your Refresh Report is ready in Excel. Click OK and then open the Excel file flashing near the bottom of your screen.  ", 64, "Excel Export"
      
      xlApp.Visible = True
      xlApp.UserControl = True
End Sub
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 12027318
The second postional parameter is for first column's sort direction and is default to: Ascending
0
 
LVL 63

Accepted Solution

by:
Zvonko earned 125 total points
ID: 12027331
Change it to:
    Call range1.Sort(xlsheet.Range("A1"), , xlsheet.Range("B1"))
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 12027344
If the Designer does not accept that, then try this:
   Call range1.Sort(xlsheet.Range("A1"), Null, xlsheet.Range("B1"))

0
 

Author Comment

by:jforget1
ID: 12027371
Zvonko, that fixed it. Do you know how I can have 2 values from a single checkbox item export to the sheet?
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 12027456
Which one was the fix? The one with the Null?

And for the checkbox Items do as for all other items. All values will be put in the single destination Excel cell.

0
 

Author Comment

by:jforget1
ID: 12027536
It was the first  without the Null.

For the second item, can I make another column and have the second value fill their?
0
 

Author Comment

by:jforget1
ID: 12027624
Figured out the checkbox feed. THanks for the help.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 12028112
Thanks for the points.
But why didn't you post your solution about checkboxes?
I assume it is like this:
xlSheet.Cells(x,11).Value = RepDoc.chkBox(0)
xlSheet.Cells(x,12).Value = RepDoc.chkBox(1)

0
 

Author Comment

by:jforget1
ID: 12028744
Yes you are right I should have posted it and that is what I did.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 12028765
Its ok. Thanks for the feedback.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Lotus Notes and VMWare require a double-click 22 1,460
How to change default font size and font in Lotus Notes 9 7,565
Lotus Notes 8.5 1 163
Add code to Lotus Notes view column 2 135
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question