How to determine file encoding type with Excel VBA

Question

I have built an Excel/VBA tool to validate csv files to ensure the data they contain is valid. They csv can come originate from anywhere (from a full blown unix system or a desktop user saving data out from Excel). The Excel tool is sent out to businesses so they can validate their csv files in their own environment and without taking the risk of their data leaving thier systems. Thus, the solution needs to be in native VBA and not link into external libraries.

So using VBA, I need to be able to automatically detect UTF-8 (with or without BOM) or ANSI file encodings and warn the user if these are not the file encodings used for the csv.

I think this would perhaps involve reading in a few bytes from the start of the file and determining the encoding based on the existance of the byte order mark.

Could you help me get me started on the right track?

bonCodigo · Accepted Answer · 2012-12-17 12:49:14Z

2

Assuming you have the freedom to ask user to choose the correct file type, making them responsible for what they choose as a file ;)

That means, you can create a form where users can choose the filename and the encoding type like how we do on file open wizard.

Else,

I suggest you to use the FileSystemObject. It returns a TextStream which can be utilized to determine the encoding. I doubt VBA supports other types of encoding and please correct me if it does :) and happy to hear. :)

Here is a link for further considerations:-

change encode type

answered Dec 17, 2012 at 12:49

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to determine file encoding type with Excel VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related