How to find the latest file name with integer value in its name ?

[Yousaf Shah]

Hello everybody
Suppose there is folder "A", Drive "C:\" with 15 pictures named like following
P6V15-1.jpg
P6V15-2.jpg
P6V15-3.jpg
P6V15-4.jpg
P6V15-5.jpg
P6V15-6.jpg
P7V25-1.jpg
P7V25-2.jpg
P7V25-3.jpg
P7V25-4.jpg
P7V25-5.jpg
P7V25-6.jpg
P8V5-1.jpg
P8V5-2.jpg
P8V5-3.jpg

In example above "P6V15-1.jpg" is the first picture for PateintID 7 on his 15th visit and "P6V15-6.jpg" is the last picture of same patient on same visit. How can I find in code that P6V15-6.jpg is the last picture? I want this because I want to name the next picture "P6V15-7.jpg" via code

I have used this code to find the number of pictures with specific visit by a patient but can not find the last part of the latest picture

Function FileCount(strPath As String, StrName As String) As Integer
Dim objFSO As Object, objFolder As Object, objFile As Object, i As Integer 'varDate As Variant, strName As String
 
' Specify the folder...
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
 
i = 0
For Each objFile In objFolder.Files
If objFile.Name Like StrName & "*.jpg" Then
i = i + 1
End If
Next 'objFile
 
FileCount = i
 
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Function

Thanks in anticipation

[PhilOfWalton]

It's quite complicated, but here are the steps that you need to do:

1) Load the file names into an array
2) Sort the file names alphabetically (There is no guarantee that the Scripting.FileSystemObject will read the files in alphabeticical order)
3) Use the Instr function to look for the dash -
4) Save the Patient part of the file name
5) Use the Instr function to look for the folowing dot.
6) Get the numeric part of the file name (it starts 1 letter after the patient part and end 1 letter before the dot.
7) Convert that to an integer. (Note that the sort would give files in the order

P6V15-1.jpg
P6V15-11.jpg
P6V15-12.jpg
P6V15-2.jpg
 

8) Save the integer
9) Read the next file name from the array.
10) If the Patient part is the same as the saved patient part then If the numeric part is greater, save that interger instead.
11) If the Patient part is not the same as the saved patient part, then the last record is the maximum that you are wanting. Save the new patinet part and set the numeric part back to 0.
Then loop round again from 3)

This may be over complicated, because I suspect you could load only the files for the patient you are interested in into the array.

Phil

[jforbes]

The following code will probably do what you are asking for. It's based on what you provided. It requires you to provide the Path and the first part of the File Name, and it will return the next File Name to be used:

Function getNextFileName(strPath As String, StrName As String) As String
    Dim objFSO As Object, objFolder As Object, objFile As Object, i As Integer 'varDate As Variant, strName As String
    Dim iMax As Integer
 
    ' Specify the folder...
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(strPath)
 
    ' Find the current Max
    For Each objFile In objFolder.Files
        If objFile.Name Like StrName & "*.jpg" Then
            i = Val(Mid(objFile.Name, Len(StrName) + 1, Len(objFile.Name) - (Len(StrName) + 4)))
            If i > iMax Then iMax = i
        End If
    Next 'objFile
 
    ' Determine next File Name
    iMax = iMax + 1
    getNextFileName = StrName & iMax & ".jpg"
 
    Set objFSO = Nothing
    Set objFolder = Nothing
    Set objFile = Nothing
 
End Function

But I have a feeling that there may be more needed, which is what Phil seems to be addressing. The above function will require you to know the both the Patient as well as the Visit Number. If you know the Visit Number, then great, but if you are attempting to figure that out also, things will get more complicated. Either way, let us know what you come up with.

[PhilOfWalton]

I suspect that just counting the "right files" won't work.
If files were originally 1,2,3,4 and file 3 has been deleted, counting will say there 3 files, and then try to add a duplicate file 4.

Phil

[PhilOfWalton]

Sorry, jforbes. Just re-read your post, and humbly withdraw my last post. That should work with the provisos you mention other possibly than I'm not sure whether you have to convert i to an integer.

i = CInt(Mid(objFile.Name, Len(StrName) + 1, Len(objFile.Name) - (Len(StrName) + 4)))
 

Phil

[ADezii]

I was able to arrive at a solution using a different approach:

1. Recursively called the Dir() Function to load all File Names into a Temporary Table.
2. Created a simple TOTALS Query which GROUPS BY the File Name Prefixes, namely: P6V15, P7V25, etc. The 2nd Column on this Query displays the MAX Integer Value representing the Sequence Number in the File Name, such as: 6 in P7V25-6.jpg, 122 in P54V18-122.jpg, etc. The Final Field simply concatenates all three Fields to produce the actual 'Last' File Name, such as: P7V25-6.jpg.
3. The advantage in this approach, as I see it, is that you can display all the Last File Names for your Prefixes as previously indicated.
4. Admittedly this approach is a little unorthodox, so I'll post the Code along with a Demo if you wish, only if someone specifically requests it.

[Yousaf Shah]

Thanks jforbes
I have changed your code a little to fulfill my requirements and its working fine.
New code is as follows

 
 
    Expand|Select|Wrap|Line Numbers
 
 
 
    
 
 Function NextFileInt(StrPath As String, StrName As String) As Integer
 
Dim objFSO As Object, objFolder As Object, objFile As Object, i As Integer, iMax As Integer 'varDate As Variant, strName As String
  
 
    ' Specify the folder...
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
Set objFolder = objFSO.GetFolder(StrPath)
  
 
    ' Find the current Max
 
For Each objFile In objFolder.Files
 
 If objFile.Name Like StrName & "*.*" Then
 
  i = Left$(Replace(objFile.Name, StrName, ""), InStrRev(Replace(objFile.Name, StrName, ""), ".") - 1)
 
   If i > iMax Then iMax = i
 
 End If
 
Next 'objFile
  
 
iMax = iMax + 1
 
NextFileInt = iMax
  
 
Set objFSO = Nothing
 
Set objFolder = Nothing
 
Set objFile = Nothing
 
End Function