How do I save ut8 encoding with excel vba macro

Question

I'm generating a tsv file from a macro but the data contains special characters like the 'tm' symbol and this in turn will be fed into a mysqlimport in the server. But because of the special characters it doesn't load the rest of the string after the special character.

I have the following macro to save it to my preffered delimiter and enclosure But now I want to specify the encoding I want to save the file in as well. How would I go about this?

Sub tsv()
    Dim SrcRg As Range
    Dim CurrRow As Range
    Dim CurrCell As Range
    Dim CurrTextStr As String
    Dim ListSep As String
    Dim FName As Variant
    FName = Application.GetSaveAsFilename("", "TSV File (*.tsv), *.tsv")
    'FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")


    'assign the character delimiter you want
    ListSep = Chr(9)
    'ListSep = "|"
    'assign the enclosure character you want
    ListEnc = "^"


    If Selection.Cells.Count > 1 Then
        Set SrcRg = Selection
    Else
        Set SrcRg = ActiveSheet.UsedRange
    End If

    Open FName For Output As #1

    For Each CurrRow In SrcRg.Rows
        CurrTextStr = ""

        For Each CurrCell In CurrRow.Cells
          CurrTextStr = CurrTextStr & ListEnc & CurrCell.Value & ListEnc & ListSep
        Next

        While Right(CurrTextStr, 1) = ListSep
      CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
        Wend

        Print #1, CurrTextStr
    Next

    Close #1
End Sub

tony bd · Accepted Answer · 2014-04-25 04:27:47Z

3

Use an ADODB stream object.

        Set BS = CreateObject("ADODB.Stream")
            '2 = text so use writetext rather than 1 = binary and use write
        BS.type = 2
            'Get the list of chartypes by typing in a command prompt ***reg query HKEY_CLASSES_ROOT\MIME\Database\Charset*** 
            BS.Charset = "UTF-8"
        BS.open
        BS.WriteText "Hi kiddies"
        'A=Array(CByte("M"),CByte("Z"))
        'BS.Write A
        BS.SaveToFile "c:\myfile.txt", 2

answered Apr 25, 2014 at 4:27

tony bd

4602 silver badges3 bronze badges

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

Collectives™ on Stack Overflow

How do I save ut8 encoding with excel vba macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related