How to export Multiple Columns into individual text files in Excel all languages

Question

I have found this code that is doing exactly what I need but when it exports the different languages some language comes out as ????????????? (I'm a novice and need help please?)

Sub export_data()
Dim row, column, i, j As Integer
Dim fullPath, myFile As String

fullPath = "C:\Workspace"
row = 21
column = 5

For i = 1 To column
    myFile = Cells(1, i).Value + ".txt"
    myFile = fullPath + "/" + myFile
    Open myFile For Output As #1
    For j = 2 To row
        Print #1, Cells(j, i).Value
    Next j
    Close #1
Next i

End Sub

https://stackoverflow.com/questions/18905489/how-to-save-a-unicode-character-to-a-text-file Question is about Mac VBA, but most responses are not for Mac... — Tim Williams, Nov 12 '21 at 19:10
I found this that seems to be the answer but I'm novice here. How do I combine the two codes to make it work if possible. https://stackoverflow.com/a/17699668/17321649 — Emile van der Merwe, Nov 12 '21 at 19:30

Tim Williams · Answer 1 · 2021-11-13T00:15:19.747

Try this:

'requires reference to Microsoft ActiveX DataObjects library
'  ("tools >> references")
Sub export_data()
    
    'use Const for fixed values
    Const FULL_PATH As String = "C:\Temp\"
    
    'you need to specifiy the type for every variable
    Dim row As Long, column As Long, i As Long, j As Long
    Dim myFile As String, ws As Worksheet
    Dim adoStream As ADODB.Stream
    
    Set adoStream = New ADODB.Stream
    adoStream.Type = adTypeText
    adoStream.Charset = "Unicode"
    
    row = 21
    column = 5
    Set ws = ActiveSheet 'or whatever
    
    For i = 1 To column
        adoStream.Open
        For j = 2 To row
            Debug.Print ws.Cells(j, i).Value
            adoStream.WriteText ws.Cells(j, i).Value & vbNewLine
        Next j
        
        adoStream.SaveToFile FULL_PATH & ws.Cells(1, i).Value & ".txt", _
                             adSaveCreateOverWrite
        adoStream.Close
    Next i

End Sub

It worked after changing it a bit How can I post the code here — Emile van der Merwe, Nov 12 '21 at 23:57

score -1 · Answer 2 · edited Nov 13 '21 at 00:14

Sub exportdat()
    
    'use Const for fixed values
    Const FULL_PATH As String = "C:file location"
    
    'you need to specifiy the type for every variable
    Dim row As Long, column As Long, i As Long, j As Long
    Dim myFile As String, ws As Sheet1
    Dim adoStream As Object
    
    Set adoStream = CreateObject("ADODB.Stream")
  
    adoStream.Charset = "utf-8"
    
    row = 99
    column = 104
    Set ws = ActiveSheet 'or whatever
    
    For i = 1 To column
        adoStream.Open
        For j = 3 To row
            Debug.Print ws.Cells(j, i).Value
            adoStream.WriteText ws.Cells(j, i).Value & vbNewLine
        Next j
        Debug.Print
  adoStream.SaveToFile FULL_PATH & ws.Cells(1, i).Value & ".txt", 2 ' 2: Create Or Update
                             
        adoStream.Close
    Next i

End Sub

How to export Multiple Columns into individual text files in Excel all languages

2 Answers2