Using ADO to save as UTF-8 encoding

Hi Guys,

Been looking fo a solution to this for a while, seems its quite a complex one and I'm 'Average' at VBA.

I created a bit of Code that helps me take an XLS files and make it a pipe delimited .txt file, the code for that is below:

Sub PipeDelimited()
'Create a text file with each row cell delimited with "|"

Dim intUsedrows As Integer
Dim intUsedcolumns As Integer


'change this filename to whatever you want

Open "\\thomson\london\Tech Unit\Clients\Key Accounts\****\Campaign Management\Feedback Files\Final Solution\****\campaign_information_" & Format(Date, "yyyymmdd") & ".txt" For Output As #1

With Worksheets(1).Range("a:u") 'This is the range, you can work out how many columns and mod to the right letter
intUsedrows = ActiveSheet.UsedRange.Rows.Count ' counts the rows being used...
intUsedcolumns = ActiveSheet.UsedRange.Columns.Count 'counts the columns being used


'for next loop
For i = 1 To intUsedrows
  For j = 1 To intUsedcolumns - 1
   Print #1, Trim(.Cells(i, j)); "|";
  Next j
   Print #1, Trim(.Cells(i, intUsedcolumns))
  Next i
End With
Close #1


MsgBox ("Done. Go to ****\Campaign Management\Feedback Files\Final Solution\Files For ****") 'added in a message to tell you its done.
End Sub

Now I've been looking for a way to output this file as UTF-8 because I get funny characters appearing as ???????? which just won't do

I found this as the most talked about solution:

Dim adoStream As adodb.Stream
    Dim adoStreamOut As adodb.Stream
    
    Set adoStream = New adodb.Stream
    
    adoStream.Charset = "UTF-8"
    adoStream.Open
    adoStream.LoadFromFile "c:\vbcode\utf8\example.txt"
  

    
    adoStream.Position = 0
    Set adoStreamOut = New adodb.Stream
    adoStreamOut.Charset = "unicode"
    adoStreamOut.Open
    adoStreamOut.WriteText adoStream.ReadText
    adoStreamOut.SaveToFile "c:\vbcode\utf8\exampleout.txt", adSaveCreateOverWrite

I'm looking for a way to incorporate this into my code if anyone can help with that? This isn't just a tell me how to do it, I'd like to understand how it works as well.

see my Feeble Attempt below that gives me an error saying it can't open the file

Sub attempt2()

Dim adoStream As ADODB.Stream
    Dim adoStreamOut As ADODB.Stream
    
    Set adoStream = New ADODB.Stream
    
    adoStream.Charset = "UTF-8"
    adoStream.Open
    adoStream.LoadFromFile "C:\Files\text.txt"
  
  
  With Worksheets(1).Range("a:u") 'This is the range, you can work out how many columns and mod to the right letter
intUsedrows = ActiveSheet.UsedRange.Rows.Count ' counts the rows being used...
intUsedcolumns = ActiveSheet.UsedRange.Columns.Count 'counts the columns being used


'for next loop
For i = 1 To intUsedrows
  For j = 1 To intUsedcolumns - 1
   Print #1, Trim(.Cells(i, j)); "|";
  Next j
   Print #1, Trim(.Cells(i, intUsedcolumns))
  Next i
End With
Close #1



MsgBox ("Done. Go to ****\Campaign Management\Feedback Files\Final Solution\Files For ****") 'added in a message to tell you its done.
    
    adoStream.Position = 0
    Set adoStreamOut = New ADODB.Stream
    adoStreamOut.Charset = "unicode"
    adoStreamOut.Open
    adoStreamOut.WriteText adoStream.ReadText
    adoStreamOut.SaveToFile "\\thomson\london\Tech Unit\Clients\Key Accounts\****\Campaign Management\Feedback Files\Final Solution\Files for ****\campaign_information_" & Format(Date, "yyyymmdd") & ".txt", adSaveCreateOverWrite


End Sub

Thanks a lot,
Liam