Incrementing File Names, Rename If Exists

[DMT Dave]

Hi Guy's I can't see the wood for the trees on this one, so:



I have a table that just stores a Number tblID, field is called ID

Currently is has number 1 in the table

So my aim here is to:

Save images from camera SD card > select folder > Rename images > save all to folder

I did try using If LenDir() and Right function to check file names (number) and rename if exists, but in my opinion, create table that Looks at Max current ID then:

(If there are 6 files to rename)

Current ID is 3 for example

What i am stuck with is the best method how to rename files Image Name 1 / Image Name 2 Image Name 3 / Image Name 4 Image Name 5 / Image Name 6

Now tblID stores number 9

this way file names can never be repeated nor no need to be checked for existing name

Next time rename is selected

(If there are 2 files to rename)

New image names would be Image Name 9 / Image Name 10

Now tblID stores number 11

Please forgive me if I haven't asked correctly!!

Everything works apart from image naming and incrementing tblID to ensure no repeat of Image names

Thanks to all you great people out there

sPath = "T:\DMT Ltd\Cam Images\"        
sOldFIle = Dir(sPath & "*.jpg")
        
        strFileName = DLookup("ImageName", "tblImageName", "[ID] = " & varFileOpt)
        
        While sOldFIle <> ""
        
            i = i + 1
            
            Debug.Print i
            
            x = DMax("ID", "tblID")
            
            Set rs = CurrentDb.OpenRecordset("Select * From tblID")
                With rs
                    .Edit
                    !ID = i
                    .Update
                    .Close
                End With
                
            x = DMax("ID", "tblID") + i
        
        sNewFile = strFileName & " " & "Img " & x & ".jpg"
        
        Name sPath & sOldFIle As sPath & sNewFile
        
        sOldFIle = Dir
    
        Wend

Im not sure if i need to change .Edit in rs to .AddNew, at the moment i am thinking not because it only needs to store one number ?

Would the table update be i + x ?

[Welshgasman]

Why not just append a dateTimestamp to each file as it is saved?

[DMT Dave]

Hi WGM, yes that is probably best now you have suggested

Use dd-mm-yy-hh-nn

I guess that way, very rare we would save/rename any files after 60 seconds of the previous save

even so, i could seconds in but it will take longer than 60 seconds to take the image to save new

Thanks a lot indeed

ps: hope the wind it ok in Wales

[DMT Dave]

Hi WGM again, just a thought, while i agree date-time stamp, how does that save ie: 7 files under the same date-time stamp ?

Not adjusted/tested yet, haven't got much time until late today but crossed my mind, would that try and rename ie: 7 files with the same date-time stamp ?

That's why i though of number incrementing from a table then update the table incrementing with how many files saved ?

[Welshgasman]

You don't, you append the stamp value.

[Micron]

Time stamp isn't the way to go IMO. You cannot reliably use only seconds because there is an (e.g.) 11 second mark every minute. If you try to fix that by adding the hour, now there is a 330 every day (or perhaps even twice a day). In a network environment with multiple users, you'd need the time formatted as a number down to the second, or even millisecond and that's a long suffix. I'd use a numbering convention and just add one to it in code - assuming Windows would not do it for me by appending (1) or (2) automatically.

[DMT Dave]

Hi Micron, thank you indeed for your reply, i was unsure if i had the method correct but don't mind having a table sat there just holding a number, it is figuring out the procedure, i have done any further on this yet but i believe i am not far off with this part of the code

 i = i + 1 '= 1 to how many files            
            Debug.Print i
            
            x = DMax("ID", "tblID") '= current max number
            
            Set rs = CurrentDb.OpenRecordset("Select * From tblID")
                With rs
                    .Edit
                    !ID = i ' Update the table with the total count of files
                    .Update
                    .Close
                End With
                
            x = DMax("ID", "tblID") + i ' return the numbers after updating table
        
        sNewFile = strFileName & " " & "Img " & x & ".jpg" ' new files are called 3,4,5,6,7,8,9
        
        'next files should be called 10,11,12 (assuming there is 3 more)

i am just struggling to work the route plan assuming this method works

[Welshgasman]

Time stamp isn't the way to go IMO. You cannot reliably use only seconds because there is an (e.g.) 11 second mark every minute. If you try to fix that by adding the hour, now there is a 330 every day (or perhaps even twice a day). In a network environment with multiple users, you'd need the time formatted as a number down to the second, or even millisecond and that's a long suffix. I'd use a numbering convention and just add one to it in code - assuming Windows would not do it for me by appending (1) or (2) automatically.

My thoughts were still numberin in a simple loop but adding dateTimeStamp.
Then you would get
Image1-20240122-11:13:11
Image2-20240122-11:15:02
Image1-20240123-11:09:15
Image2-20240123-11:21:22

etc

[DMT Dave]

@WGM, i can totally see your method, so in your method it would be for each file DateAdd 1 second, i am trying to workout your method ?

Kind of get it, will come back to it but as always you guys are very helpful

[Welshgasman]

No,I would not add a second, I would just use the actual time the file is saved.

[Micron]

Many ways to skin the proverbial cat. After reviewing WGM's explanation I can see how that would work reliably in a single user environment as long as you're not looping through files with code. If you were, I wonder if 2 files could get the same time stamp and the same renamed name in the same second. However, if file name is different in each case, why do you need any other sort of suffix? Or does "Image Name" indicate that the first part of the image name is always the same as you're suggesting with your example?

As for your other method, are you not storing the file names (assuming the first part is always the same) along with the numbered suffix in a table? Maybe then you could get DMax of something in the name. Updating the table to the next available number is OK as long as you don't mind if file suffixes have no gaps. It may happen that you create the filename but table update errs thus you get the same suffix next time. Or table updates but the filename part errs out so you have gaps in the suffix. Perhaps none of that would matter.

[DMT Dave]

Hi Micron, i should share the full procedure, so a table with folder names, there is an option to save to that folder or generate a new folder (Case 5) in the table field Image Name is called Add New so if a folder doesn't exist you want to save files, you can Mk Dir called new name

then the file name is called the same as folder name, file names will always be:

In DMT Image Folder all files will be called > DMT Image Number

Here is the saving procedure i have:

1: I have this code that transfers all files from Camera SD Card

varOpt = Me.optCamera


Select Case varOpt


    Case Is = False
       
Me.optOpenImages = False
Me.optRename = False
Me.optResize = False
Me.optSend = False
Me.optStoredImages = False
    
    Exit Sub
    
    Case Else
    Me.txtImageID = ""
    Me.optOpenImages = True
    
    strPathFrom = Forms!frmMainMenu!cboDrive & "\DCIM\101MSDCF\"
    strPathTo = "T:\DMT Ltd\Cam Images\"
    
    strFiles = Dir(strPathFrom & "*.jpg")


    While strFiles <> ""
        i = i + 1
        
        strName = i & " " & Format(Now(), "dd-mm-yy") & ".jpg"


        Name strPathFrom & strFiles As strPathTo & strName
        
    strFiles = Dir
    
    Wend
        
    MsgBox ("Images Saved:" & vbCrLf & vbCrLf & _
        "You Can Now Eject The SD Card From Your Computer"), vbInformation + vbOKOnly, "FILES MOVED"




End Select

2: now files are transferred to a local folder,
rename them into either current folder name or optional add new folder

varOpt = Me.optRename


Select Case varOpt


    Case Is = False
    
    Exit Sub


    Case Else
    
    Set rs = CurrentDb.OpenRecordset("Select * From tblImageName")
    With rs
        Do Until rs.EOF
            strBody = strBody & Chr(149) & " " & rs.Fields("ID") & " " & Chr(149) & " " & rs.Fields("ImageName") & vbCrLf
        rs.MoveNext
        Loop
        
    End With
    
    varFileOpt = InputBox("Enter What You Want To Call Your File Names ?" & vbCrLf & vbCrLf & _
        strBody, "ENTER NUMBER")
        
    Me.txtImageID = varFileOpt


End Select
   
   Select Case varFileOpt
        
    Case Is = 5
        
        strSaveName = InputBox("Enter Your New File Name That Wasn't Listed ?", "STORE NEW FILE NAME")
        Set rs = CurrentDb.OpenRecordset("Select * From tblImageName")
            With rs
                .AddNew
                !ImageName = strSaveName
                .Update
                .Close
            End With
        MsgBox ("New Image Name Saved:" & vbCrLf & vbCrLf & _
            "Now Select Rename Option Again"), vbInformation + vbOKOnly, "NEW NAME SAVED"
            
        Me.optRename = False
        
    Case Is = 18
    
        Exit Sub
        
    Case Else
    
    sPath = "T:\DMT Ltd\Cam Images\"
        sOldFIle = Dir(sPath & "*.jpg")
        
        strFileName = DLookup("ImageName", "tblImageName", "[ID] = " & varFileOpt)
        
        While sOldFIle <> ""
        
            i = i + 1 '= 1 to how many files
            
            Debug.Print i
            
            x = DMax("ID", "tblID") '= current max number
            
            Set rs = CurrentDb.OpenRecordset("Select * From tblID")
                With rs
                    .Edit
                    !ID = i ' Update the table with the total count of files
                    .Update
                    .Close
                End With
                
            x = DMax("ID", "tblID") + i ' return the numbers after updating table
        
        sNewFile = strFileName & " " & "Img " & x & ".jpg" ' new files are called 3,4,5,6,7,8,9
        
        'next files should be called 10,11,12 (assuming there is 3 more)
        
        Name sPath & sOldFIle As sPath & sNewFile
        
        sOldFIle = Dir
    
        Wend
        
        
    End Select

Hope this helps you to help me which once again is greatly appreciated

[moke123]

Personally I would consider using FSO for this.

Assuming your moving all the files from one folder to another, renaming them with a prefix and a number you could do something like this:

this is only slightly tested so may need tweaking.

Public Sub MoveMyFile(SourceFolder As String, TargetFolder As String, FilePrefix As String)

    Dim fso As New Scripting.FileSystemObject
    Dim fol As Folder, fil As File
    Dim NewName As String, NewPath As String
    
    Set fol = fso.GetFolder(SourceFolder)
    
    For Each fil In fol.Files
    
        NewName = FilePrefix & fGetNextNumber(TargetFolder) & "." & fso.GetExtensionName(fil.Path)
    
        NewPath = fso.BuildPath(TargetFolder, NewName)
        
        fso.MoveFile fil.Path, NewPath
        
    Next

    set fso = nothing
    
End Sub

Public Function fGetNextNumber(FPath As String) As Long

    Dim fso As New Scripting.FileSystemObject

    Dim fol As Folder

    Set fol = fso.GetFolder(FPath)

    fGetNextNumber = fol.Files.Count + 1  'Add 1 to the count of files in folder.

    Set fol = Nothing

End Function

[CJ_London]

The technique I use is similar to timestamp except I create a record in a table with the file name and rename the file with original file name & “-“ & pk

i then have other tables to multi categorize the picture

[moke123]

A minor tweak to my code to eliminate the function call, all code in one procedure.
This will rename the file with the folder name and a sequential number and move it to the designated folder.

Public Sub MoveRenameFile(SourceFolder As String, TargetFolder As String)

    Dim fso As New Scripting.FileSystemObject
    Dim fol As Folder, fil As File, Tfol As Folder
    Dim NewName As String, NewPath As String
    Dim PFix As String
    
    PFix = Mid(TargetFolder, InStrRev(TargetFolder, "\"))
    
    Set fol = fso.GetFolder(SourceFolder)
    Set Tfol = fso.GetFolder(TargetFolder)
    
    For Each fil In fol.Files
     
        NewName = PFix & "_" & Tfol.Files.Count & "." & fso.GetExtensionName(fil.Path)
    
        NewPath = fso.BuildPath(TargetFolder, NewName)
        
        fso.MoveFile fil.Path, NewPath
        
    Next
    
    Set fol = Nothing
    Set Tfol = Nothing
    
End Sub

Attached Example. Set up a folder and put a couple images( or any file) in it, and set up another folder as a destination.
On the form choose those folders and click on the appropriate command button.

Edit: this line

NewName = PFix & "_" & Tfol.Files.Count & "." & fso.GetExtensionName(fil.Path)

should be changed to

NewName = PFix & "_" & Tfol.Files.Count + 1 & "." & fso.GetExtensionName(fil.Path)

so the sequential numbers start at 1 and not 0.