VBA Macro to save Excel file like ".txt UTF-8"

[Mgiug]

Dears,
I'm struggling to find a way to save an excel file like a ".txt UTF-8".
I used to simply save my file like a .csv (comma delimited), but now I have to deal with Chinese characters and I learnt from internet that chinese/japanese/korean characters have some kind of issues and aren't recognize properly by excel csv format (I have lot of "?????" instead of chinese characters).

I am super newbie about VBA code (so please the easiest way, if exists), everything I made I did "stealing" information from internet. This time I'm not able to find an answer on the net.

I used to use the simply code:

ActiveWorkbook.SaveAs Filename:= "C:\Temp\Import.csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False

I need a way to do the job automatically with excel, so please don't suggest me to save the file as .txt and then manually open it with notebook and save with UTF-8.

Thank you for your support

Maurizio

 

[yky]

Does your computer support Chinese? I copied your code to one of my Chinese Excel file and ran it. It saved the file in csv format which, upon opening with Notepad, preserves all the Chinese characters. The code works. I also manually saved the file in DOS csv format. There is no loss of Chinese.

 

[Mgiug]

Hi thaks for answering. I work in China so i suppose my computer support chinese (btw not checked). I tried also on colleague computers n got same problem. Anyhow don't focus on chinese characters.

I need, if someone knows, a way to save excel file as .txt utf-8 through vba macro.

Many thanks by advance!

 

[yky]

I have tried saving a file containing Chinese in MS-DOS CSV and MS-DOS TXT formats. Both work as Chinese is preserved. Thus, your problem has nothing to do with Excel.

Did you try manually save the file as Unicode txt (availabe in Excel 2010, not sure about other version)?

 

[Mgiug]

Someone knows how to create a macro to save an excel file like .txt utf8? I need this format, i can do that manually (n it works fine) but i need the automatic way. Thx

 

[Michael M]

Try

utf 8 - Export sheet as UTF-8 CSV file (using Excel-VBA) - Stack Overflow

 

[yky]

Someone knows how to create a macro to save an excel file like .txt utf8? I need this format, i can do that manually (n it works fine) but i need the automatic way. Thx

If you can do it manually, then, go to developer/record macro and record the macro of saving the file. Then, open the macro, there is your code, should be just one line.

But, I doubt you can do it manually. In the original post, you said you couldn't do it as csv file and you tried on colleague's computer, it did not work either.

I think your system is not set up correctly.

 

[Mgiug]

@Michael M. can you copy paste here the code? In China not all internet site can be accessed, so I cannot enter the one you suggested me.

@yky. If I copy paste the content of excel in notebook, then in notebook I can save the file as .txt and change the encoding to UTF-8. Cannot register a macro, cuz excel macro recorder doesn't record what u do on another program. So what I need is:

a) a macro that directly save my excel file ".txt" with "utf-8" enconding (without thinking about my machine, the configuration of machine and so on... anyway there is another system where then I have to import the file and that system need .txt utf-8).

or

b) a macro that copy excel content of a sheet, open notebook, paste the content in notebook, save as and change encoding to utf-8 in notebook and finally close notebook (I found something but doesn't work as expected, cannot share the code cuz I threw it away).

Thanks

 

[Michael M]

Option Explicit

Const strDelimiter = """"
Const strDelimiterEscaped = strDelimiter & strDelimiter
Const strSeparator = ","
Const strRowEnd = vbCrLf
Const strCharset = "utf-8"

Function CsvFormatString(strRaw As String) As String

    Dim boolNeedsDelimiting As Boolean

    boolNeedsDelimiting = InStr(1, strRaw, strDelimiter) > 0 _
        Or InStr(1, strRaw, Chr(10)) > 0 _
        Or InStr(1, strRaw, strSeparator) > 0

    CsvFormatString = strRaw

    If boolNeedsDelimiting Then
        CsvFormatString = strDelimiter & _
            Replace(strRaw, strDelimiter, strDelimiterEscaped) & _
            strDelimiter
    End If

End Function

Function CsvFormatRow(rngRow As Range) As String

    Dim arrCsvRow() As String
    ReDim arrCsvRow(rngRow.Cells.Count - 1)
    Dim rngCell As Range
    Dim lngIndex As Long

    lngIndex = 0

    For Each rngCell In rngRow.Cells
        arrCsvRow(lngIndex) = CsvFormatString(rngCell.Text)
        lngIndex = lngIndex + 1
    Next rngCell


    CsvFormatRow = Join(arrCsvRow, ",") & strRowEnd

End Function

Sub CsvExportRange( _
        rngRange As Range, _
        Optional strFileName As Variant _
    )

    Dim rngRow As Range
    Dim objStream As Object

    If IsMissing(strFileName) Or IsEmpty(strFileName) Then
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:=ActiveWorkbook.Path & "\" & rngRange.Worksheet.Name & ".csv", _
            FileFilter:="CSV (*.csv), *.csv", _
            Title:="Export CSV")
    End If

    Set objStream = CreateObject("ADODB.Stream")
    objStream.Type = 2
    objStream.Charset = strCharset
    objStream.Open

    For Each rngRow In rngRange.Rows
        objStream.WriteText CsvFormatRow(rngRow)
    Next rngRow

    objStream.SaveToFile strFileName, 2
    objStream.Close

End Sub

Sub CsvExportSelection()
    CsvExportRange ActiveWindow.Selection
End Sub

Sub CsvExportSheet(varSheetIndex As Variant)

    Dim wksSheet As Worksheet
    Set wksSheet = Sheets(varSheetIndex)

    CsvExportRange wksSheet.UsedRange

End Sub

 

[yky]

@yky. If I copy paste the content of excel in notebook, then in notebook I can save the file as .txt and change the encoding to UTF-8. Cannot register a macro, cuz excel macro recorder doesn't record what u do on another program. So what I need is:

a) a macro that directly save my excel file ".txt" with "utf-8" enconding (without thinking about my machine, the configuration of machine and so on... anyway there is another system where then I have to import the file and that system need .txt utf-8).

or

b) a macro that copy excel content of a sheet, open notebook, paste the content in notebook, save as and change encoding to utf-8 in notebook and finally close notebook (I found something but doesn't work as expected, cannot share the code cuz I threw it away).

Thanks

Can you manually save the file using the Unicode Text option in EXCEL and get the result you want? If you cannot manually save the file in Unicode format, NO MACRO can help. So, you want to first make sure you can use EXCEL to save the file in Unicode Text. If you can do that, then the code is only one-line long. If you can't, there is no hope to do it directly from Excel.

Since I can do it, so should you (if you are using Excel 2010). If you can't, there might be some system settings you need to set.