Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Renaming Each File


    Hi Guy's, is there a method after counting the files in a folder that each file can be renamed after checking and based on the file count ?

    ie:IMG_20190917_105922 JPG File
    ie:IMG_20190917_105921 JPG File

    So the code I have will confirm there are 2 images, all Dim's are set correctly and imgPath so I haven't added this to shorten the code

    I am looking to rename the files via VBA in which I can achieve via name Path & FileName, NewPath & NewFileName

    But how do I retrieve the file names so that 1st JPG file name is renamed then moved to next file name based being 2 files

    Same applies if there are 6 JPG Files.

    Hope this makes sense


    Code:
    If MsgBox("Add Your Device To The Computer" &Chr(10) & Chr(10) & _
    "An Image Destination Window Will Appear For:"& Chr(10) & Chr(10) & _
    Chr(149) & " " & strCustomer & "" & Chr(149) & Chr(10) & Chr(10) & _
    "Copy The Files From Your Device Into ThisWindow", vbOKCancel + vbInformation, "IMAGE DESTINATION") =vbCancel Then
    DoCmd.CancelEvent
    Else
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.InitialFileName = imgPath
    With fd
    .InitialFileName = imgPath
    If .Show = -1 Then
    .Show
    DoCmd.RunCommand acCmdAppMinimize
    End If
    End With
    End If
    Set fso =CreateObject("Scripting.FileSystemObject")
    Set objFiles = fso.getfolder(imgPath).Files
    imgQty = objFiles.Count
    MsgBox ("File Quantity Confirmed = " &imgQty), vbQuestion + vbYesNo, "FILE COUNT"
     
    End If
    The code confirms there are 2 files, i now want to rename file 1 to Joe Bloggs-1.jpg and File 2 to Joe Bloggs-2.jpg, if more files then Joe Bloggs-3.jpg etc etc..........

    Thanks in advance

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Suggest you loop through file folder and load file path and name to a 2-dimension array then loop through array to rename each file. Windows tends to re-sort a folder after a file is named. I am not certain this will still happen during a programmatic renaming but I am hesitant to even test because of potential for endless loop or corrupting files.

    Do you want to rename only files that begin with "IMG"?

    Use Name method.

    Name "C:\path\OldFileName"AS"C:\path\NewFileName"

    Can even move and rename a file simultaneously.

    Name "C:\OldDir\OldFileName"AS"C:\NewDir\NewFileName"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi June7, yes fab, thank you for your response, totally correct, I use Name FilePath & FileName as NewPath & NewName quite a lot but the file name is already on the database on those occasions so that's easy but your question is a prefect question YES, any file that starts with IMG

    Once the new file name is attached to an email and sent then its auto copied to a back up folder (this I have done successfully) I will then use Kill File to empty that folder for more images the next day for example so it would be all files that start with IMG (not done Kill file yet but I can do that), this should help as the important part is, no more files will be added until the current renamed from IMG to NewFileName is Killed

    My problem is finding what the file name is called to rename

  4. #4
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I thought you wanted to rename all image files in the directory and came up with this...
    Code:
    Sub LoopThroughFiles(txtName As String)
     
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object
    Dim i As Integer
    Dim oExt As String
    Dim pth As String
    
    
    i = 1
    
    
    pth = GetFolderPath
    'Debug.Print pth
     
    Set oFSO = CreateObject("Scripting.FileSystemObject")
     
    Set oFolder = oFSO.GetFolder(pth)
     
    For Each oFile In oFolder.Files
    
    
    If IsImageFile(oFile.Name) Then
    
    
    oExt = Mid(oFile.Name, InStrRev(oFile.Name, "."))
    'Debug.Print oExt
     
        Name pth & "\" & oFile.Name As pth & "\" & txtName & "-" & i & oExt
        
        i = i + 1
        
     End If
       
     
    Next oFile
     
    End Sub
    
    
    
    
    
    
    Public Function GetFolderPath() As String
    
    
        Dim strFilePath As String
        With Application.FileDialog(msoFileDialogFolderPicker)
            ' show the file picker dialog box
            If .Show <> 0 Then
                strFilePath = .SelectedItems(1)
            
                GetFolderPath = strFilePath
                
            End If
        End With
    End Function
    
    
    Public Function IsImageFile(strPath As String) As Boolean
    Dim oExt As String
    oExt = Mid(strPath, InStrRev(strPath, ".") + 1)
    
    
    Select Case oExt
    
    
    Case "jpg", "jpeg", "png", "bmp"
    IsImageFile = True
    Case Else
    IsImageFile = False
    End Select
    
    
    End Function

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If folder location is always same and contains only jpg files, consider:
    Code:
    Public Sub RenameFiles()
    On Error GoTo Err_Handler
        Dim strPath As String, strFile As String, aryFiles() As String, x As Integer
        strPath = "\\servername\folderpath\"
        strFile = Dir(strPath & "*.*")
        ReDim aryFiles(0)
        Do Until strFile = vbNullString
            aryFiles(UBound(aryFiles)) = Left(strFile, InStrRev(strFile, ".") - 1)
            strFile = Dir
            If strFile <> "" Then ReDim Preserve aryFiles(UBound(aryFiles) + 1)
        Loop
        For x = 0 To UBound(aryFiles)
            Name strPath & aryFiles(x) & ".jpg" As strPath & "Image" & x + 1 & ".jpg"
        Next
    Exit_Handler:
        Exit Sub
    Err_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Exit_Handler
    End Sub
    
    A 1-dimension array was sufficient.

    A Collection object could probably be used instead of array but I've never coded a Collection.

    Now I see moke's code. No idea if it will encounter issue I expressed concern about in earlier post. I have read other threads expressing same concern. However, it does handle possibility of other file types in folder.



    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    No idea if it will encounter issue I expressed concern about in earlier post.
    I'm not sure either although I've done similiar stuff without issue. My initial thought was using a collection but stuck with fso because OP was using it.

    Here's essentially the same code but using a collection.

    Code:
    Sub test()
    
        LoopThroughFiles "Joe Bloggs"
    
    End Sub
    
    
    Sub LoopThroughFiles(txtName As String)
    
        Dim oFSO As Object
        Dim oFolder As Object, oFile As Object
        Dim i As Integer
        Dim oExt As String, pth As String
        Dim var
        Dim colPix As New Collection
    
        i = 1
    
        pth = GetFolderPath
    
        Set oFSO = CreateObject("Scripting.FileSystemObject")
    
        Set oFolder = oFSO.GetFolder(pth)
    
        For Each oFile In oFolder.Files
    
            If IsImageFile(oFile.Name) Then
    
                On Error Resume Next
    
                colPix.Add oFile.Name, CStr(oFile.Name)
    
            End If
    
        Next oFile
    
        For Each var In colPix
    
            oExt = Mid(var, InStrRev(var, "."))
    
            Name pth & "\" & var As pth & "\" & txtName & "-" & i & oExt
    
            i = i + 1
    
        Next var
    
        Set oFSO = Nothing
        Set oFolder = Nothing
        Set colPix = Nothing
    
    End Sub

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    After thinking about it some more, files won't re-sort because not viewing and manually renaming with File Explorer. So decided to give it a try and works to rename within the folder loop, no array nor collection needed.
    Code:
        Dim strPath As String, strFile As String, x As Integer
        strPath = "\\servername\path\"
        strFile = Dir(strPath & "*.*")
        x = 1
        Do Until strFile = vbNullString
            Name strPath & strFile As strPath & "ABC" & x & ".jpg"
            strFile = Dir
            x = x + 1
        Loop
    
    Last edited by June7; 09-19-2019 at 12:26 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hey Moke and June7, thank you very kindly for adding these, i am going to try through out the day, i can see dome of your concerns regarding arranging files etc, it doesn't matter if file names are not arranged, this folder where these files are for 1 customer and will be backed up and deleted.

    The file name is actually going to be renamed: CustomerName & "-" & Format(Now(),"dd-mm-yy") & "-1" & ".jpg"

    If there is 3 pictures to send then the following
    CustomerName & "-" & Format(Now(),"dd-mm-yy") & "-1" & ".jpg"
    CustomerName & "-" & Format(Now(),"dd-mm-yy") & "-2" & ".jpg"
    CustomerName & "-" & Format(Now(),"dd-mm-yy") & "-3" & ".jpg"

    No other files will be in the folder so if I am correct in thinking, is doesn't matter if these are in order of 3 then 1 then 2

    I am going to look at your code better and try and understand and test

    Again, thank you so so much, will update later

  9. #9
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    I have tried adapting post 7 form June7 which looked a good option, this is my code and doesn't rename the files ??

    The adapted code I have added in Bold from post 7, is there something I am not doing correctly ?

    Code:
    If Me.cboFolderActions = "Add Images" Then
    Dim mDest As String
    imgDealer = InputBox("Enter Abbreviated Dealer Name?", "ENTER ABBR DEALER")
    Set rs = CurrentDb.OpenRecordset("Select * FromtblDealers WHERE Name Like ""*" & imgDealer &"*"" ORDER BY Name;")
    Do Until rs.EOF
    mBody = mBody & Chr(149) & " " &rs.Fields("RecordNo") & Chr(149) & "  " &rs.Fields("Name") & "  " &rs.Fields("Town") & vbNewLine
    rs.MoveNext
    Loop
    RecNo = InputBox("Enter Number Of Which Dealer ToSearch ?" & Chr(10) & Chr(10) & _
    mBody, "WHICH DEALER")
    mDealer = DLookup("Name", "tblDealers","[RecordNo] = " & RecNo)
    MyInput = InputBox("Enter The Location For Your NewImages ?" & Chr(10) & Chr(10) & _
    ">> 1 << Deliveries" & Chr(10)& Chr(10) & _
    ">> 2 << Collections" & Chr(10)& Chr(10) & _
    ">> 3 << Returns", "ENTER IMAGELOCATION")
    Select Case MyInput
    Case 1
    Dest = "Deliveries"
    Case 2
    Dest = "Collections"
    Case 3
    Dest = "Returns"
    
    End Select
     
    imgPath = "T:\Images\" & Dest &"\" & mDealer
     
    If Dir(imgPath, vbDirectory) = "" Then
    MkDir (imgPath)
    Else
    DoCmd.CancelEvent
    End If
     
    
    
     
    If MsgBox("Add Your Device To The Computer" &Chr(10) & Chr(10) & _
    "An Image Destination Window Will Appear For:"& Chr(10) & Chr(10) & _
    Chr(149) & " " & mDealer & "" & Chr(149) & Chr(10) & Chr(10) & _
    "Copy The Files From Your Device Into ThisWindow", vbOKCancel + vbInformation, "IMAGE DESTINATION") =vbCancel Then
    DoCmd.CancelEvent
    Else
     
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.InitialFileName = imgPath
    With fd
    .InitialFileName = imgPath
    If .Show = -1 Then
    .Show
    DoCmd.RunCommand acCmdAppMinimize
    End If
    End With
     
    Set fso =CreateObject("Scripting.FileSystemObject")
    Set ObjFiles = fso.getfolder(imgPath).Files
    imgQty = ObjFiles.Count
    If MsgBox("File Quantity Confirmed = " &imgQty & vbNewLine & vbNewLine & _
    "Rename Files Now ?", vbQuestion + vbYesNo,"FILE COUNT") = vbNo Then
    DoCmd.CancelEvent
    Else
    strPath = imgPath
    strFile = Dir(strPath & "*.*")
    x = 1
    NewName = mDealer & "-" & Format(Now(),"dd-mm-yy") & "-" & x & ".jpg"
    Do Until strFile = vbNullString
    Name strPath & strFile As strPath & NewName
    strFile = Dir
    x = x + 1
    Loop
    End If

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You really need to use indentation to make code more readable.

    Folder path needs to terminate with a backslash (\).

    strPath = imgPath & "\"

    Step debug and make sure variables are getting correct values.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    No other files will be in the folder so if I am correct in thinking, is doesn't matter if these are in order of 3 then 1 then 2
    I dont believe June was concerned with the sort order. I think she meant that the code could become recursive and result in an endless loop. Say you have files A,B,C and you iterate through the folder and rename them. Say you renamed file A to file D, file B to E, file C to F. Would the code then rename files D,E,F and continue looping endlessly. If I had to guess I would think that the fso's file collection is created in the "Set = " statement, but thats just a guess.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Finally realized my concern was erroneous as indicated in post 7.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi June 7 yes thank you very much, further up the code i have imgPath = "T:\Images" & dest & "" & mdealer

    I should have
    "T:\Images" & dest & "" & mdealer & ""

    WIll try but again, thanks to you kind people

  14. #14
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Correction to my last post 13

    "T:\Images" & dest & "" & mdealer

    To "T:\Images" & Dest & "" & mDealer & ""

  15. #15
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hu June7 and Moke, this is so so close to perfect, it appears to debug when renaming, it's done one file then debugs the next, so so close though any idea ???

    Capture 1
    Click image for larger version. 

Name:	Capture.JPG 
Views:	17 
Size:	12.8 KB 
ID:	39766
    Capture 2

    Click image for larger version. 

Name:	Capture2.JPG 
Views:	16 
Size:	13.6 KB 
ID:	39767

    Capture 3

    Click image for larger version. 

Name:	Capture3.JPG 
Views:	16 
Size:	45.9 KB 
ID:	39768

    Capture 4

    Click image for larger version. 

Name:	Capture4.JPG 
Views:	17 
Size:	14.5 KB 
ID:	39769

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Renaming a Module
    By George in forum Modules
    Replies: 4
    Last Post: 09-04-2017, 06:43 AM
  2. Renaming values?
    By brownk in forum Queries
    Replies: 6
    Last Post: 06-25-2012, 12:56 PM
  3. Renaming Utility
    By bginhb in forum Access
    Replies: 3
    Last Post: 11-02-2011, 03:11 PM
  4. Renaming a file using VBA
    By bfaucher in forum Programming
    Replies: 1
    Last Post: 11-01-2011, 02:56 PM
  5. Renaming fields
    By WilsonsW in forum Access
    Replies: 10
    Last Post: 03-25-2011, 01:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums