print text file with UTF8 encoding

[dalerdd]

I am trying to print from excel a text file of a sheet data, creating the text file works but I need to have it encoded as UTF8?? Here is the code I have to print.

  Open SECTXTFILE For Output As #3
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    Print #3, Cells(i, 11).Text & "^" & Cells(i, 12).Text & "^" & Cells(i, 2).Text & "^" & Cells(i, 14).Text & "^" & Cells(i, 15).Text & "^" & Cells(i, 16).Text & "^" & Cells(i, 17).Text & "|" & Cells(i, 18).Text & "|" & Cells(i, 19).Text & "|" & Cells(i, 20).Text & "^" & Cells(i, 21).Text & "^" & Cells(i, 22).Text & "^" & Cells(i, 8).Text
  Next i
  Close #3

Not sure if I need to add something to this or do I have to open the text file in Notepad and do the convert to UTF8 there?
Thanks
Dale

[G e r a r d]

Try this:

Sub testText()
    Dim str As String
    Dim AsT As String
    Dim i As Long
    
    On Error Resume Next
    
    'create text string
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
      str = Cells(i, 11).Text & "^" & Cells(i, 12).Text & _
      "^" & Cells(i, 2).Text & "^" & Cells(i, 14).Text & _
      "^" & Cells(i, 15).Text & "^" & Cells(i, 16).Text & _
      "^" & Cells(i, 17).Text & "|" & Cells(i, 18).Text & _
      "|" & Cells(i, 19).Text & "|" & Cells(i, 20).Text & _
      "^" & Cells(i, 21).Text & "^" & Cells(i, 22).Text _
      & "^" & Cells(i, 8).Text & vbNewLine
    Next i
    
    Set AsT = CreateObject("ADODB.Stream")
    'Specify stream type text/string data.
    AsT.Type = 2
    'Specify charset text data.
    AsT.Charset = "utf-8"
    AsT.Open
    AsT.writetext str
    'Save binary data To disk
    AsT.SaveToFile "H:\001ExcelForum\2014\jan\30\TextFile.txt", 2
End Sub

[dalerdd]

Hi I am getting an error with this line
Set AsT = CreateObject("ADODB.Stream")
Do I need to change ADODB to something?

[G e r a r d]

I made a mistake by assuming that the file you wanted to write to, exists ( Assumption = the mother of all FU).
- Added testing if the file exists and creating if not.
The code attempts to makes a ADODB connection with the file, so it must be existing.....

Sub testText()
    Dim str As String
    Dim fsT, tFilePath As String
    Dim i As Long
    Dim fs, obj As Object
    tFilePath = "H:\001ExcelForum\2014\jan\30\TextFile3.txt"
    'check if file exists
    If (Dir$(tFilePath, vbDirectory) = "") Then
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set obj = fs.CreateTextFile(tFilePath, True)
        obj.Close
        Set fs = Nothing
    End If
    
    On Error Resume Next
    Set fsT = CreateObject("ADODB.Stream")
    fsT.Type = 2
    fsT.Charset = "utf-8"
    fsT.Open
    
    'create text string
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        str = Cells(i, 11).Text & "^" & Cells(i, 12).Text & _
        "^" & Cells(i, 2).Text & "^" & Cells(i, 14).Text & _
        "^" & Cells(i, 15).Text & "^" & Cells(i, 16).Text & _
        "^" & Cells(i, 17).Text & "|" & Cells(i, 18).Text & _
        "|" & Cells(i, 19).Text & "|" & Cells(i, 20).Text & _
        "^" & Cells(i, 21).Text & "^" & Cells(i, 22).Text _
        & "^" & Cells(i, 8).Text & vbNewLine
        fsT.writetext str
    Next i
    fsT.SaveToFile tFilePath, 2
    fsT.Close
    
End Sub

[dalerdd]

Great thanks that worked
Dale