I believe DIR will get the filename the way they are stored in the directory
structure. The diredctory structure is a table that initially has a small
number of rows (a row being one file). Has you add items into the directory
the table can grow larger (never smaler). When you add a new file it goes
into the first open location which can be at the end of the table or in the
middle if files were deleted. DIR displays the files in the row order of the
table.

"KT1972" wrote:

I cheked and found that my sort algorithm was mistaken. Sorry about that.

But still wonder that whether DIR command brings the files sorted or not?

"joel" wrote:

I need to know the sort method you are using. Some sorts are much faster
than others. I also trying to find other ways to do sort.

"KT1972" wrote:

Thanks for your quick respond but I've already done it with very similar
coding.

Problem is that: The folder contains more than 10000 files. I'm trying to do
a form in excel which will list 50 file names sorted and there will be
command buttons to scroll the page for next 50 or previous 50.

Please assume that we already scralled and pointed at 5000 th file. I tried
2 cases for next 50 files:
1. by using DIR command: Catch the file at 5000th point, list the following
50 files. This case is running fast. However, I don't prefer to use this
case since I'm not confident with DIR command bring files sorted. Can am I
confident?
2. I store all 10000 files to an array, sort it, catch the 5000th file and
list following 50 files on excel workbook. This case is running correctly,
but slow. Can I increase the speed?

Note: we are using XP as Operating System and Office 2003.

"joel" wrote:

Do you need the files sorted. If so put the names on a worksheet and sort
worksheet. Then display from worksheet. Otherwise, just get 50 files at a
time using dir()

Method 1
Folder = "C:\temp\"
RowCount = 1
FName = dir(Folder & "*.*")
Do While FName < ""
Range("A" & RowCount) = FName
RowCount = RowCount + 1
FName = Dir()
loop

LastRow = RowCount - 1

Set SortRange = Range("A1:A" & LastRow)
SortRange.Sort _
key1:=Range("A1"), _
Order1:=xlascending, _
header:=xlno

-------------------------------------------------------------------
Method 2
Dim MyArray()
Redim MyArray(50)
Folder = "C:\temp\"
RowCount = 1
FName = dir(Folder & "*.*")
ItemCount = 1
Do While FName < ""
Range("A" & RowCount) = FName
MyArray(ItemCount) = FName
if ItemCount = 50 then
'display items
ItemCount = 1
else
ItemCount = ItemCount + 1
end if
RowCount = RowCount + 1
FName = Dir()
loop
If ItemCount < 1 then
'display items
end if


"KT1972" wrote:

Hi,
I can list all of the files in a folder. However I need to point a specific
file in the folder and list following 50 files by every click a command
button.

# of files in the folder more than 10.000. I have taken all the file names
to an array and sorted the array. But It takes too long time because folder
is dynamic (new files are added and deleted) and I must update the array each
time.

Could you help me to find more efficient way to do it?

Thanks.