Export multiple columns to text file

[YasserKhalil]

Hello everyone
I have multiple columns and I need to export these columns to text files
I found a code but it exports the columns to columns in text file .. but I need to have the values beneath each other in one column in text file ..
Note : I don't need to concatenate the values in one column in the worksheet..
If possible I need to concatenate the values in one array and export that array of values to the text file
Thanks advanced for any help

 

[Marc L]

Hi !

Easy, at beginner level without the need to Copy / Paste anything !

• Open text file for output.
• Start with a main loop upon columns. (whatever number or object)
• Within a second loop upon rows number or columns cells (object),
write to file each value.
• Close text file …

Better is to loop upon objects columns and cells via For Each …
That just needs around 10 codelines and from your sample workbook
less than 0.02 second to save text file !
​

 

[YasserKhalil]

In fact I need to use arrays to do the task as the real data is too large ..
Please give me hand by putting the best and most efficient code .. I am not so perfect at programming I am just a learner ..

 

[Marc L]

As an array can not be written at once in a text file, efficient way is
For Each loop on objects collection (Columns, Cells), no needs any array …

If you really want to work with an array (but can crash if too huge !),
you can use a main loop on second array dimension (columns)
and a second loop on first array dimension (rows) …

Both ways are at beginner level and need few codelines …
I gave you a synopsis to follow in my previous post, just try !
As trying is learning …
​

 

[Marc L]

putting the best and most efficient code ..

See the thread

Procedure to export a text file with both comma and quote delimiters in Excel !
​

 

[YasserKhalil]

I tried this demo code

Sub Demo()
    Dim Rg As Range, R As Long, T
    T = Timer
    Set Rg = Sheet1.Cells(1).CurrentRegion.Rows

    With CreateObject("ADODB.Stream")
        .Charset = "Windows-1252"
        .Open

        For R = 1 To Rg.Count
            .WriteText """" & Join(Application.Index(Rg.Item(R).Resize(2).Value, 1), """;""") & """", 1
        Next

        .SaveToFile ThisWorkbook.Path & "\Export.txt", 2
        .Close
    End With

    Set Rg = Nothing
    MsgBox "Done In " & Format(Timer - T, "0.000s !"), vbExclamation, "  Export Process "
End Sub

It is ok as for exporting but I don't need delimiters and the desired results I need to be in one column in text file
Hope it is clear now

 

[jindon]

Sub test()
    Dim i As Long, a() As String
    With Cells(1).CurrentRegion
        ReDim a(1 To .Columns.Count)
        For i = 1 To .Columns.Count
            a(i) = Join(Application.Transpose(.Columns(i).Value), vbNewLine)
        Next
    End With
    With CreateObject("ADODB.Stream")
        .Charset = "Windows-1252"
        .Open
        .WriteText Join(a, vbCrLf)
        .SaveToFile ThisWorkbook.Path & "\Export.txt", 2
        .Close
    End With
End Sub

 

[YasserKhalil]

Thank you very much Mr. Jindon .. It is working exactly as I needed
You saved me .. I can now sleep well
Happy dreams Mr. Jindon