|
|
cluM09 (TechnicalUser) |
16 Jun 06 10:11 |
I have a script to generate data and write to the Excel worksheet. I want to sort the data in the worksheet but not the header.
Is there anyway to sort a particular field without sorting the header? I have an example code that is listed below, but it will sort the header also even if I indicated "A2" as the starting point:
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Cells(1, 1).Value = "Number"
objExcel.Cells(2, 1).Value = "9"
objExcel.Cells(3, 1).Value = "2"
objExcel.Cells(4, 1).Value = "60"
objExcel.Cells(5, 1).Value = "0"
objExcel.Cells(6, 1).Value = "8"
objExcel.Cells(7, 1).Value = "2"
objExcel.Cells(1, 2).Value = "Letter"
objExcel.Cells(2, 2).Value = "B"
objExcel.Cells(3, 2).Value = "C"
objExcel.Cells(4, 2).Value = "P"
objExcel.Cells(5, 2).Value = "K"
objExcel.Cells(6, 2).Value = "J"
objExcel.Cells(7, 2).Value = "O"
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A2")
objRange.Sort(objRange2)
Thanks! |
|
if you highlight the rows in a cloumn and sort them, it works ok. you may want to use macro recorder to see how excel does this. and copy the code. |
|
here's the code:
Range("A2:A6").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal |
|
|
PPettit (IS/IT--Management) |
16 Jun 06 10:57 |
I just did my first script for Excel and had problems with sorting as well. You could change your last line to something like: CODEobjRange.Sort objRange2,1,,,1,,1,1,1,False,1 Maybe this will help you. This is a part of my script that includes notes I made about each portion of the sort line: CODEDim objRange, ColumnA, ColumnB, ColumnC
Dim Key1, Order1, Key2, sortType, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation
Set objRange = objSheet.UsedRange
Set ColumnA = objExcel.Range("A2")
Set ColumnB = objExcel.Range("B2")
Set ColumnC = objExcel.Range("C2")
'Key1 = defined range
Order1 = 1 'sort order - (default) 1 = ascending, (?)2 = descending
'Key2 = defined range
'Type = (?) 'ignored for the Range object
Order2 = 1 'sort order - (default) 1 = ascending, (?)2 = descending
'Key3 = defined range
Order3 = 1 'sort order - (default) 1 = ascending, (?)2 = descending
Header = 1 'use first row as column headings - (default) 1 = Yes, (?)2 = No
OrderCustom = 1 'index of custom sort order from Sort Options dialog box - (default) 1 = Normal
MatchCase = False 'True = case sensitive, (default) False = ignore case
Orientation = 1 '(default) 1 = top to bottom, (?)2 = left to right
Order = MsgBox("Sort by description?. No = Sort by code", 3, "Sort Order")
Select Case Order
Case "6"
objRange.Sort ColumnB,Order1,ColumnA,,Order2,ColumnC,Order3,Header,OrderCustom,MatchCase,Orientation
Case "7"
objRange.Sort ColumnA,Order1,ColumnB,,Order2,ColumnC,Order3,Header,OrderCustom,MatchCase,Orientation I found the info by looking at the Object Browser within Excel's built in Visual Basic Editor (Tools->Macro->Visual Basic Editor->View->Object Browser). |
|
|
cluM09 (TechnicalUser) |
16 Jun 06 11:00 |
I finally figured it out. Thanks anyway!
Below is code:
Const xlAscending = 1
Const xlDescending = 2
Const xlYes = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Cells(1, 1).Value = "Number"
objExcel.Cells(2, 1).Value = "9"
objExcel.Cells(3, 1).Value = "2"
objExcel.Cells(4, 1).Value = "60"
objExcel.Cells(5, 1).Value = "0"
objExcel.Cells(6, 1).Value = "8"
objExcel.Cells(7, 1).Value = "2"
objExcel.Cells(1, 2).Value = "Letter"
objExcel.Cells(2, 2).Value = "B"
objExcel.Cells(3, 2).Value = "C"
objExcel.Cells(4, 2).Value = "P"
objExcel.Cells(5, 2).Value = "K"
objExcel.Cells(6, 2).Value = "J"
objExcel.Cells(7, 2).Value = "O"
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes |
|
|
 |
Talk To Other Members
Read More Threads Like This One