SUGGESTION: Follow the "DRY" rule of programming ... i.e. Don't Repeat Yourself
one reason for DRY is eliminate typos ... like NIE X instead of NIF X
REPLACE THIS ...
If fil.Name Like "*.xls" Then
fileExt = "." & Split(fil.Path, ".")(1)
newFileName = GetInfoFromClosedBook(PATH_TO_FOLDER, fil.Name, SOURCE_SHEET_NAME, 5, 1)
newFileName = Replace(newFileName, ":", "")
newFileName = Replace(newFileName, "1", "")
newFileName = Replace(newFileName, "2", "")
newFileName = Replace(newFileName, "3", "")
newFileName = Replace(newFileName, "4", "")
newFileName = Replace(newFileName, "5", "")
newFileName = Replace(newFileName, "6", "")
newFileName = Replace(newFileName, "7", "")
newFileName = Replace(newFileName, "8", "")
newFileName = Replace(newFileName, "9", "")
newFileName = Replace(newFileName, "0", "")
newFileName = Replace(newFileName, "-", "")
newFileName = Replace(newFileName, "NIF A", "")
newFileName = Replace(newFileName, "NIF A", "")
newFileName = Replace(newFileName, "NIF B", "")
newFileName = Replace(newFileName, "NIF C", "")
newFileName = Replace(newFileName, "NIF D", "")
newFileName = Replace(newFileName, "NIF F", "")
newFileName = Replace(newFileName, "NIF G", "")
newFileName = Replace(newFileName, "NIF J", "")
newFileName = Replace(newFileName, "NIF N", "")
newFileName = Replace(newFileName, "NIF V", "")
newFileName = Replace(newFileName, "NIF Q", "")
newFileName = Replace(newFileName, "NIE X", "")
newFileName = Replace(newFileName, "Empresa", " - LISTADO COSTES -")
newFileName = Format(Now(), "MMMM YYYY") & newFileName
newFileName0 = GetInfoFromClosedBook(PATH_TO_FOLDER, fil.Name, SOURCE_SHEET_NAME, 4, 1)
newFileName0 = newFileName0 & "_" & newFileName & fileExt
oFSO.moveFile fil.Path, PATH_TO_FOLDER & newFileName0
End If
WITH THIS ...
If fil.Name Like "*.xls" Then
fileExt = "." & Split(fil.Path, ".")(1)
newFileName = GetInfoFromClosedBook(PATH_TO_FOLDER, fil.Name, SOURCE_SHEET_NAME, 5, 1)
newFileName = replaceWithBlank(newFileName) ' one line instead of repeating yourself many times
newFileName = Replace(newFileName, "Empresa", " - LISTADO COSTES -")
newFileName = Format(Now(), "MMMM YYYY") & newFileName
newFileName0 = GetInfoFromClosedBook(PATH_TO_FOLDER, fil.Name, SOURCE_SHEET_NAME, 4, 1)
newFileName0 = newFileName0 & "_" & newFileName & fileExt
oFSO.moveFile fil.Path, PATH_TO_FOLDER & newFileName0
End If
AND THIS ...
Private Function replaceWithBlank(rawString As String) As String
Dim origStrings, origStr
Dim newString As String
'Config here
origStrings = Array(":", "1", "2", "3", "4", "5", "6", "7", "8", "9", "0", _
"-", "NIF A", "NIF A", "NIF B", "NIF C", "NIF D", "NIF F", "NIF G", _
"NIF J", "NIF N", "NIF V", "NIF Q", "NIF X")
newString = rawString
For Each origStr In origStrings
newString = Replace(newString, origStr, "")
Next origStr
replaceWithBlank = newString
End Function
Bookmarks