I have to save a file that may have the same name as an existing file. If it does, I append a number to the end of it to make it unique. The problem is that the file will live in three different folders in its life; Working, Review, and Archive. I need to check for similar file names in all three folders.
I created a function to return the next available suffix. If no similar file exists, it returns an empty string. If any similar files exist, it returns a number (as a string) that I can append to the file name to make it unique.
I loop through the folders and use the Dir function to get the similar names. To use Dir, I change the file I’m searching for to include an asterisk. A name like MyFile.xls becomes MyFile*.xls and will find MyFile.xls, MyFile1.xls, etc. If I find a match, I isolate the number and ultimately record the largest one I find. If there isn’t a number, the Replace statements return an empty string and the Val function converts that to zero.
Dim lSuffix As Long, lMax As Long
Dim sDirName As String
Dim sBaseName As String
Dim i As Long
Dim sTempName As String
Const sEXTENSION As String = “.xls”
sDirName = Replace(sName, sEXTENSION, “*” & sEXTENSION)
sBaseName = Replace(sName, sEXTENSION, “”)
For i = LBound(vaFolders) To UBound(vaFolders)
sTempName = Dir(vaFolders(i) & sDirName)
Do Until Len(sTempName) = 0
lSuffix = Val(Replace(Replace(sTempName, sBaseName, “”), sEXTENSION, “”)) + 1
If lSuffix > lMax Then
lMax = lSuffix
End If
sTempName = Dir
Loop
Next i
If lMax > 0 Then
GetUniqueSuffix = CStr(lMax)
Else
GetUniqueSuffix = “”
End If
End Function
An example of its use:
Dim vaFolders As Variant
Dim sFile As String
Dim lUnique As Long
vaFolders = Array(“C:Working”, “C:Review”, “C:Archive”)
sFile = “MyFile.xls”
lUnique = GetUniqueSuffix(sFile, vaFolders)
sFile = Replace(sFile, “.xls”, lUnique & “.xls”)
ActiveWorkbook.SaveAs “C:Working” & sFile
End Sub
I simply add a string generated from now() … such as filename_20080804140512.ext.
That way I don’t need to check if the number has been used (or worry about re-use).
M
Date/Time approaches don’t always work, especially when there are multiple potential users involved.
If the goal is generating distinct filenames, there are more efficient approaches involving random character strings. If the goal is distinct filenames for files which should be identical, doesn’t that produce the headache of multiple versions of the ‘truth’? If the goal is some sort of versioning system, then the answer should always be one more than the highest number previously used even if some previous versions (including the most recent) had been deleted. Even then, there are better approaches involving repositories and revision control.
The example was for a single user [“I” was mentioned a lot of times.].
A situation where there are multiple users making concurrent changes could exist – though I suspect it’s rare. The likelihood of them saving at the same time (down to the millisecond) to the same server is almost inconceivable but not impossible. But I’m not sure it couldn’t break the example posted. Two people in the organisation ran the same code – saving to different folders in the array – concurrently both find the name missing … voila, it’s not perfect.
In that scenario you could incorporate the user initials / ID too.
But seriously, I suspect it’s rare.
‘I’ could have meant you as individual DEVELOPER rather than user. There are more than a few developers who respond in this blog, and some may develop for more than just themselves.
With multiple users date/time stamping conflicts should be rare when time is to the second, but they can happen. They happened with a system I wrote that used such a scheme. Users were in a training class and several of them saved at the same time. Bingo! Unforeseen problems.
There’s really no good alternative to strictly sequential numbering with NO repeated numbers allowed. The complications are storing the next number to be used and locking it between receiving a request for it and incrementing it. This is basic transaction processing.
If you are using this in Outlook (which is where I would probably need this code) you can use Item.EntryID which is a 48 character string unique to every item :)
JP
Sorry fzz – this is in danger of getting silly.
If (you require “strictly sequential numbering”) AND (you are in an organisation big enough that there is a realistic likelihood that duplicate filetime stamps “to the second” might be generated) then maybe Excel is not the way to go. Surely a database would be a better place to allocate the unique (and incrementing) file names. Indeed, we should probably guard against re-use where the file has been deleted (inadvertently or whatever).
Whatever Excel method you choose, there is a finite chance that two users in such a busy environment might both run the code at the same time. Hopefully, they would both be attempting to create the file in the folder “Working” – one of them will get the “file exists” error message. It’s pretty straightforward to get round that rare event by having the code repeat itself if that happens with the next sequential number.
But I would still say, if the company was so big (thousands of employees concurrently generating new files with the same numerical sequence) then Excel is almost certainly not be best way of managing it :o)
If the code is being developed for a single user machine [as in the original example — Array(“C:Working”, “C:Review”, “C:Archive”) ] then Excel is fine.
The original code is inefficient. Replacing it with date/time stamping usually works better, but it COULD generate identical filenames (and I’ve seen examples of that myself in an organization with only 30 potential concurrent users).
One goal in GOOD software development is avoiding REPEATS of problems. If conflicting identical filenames occurs once, even if that were extremely rare, if there are simple ways to avoid that in future, why not do so?
For a SINGLE user, date/time stamping may be most efficient, but what’s efficient for a single user is often not best for multiple users.
Generating unique filenames is NOT an Excel-specific problem. There’s lots of OS code available as open source that shows efficient ways of generating unique temporary filenames. If a large body of code AND experience is out there, as there is in this situation, why try to reinvent suboptimal wheels?
AND… fzz is mostly correct.
The “Unusual” and “Rare” seems to always happen to me. I would hazard that if small files are saved every day several times a day by a small team, all of which have similar deadlines then the clash will happen sooner than you imagine. Especially if for whatever reason the network hangs then releases simultaneously and everyones code runs at the same time.
Good software on the other hand does not generally suffer from this problem. That said, there is still the same slim risk of two people are both seeing the same count of files and trying to save the same filename, which would probably happen at the time when two users are both trying to save at the same time. But I still prefer the clean numerical solution, if nothing else but for asthetics.
Fzz and Jan
Indeed. For both solutions there is a finite possiblity that two users attempt to save the same (therefore non-unique) filename at the same time.
If the workflow is such that all NEW files go into a single common folder “N:Working” then one of them will get a File Exists error and that is easy for either method to get round (you simply loop back and find the next). That would be fine for small organisations (
Fzz and Jan
Indeed. For both solutions there is a finite possiblity that two users attempt to save the same (therefore non-unique) filename at the same time.
If the workflow is such that all NEW files go into a single common folder “N:Working” then one of them will get a File Exists error and that is easy for either method to get round (you simply loop back and find the next). That would be fine for small organisations (20 users).
There is a more intractable problem if they have individual separate working folders on their own network space. But all this is circumvented by obtaining the unique incrementing file name from a central database, even Access.
Ditto for the hhmmss time stamp – if all new files go into a single shared folder then one will get the file exists message (which the code could deal with). Separate folders is not so easy – but again (if having a unique name was the only criterion rather than it being incrementing) could be dealt with by having a user code as part of the string FILENAME_USER_ddmmyyyyhhmmss.ext
FWIW: I do like the original code for a single user scenario. Beyond that I’d prefer a more robust solution.
once you have a time stamp for example:
rivate Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range(“A12:A500?), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 2)
.NumberFormat = “d/m/yyyy h:mm:ss AM/PM”
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub
If I wanted to add another time stamp within the work sheet how can I accomplish this task?