• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

File renaming script - unrecognised character issue

dohsan

New Member
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?
 
Dohsan


Why not replace any character with an Ascii value Greater than 122 or "z" with a known character.


You could do this for all characters > 122 with a simple

Case 123 To 20000: charTest = "Au"


or


After your code

[pre]
Code:
Case 241: charTest = "n"
Case 249 To 252: charTest = "u"
Case 253, 255: charTest = "y"[/pre]
add a line

Case 255 To 9999: charTest = "A"


or Change A to suit maybe a - or = character
 
Thanks Hui, I both your methods and it still gets skipped. I checked the characters as they were passed through with debug.print and it seems Excel recognises it as a standard Capital I...


T E K S T I L - D O C 0 0 0 6 6 3 9

84 69 75 83 84 73 76 45 68 79 67 48 48 48 54 54 51 57


I tried Case 73: charTest = "i"


this went seemed to work, but then it threw an error as it couldn't find the original file to rename - doh!


I foudn what the characters should be (and your solution should work)


ğ - ChrW(287)


ş - ChrW(351)


ı - ChrW(305)


Ğ - ChrW(286)


Ş - ChrW(350)


İ - ChrW(304)


seems to be some limitations with the vba editor as I can run this


Sub test()


Dim x As Integer


x = 304

Debug.Print ChrW(x)


End Sub


and in the immediate window "I" is displayed


I think we'll just have to manually sweep the files to get around this
 
Hi, dohsan!

The VBA editor has problems to display in immediate window those and other characters. I faced that issue while debugging a formula translation project (see below link), so outputting to a work worksheet will do the job.

Regards!

http://chandoo.org/forums/topic/excel-multilanguage-formula-translator-and-function-reference
 
Back
Top