1

I have hundreds of CSV files containing text in Japanese. When I open them in Excel, the characters look garbled. And when I open them with Notepad, the characters look fine. I got my computer's locale changed to Japan, but the problem did not get resolved.

However, when I manually open the files in Notepad, save them with the character encoding UTF-8 without BOM, and then open the files in Excel, the characters look as they should. I have also recorded a macro with Notepad++ for this, but it cannot be used with VBA as per my knowledge. Please correct me if I am wrong.

I want to automate this process with Excel macro. I tried searching the code on the Internet, but did not get any solution.

Unfortunately, there is no way to attach the file. But, can someone please give a macro to: Open a .csv file in Excel with the character encoding UTF-8 without BOM?

Thanks in advance!

  • Does this answer your question? [VBA : save a file with UTF-8 without BOM](https://stackoverflow.com/questions/31435662/vba-save-a-file-with-utf-8-without-bom) – braX Oct 12 '21 at 11:50
  • I tried to run the code after changing the file path\name against .SaveToFile, but it did not work... Is there anything else I need to change? –  Oct 12 '21 at 12:50
  • "it did not work" tells people exactly nothing. Does it error? – braX Oct 12 '21 at 12:51
  • Just to clarify, but normally Excel defaults to opening CSV files in the default Windows locale encoding, which if the file is actually UTF-8 without BOM looks garbled. Excel normally needs UTF8 **with** BOM to use UTF-8. This seems backward. – Mark Tolonen Oct 12 '21 at 20:10
  • Hi @braX Sorry for making it unclear... There is no error and I don't see the timestamp changing, which means the file is not getting saved... Nothing seems to be happening to the user's eye, although something could actually be happening in the background. –  Oct 13 '21 at 04:41
  • Hi @MarkTolonen Thanks for the information. I tried saving them with different enumerations given on [link](https://learn.microsoft.com/en-us/office/vba/api/office.msoencoding), but that too did not make the characters normal... –  Oct 13 '21 at 04:44
  • What encoding does Notepad++ show when you first open the file. ? Does the filesize decrease by 3 bytes when you save without BOM . – CDP1802 Oct 13 '21 at 16:38

0 Answers0