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

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

CodePudding user response：

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

CodePudding user response：

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