jforget1
asked on
Secondary Sort
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).Workshe ets("Sheet 1")
Set range1 = xlsheet.Range("A2: B2000")
Call range1.Sort(xlsheet.Column s("A"), , , , , , , 1)
Set xlSheet = xlApp.Workbooks(1).Workshe
Set range1 = xlsheet.Range("A2: B2000")
Call range1.Sort(xlsheet.Column
It should be like this
Call range1.Sort(xlsheet.Range( "A1"), xlsheet.Range("B1"))
~Hemanth
Call range1.Sort(xlsheet.Range(
~Hemanth
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
> 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
Second column is sorted like this:
Call range1.Sort(xlsheet.Column s("A"), , xlsheet.Columns("B"), , , , , 1)
Call range1.Sort(xlsheet.Column
ASKER
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.applic ation")
Print "Creating Excel Worksheet for Refresh..."
xlApp.Workbooks.Add
Set xlSheet = xlApp.Workbooks(1).Workshe ets(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(Re pDoc)
Wend
Print "Data Collection Complete."
Set xlSheet = xlApp.Workbooks(1).Workshe ets("Sheet 1")
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
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.applic
Print "Creating Excel Worksheet for Refresh..."
xlApp.Workbooks.Add
Set xlSheet = xlApp.Workbooks(1).Workshe
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(Re
Wend
Print "Data Collection Complete."
Set xlSheet = xlApp.Workbooks(1).Workshe
Set range1 = xlsheet.Range("A2: J2000")
Call range1.Sort(xlsheet.Range(
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
The second postional parameter is for first column's sort direction and is default to: Ascending
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If the Designer does not accept that, then try this:
Call range1.Sort(xlsheet.Range( "A1"), Null, xlsheet.Range("B1"))
Call range1.Sort(xlsheet.Range(
ASKER
Zvonko, that fixed it. Do you know how I can have 2 values from a single checkbox item export to the sheet?
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.
And for the checkbox Items do as for all other items. All values will be put in the single destination Excel cell.
ASKER
It was the first without the Null.
For the second item, can I make another column and have the second value fill their?
For the second item, can I make another column and have the second value fill their?
ASKER
Figured out the checkbox feed. THanks for the help.
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)
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)
ASKER
Yes you are right I should have posted it and that is what I did.
Its ok. Thanks for the feedback.
ASKER