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.

Hi Joel, is the array zero based or one based?


"joel" wrote in message
...
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.

I set ItemCount = 1 which means it is 1 based.

"JLGWhiz" wrote:

Hi Joel, is the array zero based or one based?


"joel" wrote in message
...
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.

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.

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.

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.

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.

I don't know if this is faster but it's a different approach that might be
worth testing. It's a modification of a script I've used and I suspect it
may be more efficient to put the data on a sheet to sort when you're in
Excel but that may not be the case. Modify the line that sets the myPath
variable and give this a shot.

'--------------------------------------------

Sub AlphabetizeFileSet()

Const adVarChar = 200
Const MaxCharacters = 255

Dim myPath As String
Dim Fcnt As Integer

myPath = "C:\Scripts"

Set fso = CreateObject("Scripting.FileSystemObject")

Set targetFldr = fso.GetFolder(myPath)
Fcnt = targetFldr.Files.Count

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "FileName", adVarChar, MaxCharacters
DataList.Open

For Each myFile In targetFldr.Files
DataList.AddNew
DataList("FileName") = fso.GetFileName(myFile)
DataList.Update
Next myFile

DataList.Sort = "FileName"

' Do some stuff with sorted list of file names
DataList.AbsolutePosition = 1
MsgBox DataList.Fields.Item("FileName")
DataList.MoveNext
MsgBox DataList.Fields.Item("FileName")
DataList.AbsolutePosition = Fcnt
MsgBox DataList.Fields.Item("FileName")

Set fso = Nothing
Set DataList = Nothing

End Sub


'--------------------------------------------

Steve Yandl



"KT1972" wrote in message
...
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.