CODEWITHSUNDEEP
vbafor-loopfile-renameinvalid-characters
user3114375
user3114375

Reputation: 97

VBA Loop through all files in folder and remove invalid characters

I found function that replace invalids characters in file name. I would like to use this for all files in one folder.

Function strLegalFileName(strFileNameIn As String) As String
Dim i As Integer

Const strIllegals = "\/|?*<>"":"
strLegalFileName = strFileNameIn
For i = 1 To Len(strIllegals)
    strLegalFileName = Replace(strLegalFileName, Mid$(strIllegals, i, 1), "_")
Next i
End Function

I don't know how to loop through all files and replace characters when include invalids.

Is it call function properly? by Debug.print command There is my loop through all files, but I am not sure how to build it:

Sub LoopThroughFiles()
  Dim MyObj As Object, MySource As Object, FileName As Variant
  FileName = Dir("C:\Users\Anna\Desktop\testNazwa\")
   While (file <> "")
   Debug.Print strLegalFileName(strFileNameIn)
  file = Dir
  Wend
End Sub

Upvotes: 1

Views: 3443

Answers (1)

brettdj
brettdj

Reputation: 55682

You have some issues with

  • Your Dir loop
  • Not actually renaming filenames that you have examined

The code below does this. Pls change StrDir to suit

But I note as these charcaters are ilegal the filenames should be invalid to begin with (hence my test looks at different characters)

loop and rename code

Sub LoopThroughFiles()
Dim FName As Variant
Dim strNew As String
Dim strDir As String

strDir = "C:\temp\"
FName = Dir(strDir & "*.*")
Do While Len(FName) > 0
strNew = strLegalFileName(FName)
    If StrComp(strNew, FName) <> 0 Then Name (strDir & FName) As (strDir & strNew)
FName = Dir
Loop
End Sub

string replace function 

Function strLegalFileName(ByVal FName) As String
Dim i As Integer

Const strIllegals = "P:"
strLegalFileName = FName
For i = 1 To Len(strIllegals)
    strLegalFileName = Replace(strLegalFileName, Mid$(strIllegals, i, 1), "_")
Next i
End Function

Regexp Function alternative

Function strLegalFileName(ByVal FName) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")

With objRegex
    .Pattern = "[\/\\\?\*\]\[\|:]"""
    .Global = True
    strLegalFileName = .Replace(FName, vbNullString)
End With

End Function

Upvotes: 2

Related Questions