Hi,
We have a process that picks up excel workbooks and then processes them into a database. The initial part of this process is to clean the filename to ensure that no illegal characters or symbols are present.
The below function removes all symbols etc and also converts letters with accents/umlauts etc. to standard English characters:
Sub getFNstatus(fn As String)
Dim StrOutput As String
Dim charTest As String
Dim i As Integer
Dim extension
Dim fn1 As String
'find extension and store - work for all version .xls and .xls*
extension = Mid(fn, InStrRev(fn, "."))
'remove extension from filename
fn1 = Left(fn, Len(fn) - Len(extension))
For i = 1 To Len(fn1)
charTest = Mid$(fn1, i, 1)
Debug.Print charTest
Debug.Print AscW(charTest)
'Changes accents to Standard letters
Select Case AscW(charTest)
Case 192 To 197: charTest = "A"
Case 198: charTest = "AE"
Case 199: charTest = "C"
Case 200 To 203: charTest = "E"
Case 204 To 207: charTest = "I"
Case 208: charTest = "D"
Case 209: charTest = "N"
Case 210 To 214, 216: charTest = "O"
Case 215: charTest = "x"
Case 217 To 220: charTest = "U"
Case 221: charTest = "Y"
Case 222, 254: charTest = "p"
Case 223: charTest = "B"
Case 224 To 229: charTest = "a"
Case 230: charTest = "ae"
Case 231: charTest = "c"
Case 232 To 235: charTest = "e"
Case 236 To 239: charTest = "i"
Case 240, 242 To 246, 248: charTest = "o"
Case 241: charTest = "n"
Case 249 To 252: charTest = "u"
Case 253, 255: charTest = "y"
End Select
'remove char if not alphanumeric
If charTest Like "[A-Za-z0-9- _]" Then
StrOutput = StrOutput & charTest
End If
Next
'Check if original filename = new filename (i.e. no change) and set to blank if true else rename
Sheets("NewFileName").Range("A2").Value = IIf(fn <> (StrOutput + extension), _
StrOutput + extension, "")
End Sub
This seems to do the job fine, but I've come across some characters that seem to be skipped.
TEKSTİL-DOC0006639.xlsx
It's a capital I with a dot above it and VBA sees it as a capital I (It's a Turkish character I believe)
Is there anyway to change/remove this character?
We have a process that picks up excel workbooks and then processes them into a database. The initial part of this process is to clean the filename to ensure that no illegal characters or symbols are present.
The below function removes all symbols etc and also converts letters with accents/umlauts etc. to standard English characters:
Sub getFNstatus(fn As String)
Dim StrOutput As String
Dim charTest As String
Dim i As Integer
Dim extension
Dim fn1 As String
'find extension and store - work for all version .xls and .xls*
extension = Mid(fn, InStrRev(fn, "."))
'remove extension from filename
fn1 = Left(fn, Len(fn) - Len(extension))
For i = 1 To Len(fn1)
charTest = Mid$(fn1, i, 1)
Debug.Print charTest
Debug.Print AscW(charTest)
'Changes accents to Standard letters
Select Case AscW(charTest)
Case 192 To 197: charTest = "A"
Case 198: charTest = "AE"
Case 199: charTest = "C"
Case 200 To 203: charTest = "E"
Case 204 To 207: charTest = "I"
Case 208: charTest = "D"
Case 209: charTest = "N"
Case 210 To 214, 216: charTest = "O"
Case 215: charTest = "x"
Case 217 To 220: charTest = "U"
Case 221: charTest = "Y"
Case 222, 254: charTest = "p"
Case 223: charTest = "B"
Case 224 To 229: charTest = "a"
Case 230: charTest = "ae"
Case 231: charTest = "c"
Case 232 To 235: charTest = "e"
Case 236 To 239: charTest = "i"
Case 240, 242 To 246, 248: charTest = "o"
Case 241: charTest = "n"
Case 249 To 252: charTest = "u"
Case 253, 255: charTest = "y"
End Select
'remove char if not alphanumeric
If charTest Like "[A-Za-z0-9- _]" Then
StrOutput = StrOutput & charTest
End If
Next
'Check if original filename = new filename (i.e. no change) and set to blank if true else rename
Sheets("NewFileName").Range("A2").Value = IIf(fn <> (StrOutput + extension), _
StrOutput + extension, "")
End Sub
This seems to do the job fine, but I've come across some characters that seem to be skipped.
TEKSTİL-DOC0006639.xlsx
It's a capital I with a dot above it and VBA sees it as a capital I (It's a Turkish character I believe)
Is there anyway to change/remove this character?