How to save a text file (CSV) with UTF-8 without BOM encoding in VBA (Excel)?

Question

So this was my initial question. The answer to my question below, seems to be that the only solution to get UTF-8 (and UTF-8 without BOM) encoding, is to use the ADODB.Stream object.
The answer to my new question in the subject line is posted as a code.

I am sitting here and trying to Save an Excel sheet as a .CSV-file with a VBA macro.
However, I am wondering if it matters whether I use ADODB/ADODB.Stream or just .SaveAs Fileformat:=xlCSV. I have tried to Google it, and it seems like I cannot find an answer to which method is the "best". I would need it to be comma delimited, UTF-8, and double quotations ("") as text-identifier.

Is it correct that when you use Fileformat:=, it is not possible to SaveAs UTF-8, since the xlCSV is not using that encoding? YES, that is correct.

See my answer for the solution.

Answer 1

thank you for posting this question and also the solution. It helped me a lot. Yes, I also found that SaveAs does not save the CSV file in UTF8. In my case it uses shift-JIS. The adodb.stream worked well for me.

However, I am not sure why but I had to declare some constants (enum) you used in the code. (I am really new to VBA so maybe I missed something about why this happens). I added this in the beginning of the function, then it worked perfectly:

  Const adTypeText = 2
  Const adModeReadWrite = 3
  Const adTypeBinary = 1
  Const adLF = 10
  Const adSaveCreateOverWrite = 2
  Const adWriteLine = 1

I got the value from Microsoft docs. Once again, thanks!