dcsimg
Results 1 to 31 of 31

Thread: Remove first character from folder and file names

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    Lightbulb Remove first character from folder and file names

    Hi Everyone,

    I want to remove the first letter if it is a Special character from folder and file name.
    For Ex. File path like "@documents/#_file.pdf"
    Then it should be like "documents/file.pdf".

    Could please help me to find the solution.


    Thanks in advance.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,537

    Re: Remove first character from folder and file names

    Here is a Link to my ReplaceAny-Function
    http://www.vbforums.com/showthread.p...imAny-Function

    In your case the call would be something like
    Code:
    OldFileName="@documents/#_file.pdf"
    IllegalChars="@#"  'Add more illegal Characters. Order doesn't matter
    NewFileName=ReplaceAny(OldFileName, IllegalChars, "")
    'Do the renaming of file
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    Re: Remove first character from folder and file names

    Thanks for your reply Zvoni,

    Actually i have a lot of file path in sheet1 A:A column on my workbook.
    Few files and folders contains a special character as first letter.
    I want to remove these special character from all files and folders.
    Is that possible?


    Thanks again.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,537

    Re: Remove first character from folder and file names

    Yes, as long as those special characters are not repeated within the folder-/filename
    but you have to be careful: if you have more than one file in a folder, where you have to rename both, it won't work, since the path (folders!) is part of a filename.
    search this subforum. There's been something similiar a few weeks ago

    EDIT: if you only want to remove leading illegal characters then you can use the TrimAnyL-Function found under the link i gave you

    Any way, you would have to split up your path completely into its single contents, then run the function against every element, build the path back together again....


    can you upload a sample? I could have a look at it coming monday (holidays in Germany now)
    Last edited by Zvoni; May 10th, 2018 at 04:33 AM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    Re: Remove first character from folder and file names

    Hi Zvoni
    Please find the sample file.
    Sample.zip

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: Remove first character from folder and file names

    http://www.vbforums.com/showthread.p...ht=rename+file

    the above link is to a recent thread to similarly rename files and folders, with solution attempts from zvoni and myself, see if that helps at all
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    Re: Remove first character from folder and file names

    Hey Westconn
    I tried that code..but that's replace all the special character from file path..but i need remove the special character if it is first letter on folder name.
    Even the folder name has more special character i need to remove first letter only.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: Remove first character from folder and file names

    Even the folder name has more special character i need to remove first letter only
    it would be pretty easy to modify the code to only replace the first character, check if first character is one listed to remove then modify the string
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    Re: Remove first character from folder and file names

    Hi Westconn
    I'm not a Expert on VBA Scripts, Could you please tell me how to modify the Code?

    Thanks you so much.

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,537

    Re: Remove first character from folder and file names

    Quote Originally Posted by westconn1 View Post
    it would be pretty easy to modify the code to only replace the first character, check if first character is one listed to remove then modify the string
    Applying that to my version it would just be replacing strCSpn with StrSpn-API and chaning the If-Clause
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: Remove first character from folder and file names

    you should post the code as you are currently trying, there was several versions of code, by different authors, hard to know which you want to modify and specify which characters you want to remove
    will the folder tree only be one deep as per the example in post #1 or multi level folders that may all contain characters to be removed?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    Re: Remove first character from folder and file names

    I don't have code Westconn...

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: Remove first character from folder and file names

    in that case you should make some attempt to test some of the other codes linked to and tell us why it is not producing the required results

    also you did not answer the other questions, so it is hard to help you
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    Re: Remove first character from folder and file names

    Hey Westconn
    This is the code i have found from and tested
    In this i want to do some changes..i have a lot of file path on A:A column but this code is working with only one path...
    And also i need to remove first character only if it is special character.

    Code:
    .  Sub RenameDocFiles()
    
        Dim MyFolder As String
        Dim MyFile As String
        Dim Temp1 As String
        Dim Temp2 As String
        Dim NewFileName As String
        
        'Change the path accordingly
        MyFolder = "C:\Users\Domenic\Desktop\temp\"
        
        'Change the file filter (.docx) accordingly
        MyFile = Dir(MyFolder & "*.docx")
        
        Do While Len(MyFile) > 0
            Temp1 = Left(MyFile, InStr(1, MyFile, ".doc") - 1)
            If Len(Temp1) > 10 Then
                NewFileName = Mid(MyFile, 11)
                Name MyFolder & MyFile As MyFolder & NewFileName
            Else
                Temp2 = Temp2 & vbLf & MyFile
            End If
            MyFile = Dir
        Loop
        
        If Temp2 <> "" Then
            MsgBox "File names less than 10 characters:  " & vbLf & Temp2
        End If
        
    End Sub

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: Remove first character from folder and file names

    the code as posted does not do what you want at all, probably does not even do what it was supposed to do when it was written

    i took the code from
    http://www.vbforums.com/showthread.p...=1#post5271267 and modified slightly, to suit your original specification

    Code:
    Sub renamePath()
    Dim chars
    
    chars = "@#%_"    ' add or remove characters to suit
            For Each cell In Range("a:a")
               If IsEmpty(cell) Then Exit For   '   finish on first empty cell, or change range to suit
                    farray = Split(cell, "\")
                    newpath = farray(0)
                    For f = 1 To UBound(farray)
                        oldpath = newpath
                        oldpath = oldpath & "\" & farray(f)
                        tmp = farray(f)
                        If InStr(chars, Left(farray(f), 1)) > 0 Then tmp = Mid(farray(f), 2)
                        newpath = newpath & "\" & tmp
                        If Len(Dir(oldpath, vbDirectory)) > 0 And Not newpath = oldpath Then Name oldpath As newpath
                    Next
                    If Not Len(Dir(newpath, vbDirectory)) > 0 Then MsgBox "path in " & cell.Address & " not changed"
            Next cell
    End Sub
    this has not been tested, so test carefully
    this does not in anyway check if the second or more characters are also special characters, so it would be possible that the first character of the new name is still a special character
    i have assumed that all the paths in the worksheet are full paths, drive:\folderpath\filename.ext, as per your sample workbook

    from your examples:-
    i do not believe that * is a valid character for a file name and may cause errors, even if it could exist at any position in a filename
    other illegal characters are /\:"*?<>|
    you should make sure none of those are in your worksheet paths
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    Re: Remove first character from folder and file names

    Hi Westconn
    Thank you so much.
    You are the genius.I have tested this code... working perfectly...

    Thanks a lot again

  17. #17
    Junior Member
    Join Date
    Mar 2018
    Posts
    23

    Re: Remove first character from folder and file names

    Hi Westconn,
    Thank you so much, it helped me too a lot.
    one thing to be mentioned its working fine in Local drives, but its not working in servers,
    could you please help out on that issue.
    Also its working only if it is full path like drive:\folderpath\filename.ext
    not working/error message shows while path is given like drive:\folderpath

    Awaiting for your reply.
    Last edited by amb2301; May 14th, 2018 at 12:47 PM.

  18. #18
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: Remove first character from folder and file names

    one of the other recent threads on removing special characters from file paths, was to work on network drives, so i modified the code to use FSO, as DIR does not work with network folders, though it does work with network files, have a look to see if you can make that work for you application

    Edit: @amb2301 i just realized it was your thread i was referring to, so i had already posted a working solution based on your criteria, if that is not working as required, probably better to post in that thread
    Last edited by westconn1; May 14th, 2018 at 04:17 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  19. #19
    Junior Member
    Join Date
    Mar 2018
    Posts
    23

    Re: Remove first character from folder and file names

    Hi westconn,
    the code which u have is working well, i am using it, I agree your point,
    i am new to VBA, so i am not able to convert it for removing only first special character in network drive, i mean using FSO,
    so could you please help me, i just need this code to work on network drive
    Code:
    Sub renamePath()
    Dim chars
    
    chars = "@#%_"    ' add or remove characters to suit
            For Each cell In Range("a:a")
               If IsEmpty(cell) Then Exit For   '   finish on first empty cell, or change range to suit
                    farray = Split(cell, "\")
                    newpath = farray(0)
                    For f = 1 To UBound(farray)
                        oldpath = newpath
                        oldpath = oldpath & "\" & farray(f)
                        tmp = farray(f)
                        If InStr(chars, Left(farray(f), 1)) > 0 Then tmp = Mid(farray(f), 2)
                        newpath = newpath & "\" & tmp
                        If Len(Dir(oldpath, vbDirectory)) > 0 And Not newpath = oldpath Then Name oldpath As newpath
                    Next
                    If Not Len(Dir(newpath, vbDirectory)) > 0 Then MsgBox "path in " & cell.Address & " not changed"
            Next cell
    End Sub

  20. #20
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: Remove first character from folder and file names

    Code:
                        tmp = farray(f)
                          If InStr(chars, Left(farray(f), 1)) > 0 Then tmp = Mid(farray(f), 2)
    
                        newpath = newpath & "\" & tmp
                        If f > 2 Then
                            On Error Resume Next
                            Set pth = fso.GetFolder(oldpath)
                            If Not Err.Number = 0 Then
                                Err.Clear
                                Set pth = fso.GetFile(oldpath)
                            End If
                            On Error GoTo 0
                            If Not newpath = oldpath Then pth.Name = tmp
                        End If
                    Next
            Next cell
    try fitting this from the other thread into the above code the first line should indicate where it has to go, down to next cell inclusive

    you will also need
    Code:
    Dim fso As FileSystemObject, pth As Object
    Set fso = New FileSystemObject
    at the top of the procedure and a reference to MS scripting runtime

    the above should work with both local drives and network drives, with some exceptions, like special character in a first level subfolder of c:
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  21. #21
    Junior Member
    Join Date
    Mar 2018
    Posts
    23

    Re: Remove first character from folder and file names

    hi Westconn,
    i am sorry, i tried but i cant, please help, i tried like below
    Code:
    Sub Firstchar_rename_study()
    
        Dim fso As FileSystemObject, pth As Object
            Dim newName As String
    
        Set fso = New FileSystemObject
        Set mySource = fso.GetFolder(Worksheets("sheet1").Range("f3").Value)
    
       tmp = farray(f)
                          If InStr(chars, Left(farray(f), 1)) > 0 Then tmp = Mid(farray(f), 2)
    
                        newpath = newpath & "\" & tmp
                        If f > 2 Then
                            On Error Resume Next
                            Set pth = fso.GetFolder(oldpath)
                            If Not err.Number = 0 Then
                                err.Clear
                                Set pth = fso.GetFile(oldpath)
                            End If
                            On Error GoTo 0
                            If Not newpath = oldpath Then pth.Name = tmp
                        End If
                    Next
            Next cell
        
    End Sub

  22. #22
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: Remove first character from folder and file names

    more like
    Code:
    Dim chars
    Dim fso As FileSystemObject, pth As Object
    Set fso = New FileSystemObject
    
    chars = "@#%_"    ' add or remove characters to suit
            For Each cell In Range("a:a")
               If IsEmpty(cell) Then Exit For   '   finish on first empty cell, or change range to suit
                       farray = Split(cell, "\")
                        tmp = farray(f)
                          If InStr(chars, Left(farray(f), 1)) > 0 Then tmp = Mid(farray(f), 2)
    
                        newpath = newpath & "\" & tmp
                        If f > 2 Then
                            On Error Resume Next
                            Set pth = fso.GetFolder(oldpath)
                            If Not Err.Number = 0 Then
                                Err.Clear
                                Set pth = fso.GetFile(oldpath)
                            End If
                            On Error GoTo 0
                            If Not newpath = oldpath Then pth.Name = tmp
                        End If
                    Next
            Next cell
    End Sub
    untested, some error handling maybe required
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  23. #23
    Junior Member
    Join Date
    Mar 2018
    Posts
    23

    Re: Remove first character from folder and file names

    Hi Westconn,
    it showing error as (compile errr: Next without for)
    please guide me

  24. #24
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,545

    Re: Remove first character from folder and file names

    Looks like there's an extra "next" right before "Next cell." Remove that, should be good.

  25. #25
    Junior Member
    Join Date
    Mar 2018
    Posts
    23

    Re: Remove first character from folder and file names

    hi vbfbryce,
    Thanks for your reply,i removed extra next as you mentioned & tried, now error not appears but the removal of first special characters in each folders & files is not happening both in server as well as in local drive.

    example:
    this folders in path E:\Testing folder\@sdf Testing folder\&%XCvSERGAnd _AndDV\##clip1.jpg
    to be corrected as E:\Testing folder\sdf Testing folder\%XCvSERGAnd _AndDV\#clip1.jpg

    please help .

  26. #26
    Junior Member
    Join Date
    Mar 2018
    Posts
    23

    Re: Remove first character from folder and file names

    hi Westconn,
    could you please help me on that above mentioned threat

  27. #27
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: Remove first character from folder and file names

    Code:
    Dim fso As FileSystemObject, pth As Object
    Set fso = New FileSystemObject
    chars = "@#%"
            For Each cell In Range("a1:a10000")
               If IsEmpty(cell) Then Exit For   '   finish on first empty cell, or change range to suit
                    farray = Split("\\Pete-497fff58bc\xxx\#XLSX\@dlg.zip", "\")
                    newpath = farray(0)
                    For f = 1 To UBound(farray)
                        oldpath = newpath
                        oldpath = oldpath & "\" & farray(f)
                        tmp = farray(f)
                        If InStr(chars, Left(tmp, 1)) Then tmp = Mid(tmp, 2)
                        newpath = newpath & "\" & tmp
                        If f > 2 Then
                            On Error Resume Next
                            Set pth = fso.GetFolder(oldpath)
                            If Not Err.Number = 0 Then
                                Err.Clear
                                Set pth = fso.GetFile(oldpath)
                            End If
                            On Error GoTo 0
                            If Not newpath = oldpath Then pth.Name = tmp
                        End If
                    Next
            Next cell
    this is tested and works with local drive, and network path

    test with care

    if your list of files contains more than one file in a folder, like
    E:\Testing folder\@sdf Testing folder\&%XCvSERGAnd _AndDV\##clip2.jpg
    E:\Testing folder\@sdf Testing folder\&%XCvSERGAnd _AndDV\##clip41.jpg
    E:\Testing folder\@sdf Testing folder\&%XCvSERGAnd _AndDV\##clip177.jpg
    as the folder renamed in the processing of the first file, an error is likely to occur when processing other files in a folder that no longer exists
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  28. #28
    Junior Member
    Join Date
    Mar 2018
    Posts
    23

    Re: Remove first character from folder and file names

    hi westconn,
    now error message appearing as "object variable or with block variable not set"
    please help

  29. #29
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: Remove first character from folder and file names

    "object variable or with block variable not set"
    at which line?

    post a sample of your workbook, does the error occur at the first cell or later?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  30. #30
    Junior Member
    Join Date
    Mar 2018
    Posts
    23

    Re: Remove first character from folder and file names

    hi westconn,
    i am getting error at the line as show in sceenshot,
    i am using the following path in the cell a1
    F:\testing\$dfdf\_ _R_ DFSVSDFDF.docx

    please check itName:  error1.jpg
Views: 43
Size:  25.9 KB
    Last edited by amb2301; May 17th, 2018 at 07:09 AM. Reason: attached screenshot

  31. #31
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: Remove first character from folder and file names

    as i mentioned above, post #22, some error handling would be required

    i created a file in path "c:\temp\test\$dfdf\_ _R_ DFSVSDFDF.docx"which worked fine for me

    i have added some simple check to make sure each file path does exist, before making any attempt to rename any part of the file path
    Code:
    Dim fso As FileSystemObject, pth As Object
    Set fso = New FileSystemObject
    afind = Array("@", "#", "%", "_")
    areplace = Array("AT", "NUMBER", "PERCENT", "UNDERSCORE")
    chars = "@$#%_"
            For Each cell In Range("a1:a10000")
               If IsEmpty(cell) Then Exit For   '   finish on first empty cell, or change range to suit
            On Error Resume Next
               Set pth = fso.GetFile(cell)
               On Error GoTo 0
               If pth Is Nothing Then
                   MsgBox "file path in row " & cell.Row & " does not exist"
                   Err.Clear
                   Else
                        farray = Split(cell, "\")
                        newpath = farray(0)
                        For f = 1 To UBound(farray)
                            oldpath = newpath
                            oldpath = oldpath & "\" & farray(f)
                            tmp = farray(f)
                            If InStr(chars, Left(tmp, 1)) Then tmp = Mid(tmp, 2)
                            newpath = newpath & "\" & tmp
                            If f > 2 Then
                                On Error Resume Next
                                Set pth = fso.GetFolder(oldpath)
                                If Not Err.Number = 0 Then
                                    Err.Clear
                                    Set pth = fso.GetFile(oldpath)
                                End If
                                On Error GoTo 0
                                If Not newpath = oldpath Then pth.Name = tmp
                            End If
                        Next
                    End If
            Next cell
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width