Hi

I have a sub (see below) that I use to number files with, according to
which revision the file represents.
The sub checks which filenumbers already exist in a given directory
and gives the new file the next number (e.g. projectname7 = next file
will be named projectname8).

The sub works.

However, the directory now contains several thousand files, making the
sub very slow.
I assume this is because potentially every file in the directory must
be checked in each loop.

Now, is there a smarter way of doing this? (preferably one that takes
into account the possibilty of not all revisions, being present in the
directory. The current version will name a file projectname1 if the
filename is not represented in the directory, even if projectname2
exists).

Public Sub TxtFileNumber()

'The txtfile number is determined. A search is performed for existing
files and the new file is assigned the next available number.
Dim NumFiles As Long
Dim FileName As String
Dim FileNames() As String 'This is an Array.

'The first file in the directory is found.
FileName = Dir("C:\something")
NumFiles = 1

'The array is redimensioned, without deleting content.
ReDim Preserve FileNames(NumFiles)
FileNames(NumFiles) = FileName

'Filenames are inserted into an array until there are no more in the
directory.
Do While FileName < ""
FileName = Dir() 'Next file in directory.
If FileName < "" Then
NumFiles = NumFiles + 1
ReDim Preserve FileNames(NumFiles)
FileNames(NumFiles) = FileName
End If
Loop

NextNumber = 1
Found = True

'A search is performed for projectname1. If this file is in the
directory a search for projectname2 is performed.
'This continues until projectnameX (where X is an integer) is not
found. The new file then receives this number (done elsewhere - the
number is the important part here).

While Found = True
For Each fname In FileNames
If fname = "projectname" & NextNumber & ".txt" Then
Found = True
NextNumber = NextNumber + 1
Exit For
End If
Found = False
Next fname
Wend
Range("NextNumber").Formula = NextNumber

End Sub

You could maintain a database (or excel worksheet) where you list the base
file name and the existing revisions or the basefilename and the last
revision number. Then access this to create the new file - and update the
database.

--
Regards,
Tom Ogilvy

"mr" wrote in message
om...
Hi

I have a sub (see below) that I use to number files with, according to
which revision the file represents.
The sub checks which filenumbers already exist in a given directory
and gives the new file the next number (e.g. projectname7 = next file
will be named projectname8).

The sub works.

However, the directory now contains several thousand files, making the
sub very slow.
I assume this is because potentially every file in the directory must
be checked in each loop.

Now, is there a smarter way of doing this? (preferably one that takes
into account the possibilty of not all revisions, being present in the
directory. The current version will name a file projectname1 if the
filename is not represented in the directory, even if projectname2
exists).

Public Sub TxtFileNumber()

'The txtfile number is determined. A search is performed for existing
files and the new file is assigned the next available number.
Dim NumFiles As Long
Dim FileName As String
Dim FileNames() As String 'This is an Array.

'The first file in the directory is found.
FileName = Dir("C:\something")
NumFiles = 1

'The array is redimensioned, without deleting content.
ReDim Preserve FileNames(NumFiles)
FileNames(NumFiles) = FileName

'Filenames are inserted into an array until there are no more in the
directory.
Do While FileName < ""
FileName = Dir() 'Next file in directory.
If FileName < "" Then
NumFiles = NumFiles + 1
ReDim Preserve FileNames(NumFiles)
FileNames(NumFiles) = FileName
End If
Loop

NextNumber = 1
Found = True

'A search is performed for projectname1. If this file is in the
directory a search for projectname2 is performed.
'This continues until projectnameX (where X is an integer) is not
found. The new file then receives this number (done elsewhere - the
number is the important part here).

While Found = True
For Each fname In FileNames
If fname = "projectname" & NextNumber & ".txt" Then
Found = True
NextNumber = NextNumber + 1
Exit For
End If
Found = False
Next fname
Wend
Range("NextNumber").Formula = NextNumber

End Sub