-
May 9th, 2018, 12:57 PM
#1
Thread Starter
Member
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.
-
May 9th, 2018, 01:37 PM
#2
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
May 9th, 2018, 08:00 PM
#3
Thread Starter
Member
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.
-
May 10th, 2018, 04:28 AM
#4
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.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
May 10th, 2018, 01:57 PM
#5
Thread Starter
Member
Re: Remove first character from folder and file names
Hi Zvoni
Please find the sample file.
Sample.zip
-
May 11th, 2018, 04:58 AM
#6
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
-
May 11th, 2018, 05:16 AM
#7
Thread Starter
Member
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.
-
May 11th, 2018, 05:51 AM
#8
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
-
May 11th, 2018, 09:35 AM
#9
Thread Starter
Member
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.
-
May 11th, 2018, 04:25 PM
#10
Re: Remove first character from folder and file names
Originally Posted by westconn1
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
May 11th, 2018, 04:53 PM
#11
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
-
May 11th, 2018, 07:03 PM
#12
Thread Starter
Member
Re: Remove first character from folder and file names
I don't have code Westconn...
-
May 12th, 2018, 01:51 AM
#13
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
-
May 12th, 2018, 05:33 PM
#14
Thread Starter
Member
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
-
May 12th, 2018, 06:09 PM
#15
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
-
May 12th, 2018, 06:45 PM
#16
Thread Starter
Member
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
-
May 14th, 2018, 11:29 AM
#17
Junior Member
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.
-
May 14th, 2018, 04:12 PM
#18
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
-
May 14th, 2018, 05:16 PM
#19
Junior Member
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
-
May 15th, 2018, 06:05 AM
#20
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
-
May 15th, 2018, 07:37 PM
#21
Junior Member
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
-
May 16th, 2018, 05:22 AM
#22
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
-
May 16th, 2018, 11:18 AM
#23
Junior Member
Re: Remove first character from folder and file names
Hi Westconn,
it showing error as (compile errr: Next without for)
please guide me
-
May 16th, 2018, 11:28 AM
#24
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.
-
May 16th, 2018, 01:16 PM
#25
Junior Member
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 .
-
May 16th, 2018, 04:02 PM
#26
Junior Member
Re: Remove first character from folder and file names
hi Westconn,
could you please help me on that above mentioned threat
-
May 16th, 2018, 04:23 PM
#27
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
-
May 16th, 2018, 05:22 PM
#28
Junior Member
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
-
May 17th, 2018, 04:19 AM
#29
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
-
May 17th, 2018, 07:04 AM
#30
Junior Member
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 it
Last edited by amb2301; May 17th, 2018 at 07:09 AM.
Reason: attached screenshot
-
May 17th, 2018, 04:36 PM
#31
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|