File Name %,#,$ these signs then remove file from that folder

[Abhijeet]

Hi

I have macro but %,#,$ if any signs are in file names then macro not work so i want where ever is these signs in files name then cut paste those files in different folder or remove those signs from that file name in same folder give status in excel which files these signs
please tell me how to do this

 

[Marc L]

Hi,

maybe no way in regular VBA 'cause of forbidden characters in file name !

Better is to go back to source, so the question is :
how these files were created ?‼
Not difficult to create correct file name …

I did not ask to kids, they would say for sure « too easy » again !
​

 

[Abhijeet]

Hi,

maybe no way in regular VBA 'cause of forbidden characters in file name !

Better is to go back to source, so the question is :
how these files were created ?‼
Not difficult to create correct file name …

I did not ask to kids, they would say for sure « too easy » again !
​

I know while create files that time remove this but that is not in my hand to do that's why i ask question here

 

[Marc L]

'Cause regular VBA accept only valid file name, what did you expect ?‼

It's like a guy in a doctor office asking
« My car smokes ! Could you help me ? »

So the MD may have several ways to answer :

« I'm a doctor, not an auto mechanic ! »

« Oh ! Tell it to stop if it do not want to have cancer ! »

« I'm The Doctor ! I only travel with my Tardis ! » …

I already had same case, no choice to manually rename each file …
Better is to ask to source file creator.

Don't forget here it's only an Excel forum,
see other websites for system forum …
​

 

[Abhijeet]

'Cause regular VBA accept only valid file name, what did you expect ?‼

It's like a guy in a doctor office asking
« My car smokes ! Could you help me ? »

So the MD may have several ways to answer :

« I'm a doctor, not an auto mechanic ! »

« Oh ! Tell it to stop if it do not want to have cancer ! »

« I'm The Doctor ! I only travel with my Tardis ! » …

I already had same case, no choice to manually rename each file …
Better is to ask to source file creator.

Don't forget here it's only an Excel forum,
see other websites for system forum …
​

I know this can you please tell me excel formula to identify this in file names then we can rename those files i think this is Valid solution please tell me formula to identify this signs in file name

 

[Marc L]

If you knew this you did'nt open this thread !

Excel formula ? Here is VBA forum !
Excel formula : means you already have a file list in a worksheet ?
​

 

[Nebu]

Hi:

You can try this code not tested.This will replace the special characters with a blank in the file name.

Dim objFSO As Object
Dim objFolder As Object
Dim objFile  As Object

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("Your File Path Here")

For Each objFile In objFolder.Files
  If InStr(1, objFile.Name, "#") > 0 Or InStr(1, objFile.Name, "%") > 0 Or InStr(1, objFile.Name, "$") > 0 Then
  objFile.Name = Replace(objFile.Name, "#", "")
  objFile.Name = Replace(objFile.Name, "%", "")
  objFile.Name = Replace(objFile.Name, "$", "")
  End If
Next

Thanks

 

[Marc L]

May work with some Windows ActiveX like FileSystemObject.
See help on MSDN. But when I had the same issue, it failed …

​

 

[Abhijeet]

Hi:

You can try this code not tested.This will replace the special characters with a blank in the file name.

Dim objFSO As Object
Dim objFolder As Object
Dim objFile  As Object

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("Your File Path Here")

For Each objFile In objFolder.Files
  If InStr(1, objFile.Name, "#") > 0 Or InStr(1, objFile.Name, "%") > 0 Or InStr(1, objFile.Name, "$") > 0 Then
  objFile.Name = Replace(objFile.Name, "#", "")
  objFile.Name = Replace(objFile.Name, "%", "")
  objFile.Name = Replace(objFile.Name, "$", "")
  End If
Next

Thanks

Thanks for replay but this is not work Run type Error 58 File already exits show

 

[Nebu]

Hi:

I have given you a code snippet, I am not sure where you are incorporating this. "This error occurs at run time when the new file name, for example, one specified in a Name statement, is identical to a file name that already exists."
Source:https://msdn.microsoft.com/en-us/library/office/gg251386.aspx

Try putting error handler like on Error resume next.

Thanks

 

[NARAYANK991]

Hi Abhijeet ,

Instead of replacing the forbidden characters with a character where an identical file name may exist , you could try replacing them with the underscore character _ ; replacing each single character with multiple underscores may ensure that an identically named file does not exist.

Narayan