output UTF-8 text files in excel vba

Question

I want to output the Japanese written on the sheet as a UTF-8 text file. The current code also works, but this code export SJIS file. Is the problem that I am getting it in range and outputting it? Or is there something wrong with the SaveAs Filename code?

Set rng = Range("A1").CurrentRegion

Workbooks.Add
ActiveSheet.Cells.Select
Selection.NumberFormatLocal = "@"
rng.Copy ActiveSheet.Range("A1")

Rows(1).Delete

ActiveWorkbook.SaveAs Filename:=fPath & fName, FileFormat:=xlText, Local:=True
ActiveWindow.Close

Worksheets("sheetname").Select
    
Application.DisplayAlerts = True

@FaneDuru I tried it, but the result was a UTF-16 file with BOM. — SJ W
– SJ W, Commented Oct 11, 2021 at 14:24
Then, try xlCSVUTF8. Letting the ."txt" format in the file name... — FaneDuru
– FaneDuru, Commented Oct 11, 2021 at 14:34
Since you do not say anything, I can understand that the above suggestion didn't work too. Is the separator of the text file a space (" "), Comma, Tab, or what? I will try helping with a different approach. — FaneDuru
– FaneDuru, Commented Oct 11, 2021 at 14:54

FaneDuru · Accepted Answer · 2021-10-11 14:58:46Z

2

Please, try the next code. It will not use Excel `SaveAs. It will build a string from the existing cells and save it in a different way:

Sub testExportUth8_NoBOM()
    Dim sh As Worksheet, strTxt As String, strLine As String
    Dim strName As String, arr, i As Long, j As Long, sep As String
    
    Set sh = ActiveSheet
    arr = sh.UsedRange.Value
    sep = "," 'you can use here any needed separator
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            strLine = strLine & arr(i, j) & sep
        Next j
        strLine = left(strLine, Len(strLine) - 1)
        strTxt = strTxt & strLine & vbCrLf
        strLine = ""
    Next i
    strName = ThisWorkbook.path & "\testUTF8_No_BOOM.txt" 'use here the name you want
    WriteUTF8WithoutBOM strTxt, strName
End Sub

Private Function WriteUTF8WithoutBOM(strText As String, fileName As String)
  Dim UTFStream As Object, BinaryStream As Object
  With CreateObject("adodb.stream")
     .Type = 2: .Mode = 3: .Charset = "UTF-8"
     .LineSeparator = -1
     .Open: .WriteText strText, 1
     .Position = 3 'skip BOM' !!!
     Set BinaryStream = CreateObject("adodb.stream")
         BinaryStream.Type = 1
         BinaryStream.Mode = 3
         BinaryStream.Open
        .CopyTo BinaryStream
        .Flush
    .Close
  End With
    BinaryStream.SaveToFile fileName, 2
    BinaryStream.Flush
    BinaryStream.Close
End Function

Please run it and send some feedback.

answered Oct 11, 2021 at 14:58

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

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

11 Comments

SJ W Over a year ago

I tried the code, but I couldn't get it to work because the / and \ marks were mixed up in the filename path... So I tried "/testUTF8_No_BOOM.txt" instead of "\testUTF8_No_BOOM.txt", but it didn't work.

FaneDuru Over a year ago

Why should you change the path separator? Isn't Windows your OS? Isn't the working workbook situated on a local path? Did you try using the path from your initial code?

SJ W Over a year ago

This is a windows os. I'm trying this on a computer at work. The only local drive is c, so when I tried to output to that, I got a 3004 error. So I changed the path and tried to implement it, but I got a 3004 error as well.

FaneDuru Over a year ago

Try using the file name you used in the previous trials, please. The full name...

FaneDuru Over a year ago

@SJ W Glad I (finally) could help! But, just from curiosity: why did you talk about mixing up the slashes with back slashes? Did you try the code as it is and it raised an error? If yes, on which code line? Please, also try inserting Debug.Print strName , immediately after giving it a value. What does it return in Immediate Window?

|

Collectives™ on Stack Overflow

output UTF-8 text files in excel vba

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related