CODEWITHSUNDEEP
filevbatextescapingascii
Mike Williamson
Mike Williamson

Reputation: 3218

Inserting special ascii characters into a file using VBA (excel)



I am fairly new to VBA, but not to coding in general. I have an Excel worksheet, part of which I need to export into a text file. However, to ensure the escape characters don't collide with anything written in the worksheet, and also to match up with how escape characters are handled in other (Java & C) apps at our company, I would like to use the ascii character "thorn".

Some languages that I know, like perl, will simply let you enter an escape character and the ascii number of the symbol. E.g., I could type "\231" in order to get "thorn". It seems this is not supported in VBA. Or if it is, I am not sure how.

Lastly, below is a brief snippet of how I imagine I would implement the code, with just the key statements. If you have a better suggestion, or know why this wouldn't be the best way, please let me know.

Open sFullPath For Output As #1

(... some code to capture the values to be written to the next line of the file ...)

sLine = Join(sValues, <the thorn character>)
Print #1, sLine


The code above would be placed within a loop that would regenerate "sValues", the set of relevant cells in the next row to be printed to the text file.

Thanks so much!
Regards,
Mike

Upvotes: 2

Views: 7039

Answers (2)

mkingston
mkingston

Reputation: 2718

Try replacing <the thorn character> with Chr(254). Let me know how that goes.

Upvotes: 1

Nick Perkins
Nick Perkins

Reputation: 1327

To print an ASCII character using its code number, use the chr() function

Extended ASCII isn't standard across all platforms. For whatever reason, the number needed is 222.

So you would need

sLine = Join(sValues, chr(222))
Print #1, sLine

Upvotes: 3

Related Questions