How to sort my data call from spreadsheet in alphabetical order?

[teddy b via OfficeKB.com]

Anyone can help? I want to sort my data call from excel file then populate
to word combobox. I need to capture the column 1 ID but display Column 2 data.
How to sort the data in alphabetical order?

My script as below:

With Me.cboBox
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = ".5 in; .0 in"
For iRow = 2 To wb.Worksheets("sheet1").Range("A65536").End(xlUp).Row
Set myCell = wb.Worksheets("sheet1").Cells(iRow, 2)
If myCell.Offset(0, 1).Value = 0 Then
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, -1).Value
End If
Next iRow

End With


Many Thanks.

 

[Doug Robbins]

The way that I sort the contents of listboxes or comboboxes is to use code
to copy the items to a table in a temporary Word document, then use the
Table>Sort command to sort the items before importing them back into the
control.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[teddy b via OfficeKB.com]

How to do it in my script?

The way that I sort the contents of listboxes or comboboxes is to use code
to copy the items to a table in a temporary Word document, then use the
Table>Sort command to sort the items before importing them back into the
control.

Anyone can help? I want to sort my data call from excel file then
populate

[quoted text clipped - 20 lines]

Many Thanks.

 

[Chuck Henrich]

The following code sorts your Excel data by col 2 then col 1 - you can
populate your combobox with the sorted data. The "CurrentRegion" in Excel is
the range of continguous cells with values - it's not clear from your code
whether the first worksheet row is a header row or blank or something else.
The code below assumes that row 1 is blank and anchors the current region at
A2. However if the first row is a header row, you'd start the current region
at A1 (because it's not blank) and set the Header sort parameter to "1"
(which is the constant value for Yes). The Key parameters determine which
columns/rows are sorted first, second, third, etc. The Orientation parameter
determines whether to sort by row or column (1 = row, 2 = column).

For more info check out Excel's VBE help re CurrentRegion and Sort...

With wb.Worksheets(1)
.Range("A2").CurrentRegion.Sort _
Key1:=.Range("B2"), Order2:=1, _
Key2:=.Range("A2"), Order1:=1, _
Header:=2, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=1
End With

 

[teddy b via OfficeKB.com]

Will it update my spreadsheet? My first row is header row, title for each
column. I just want to sort out the data call from my file then populate to
my word combobox list, without sort out my file. When the combobox is click
the options will be in alphabetical order. The name is display but the ID
will be capture. The name is tide to the ID. Column 1 is ID, Column 2 is the
Name.

Thanks.

The following code sorts your Excel data by col 2 then col 1 - you can
populate your combobox with the sorted data. The "CurrentRegion" in Excel is
the range of continguous cells with values - it's not clear from your code
whether the first worksheet row is a header row or blank or something else.
The code below assumes that row 1 is blank and anchors the current region at
A2. However if the first row is a header row, you'd start the current region
at A1 (because it's not blank) and set the Header sort parameter to "1"
(which is the constant value for Yes). The Key parameters determine which
columns/rows are sorted first, second, third, etc. The Orientation parameter
determines whether to sort by row or column (1 = row, 2 = column).

For more info check out Excel's VBE help re CurrentRegion and Sort...

With wb.Worksheets(1)
.Range("A2").CurrentRegion.Sort _
Key1:=.Range("B2"), Order2:=1, _
Key2:=.Range("A2"), Order1:=1, _
Header:=2, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=1
End With

Anyone can help? I want to sort my data call from excel file then populate
to word combobox. I need to capture the column 1 ID but display Column 2 data.

[quoted text clipped - 17 lines]

Many Thanks.

 

[Chuck Henrich]

It will of course update your spreadsheet - when you work with a workbook
object (eg "wb" in your code) you are working on the spreadsheet itself. If
you don't want to save the changes to your spreadsheet then when you close
the workbook object, don't save it:

wb.Close 2

2 is the constant value for wdDoNotSaveChanges. You will have to close the
workbook because you had to open it in order to get information from it.

So your amended code as posted would be:

[creating and opening the wb object here - your code not posted]With wb.Worksheets(1)
.Range("A1").CurrentRegion.Sort _
Key1:=.Range("B2"), Order2:=1, _
Key2:=.Range("A2"), Order1:=1, _
Header:=1, _ 'because you're ignoring the header row
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=1
End With

With Me.cboBox
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = ".5 in; .0 in"
For iRow = 2 To wb.Worksheets("sheet1").Range("A65536").End(xlUp).Row
Set myCell = wb.Worksheets("sheet1").Cells(iRow, 2)
If myCell.Offset(0, 1).Value = 0 Then
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, -1).Value
End If
Next iRow

End With

wb.Close 2
set wb = Nothing 'destroy the object

<<

One other thing. I notice that it looks like you're looping through 65,000+
(!!!!) rows to populate your list. Assuming that your Excel data has no gaps
(no empty cells in the columns you're looping through) you shouldn't be
setting your range to A65536. Instead, just test to see whether the cell
you've moved to is empty and if so end the loop.
--
Chuck Henrich
www.ProductivityApps.com

Will it update my spreadsheet? My first row is header row, title for each
column. I just want to sort out the data call from my file then populate to
my word combobox list, without sort out my file. When the combobox is click
the options will be in alphabetical order. The name is display but the ID
will be capture. The name is tide to the ID. Column 1 is ID, Column 2 is the
Name.

Thanks.

The following code sorts your Excel data by col 2 then col 1 - you can
populate your combobox with the sorted data. The "CurrentRegion" in Excel is
the range of continguous cells with values - it's not clear from your code
whether the first worksheet row is a header row or blank or something else.
The code below assumes that row 1 is blank and anchors the current region at
A2. However if the first row is a header row, you'd start the current region
at A1 (because it's not blank) and set the Header sort parameter to "1"
(which is the constant value for Yes). The Key parameters determine which
columns/rows are sorted first, second, third, etc. The Orientation parameter
determines whether to sort by row or column (1 = row, 2 = column).

For more info check out Excel's VBE help re CurrentRegion and Sort...

With wb.Worksheets(1)
.Range("A2").CurrentRegion.Sort _
Key1:=.Range("B2"), Order2:=1, _
Key2:=.Range("A2"), Order1:=1, _
Header:=2, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=1
End With

Anyone can help? I want to sort my data call from excel file then populate
to word combobox. I need to capture the column 1 ID but display Column 2 data.

[quoted text clipped - 17 lines]

Many Thanks.

 

[teddy b via OfficeKB.com]

Thanks for your guides.

I try your way but my spreadsheet still updated in order?

For iRow = 2 To wb.Worksheets("sheet1").Range("A65536").End(xlUp).Row
How to improve it?

cheers.

It will of course update your spreadsheet - when you work with a workbook
object (eg "wb" in your code) you are working on the spreadsheet itself. If
you don't want to save the changes to your spreadsheet then when you close
the workbook object, don't save it:

wb.Close 2

2 is the constant value for wdDoNotSaveChanges. You will have to close the
workbook because you had to open it in order to get information from it.

So your amended code as posted would be:

[creating and opening the wb object here - your code not posted]

With wb.Worksheets(1)
.Range("A1").CurrentRegion.Sort _
Key1:=.Range("B2"), Order2:=1, _
Key2:=.Range("A2"), Order1:=1, _
Header:=1, _ 'because you're ignoring the header row
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=1
End With

With Me.cboBox
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = ".5 in; .0 in"
For iRow = 2 To wb.Worksheets("sheet1").Range("A65536").End(xlUp).Row
Set myCell = wb.Worksheets("sheet1").Cells(iRow, 2)
If myCell.Offset(0, 1).Value = 0 Then
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, -1).Value
End If
Next iRow

End With

wb.Close 2
set wb = Nothing 'destroy the object

<<

One other thing. I notice that it looks like you're looping through 65,000+
(!!!!) rows to populate your list. Assuming that your Excel data has no gaps
(no empty cells in the columns you're looping through) you shouldn't be
setting your range to A65536. Instead, just test to see whether the cell
you've moved to is empty and if so end the loop.

Will it update my spreadsheet? My first row is header row, title for each
column. I just want to sort out the data call from my file then populate to

[quoted text clipped - 33 lines]

 

[Chuck Henrich]

I'm sorry but I don't understand your question.
--
Chuck Henrich
www.ProductivityApps.com

Thanks for your guides.

I try your way but my spreadsheet still updated in order?

For iRow = 2 To wb.Worksheets("sheet1").Range("A65536").End(xlUp).Row
How to improve it?

cheers.

It will of course update your spreadsheet - when you work with a workbook
object (eg "wb" in your code) you are working on the spreadsheet itself. If
you don't want to save the changes to your spreadsheet then when you close
the workbook object, don't save it:

wb.Close 2

2 is the constant value for wdDoNotSaveChanges. You will have to close the
workbook because you had to open it in order to get information from it.

So your amended code as posted would be:

[creating and opening the wb object here - your code not posted]

With wb.Worksheets(1)
.Range("A1").CurrentRegion.Sort _
Key1:=.Range("B2"), Order2:=1, _
Key2:=.Range("A2"), Order1:=1, _
Header:=1, _ 'because you're ignoring the header row
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=1
End With

With Me.cboBox
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = ".5 in; .0 in"
For iRow = 2 To wb.Worksheets("sheet1").Range("A65536").End(xlUp).Row
Set myCell = wb.Worksheets("sheet1").Cells(iRow, 2)
If myCell.Offset(0, 1).Value = 0 Then
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, -1).Value
End If
Next iRow

End With

wb.Close 2
set wb = Nothing 'destroy the object

<<

One other thing. I notice that it looks like you're looping through 65,000+
(!!!!) rows to populate your list. Assuming that your Excel data has no gaps
(no empty cells in the columns you're looping through) you shouldn't be
setting your range to A65536. Instead, just test to see whether the cell
you've moved to is empty and if so end the loop.

Will it update my spreadsheet? My first row is header row, title for each
column. I just want to sort out the data call from my file then populate to

[quoted text clipped - 33 lines]

Many Thanks.

 

[teddy b via OfficeKB.com]

Chuck Henrich wrote:
If you don't want to save the changes to your spreadsheet then when you close

the workbook object, don't save it:

wb.Close 2

Problem:
My spreadsheet still update in alphebetical order. As i do not want my file
been updated.

Chuck Henrich wrote:
One other thing. I notice that it looks like you're looping through 65,000+
(!!!!) rows to populate your list. Assuming that your Excel data has no gaps

(no empty cells in the columns you're looping through) you shouldn't be
setting your range to A65536. Instead, just test to see whether the cell
you've moved to is empty and if so end the loop.

Problem:
How to improve my code regarding this issue?

Thanks.

I'm sorry but I don't understand your question.

Thanks for your guides.

[quoted text clipped - 58 lines]

 

[Chuck Henrich]

Sorry the Close argument ("2") was wrong, it should have been the constant
for False (0) not do-not-save-changes (2). Another argument you can use when
opening the workbook is the read-only argument. The code below will open,
sort and close a workbook without changing it. (Obviously you need to specify
your own workbook not my test one.)

As for looping through all 65000+ rows, you should set a variable (lngRows)
containing the number of rows in the currentregion and only loop through
that.

Try this code. I haven't tested it but it should work.

Dim xlExcel As Object
Dim wb As Object
Dim lngRows As Long

Set xlExcel = CreateObject("Excel.Application")
xlExcel.Visible = False
Set wb = xlExcel.workbooks.Open("c:\test\test.xls", 0, 1)
'the 0 argument = do not update links
'the 1 argument = read only

With wb.Worksheets(1).Range("A1").CurrentRegion
'sort excluding heading row
.Sort _
Key1:=.Range("B2"), Order2:=1, _
Key2:=.Range("A2"), Order1:=1, _
Header:=1, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=1
'get number of rows of data
lngRows = .Rows.Count
End With

With Me.cboBox
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = ".5 in; .0 in"
For iRow = 2 To lngRows
Set myCell = wb.Worksheets("sheet1").Cells(iRow, 2)
If myCell.Offset(0, 1).Value = 0 Then
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, -1).Value
End If
Next iRow
End With

wb.Close 0
Set wb = Nothing
xlExcel.Application.Quit
Set xlExcel = Nothing

 

[Chuck Henrich]

Insert the following line before wb.Close 0 to avoid the do you want to
changes message:

wb.Saved = 1