Export colums as individual csv files

[steffen82]

Hi Could someone advise on how you can export each column to an individual csc or tab file using the header as the file name please.

i.e.
[TABLE="width: 377"]
<tbody>[TR]
[TD]Street 1[/TD]
[TD]Street 2[/TD]
[TD]Street 3[/TD]
[/TR]
[TR]
[TD]1 [/TD]
[TD]1C [/TD]
[TD]1 MOORGROVE HOUSE [/TD]
[/TR]
[TR]
[TD]2 [/TD]
[TD]3 [/TD]
[TD]2 MOORGROVE HOUSE [/TD]
[/TR]
[TR]
[TD]3 [/TD]
[TD]5 [/TD]
[TD]3 MOORGROVE HOUSE [/TD]
[/TR]
[TR]
[TD]4 [/TD]
[TD]7 [/TD]
[TD]4 MOORGROVE HOUSE [/TD]
[/TR]
[TR]
[TD]5 [/TD]
[TD]9 [/TD]
[TD]5 MOORGROVE HOUSE [/TD]
[/TR]
[TR]
[TD]6 [/TD]
[TD]11 [/TD]
[TD]6 MOORGROVE HOUSE [/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

Exported to these files

Filename = Street 1.txt or .csv
File contents:[TABLE="width: 88"]
<tbody>[TR]
[TD]1 [/TD]
[/TR]
[TR]
[TD]2 [/TD]
[/TR]
[TR]
[TD]3 [/TD]
[/TR]
[TR]
[TD]4 [/TD]
[/TR]
[TR]
[TD]5 [/TD]
[/TR]
[TR]
[TD]6 [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

File name = Street 2.txt
File contents:
[TABLE="width: 131"]
<tbody>[TR]
[TD]1C [/TD]
[/TR]
[TR]
[TD]3 [/TD]
[/TR]
[TR]
[TD]5 [/TD]
[/TR]
[TR]
[TD]7 [/TD]
[/TR]
[TR]
[TD]9 [/TD]
[/TR]
[TR]
[TD]11 [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

File name = Street 3.txt
[TABLE="width: 158"]
<tbody>[TR]
[TD]1 MOORGROVE HOUSE [/TD]
[/TR]
[TR]
[TD]2 MOORGROVE HOUSE [/TD]
[/TR]
[TR]
[TD]3 MOORGROVE HOUSE [/TD]
[/TR]
[TR]
[TD]4 MOORGROVE HOUSE [/TD]
[/TR]
[TR]
[TD]5 MOORGROVE HOUSE [/TD]
[/TR]
[TR]
[TD]6 MOORGROVE HOUSE [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[John_w]

Try this, changing the folder path as required.

Public Sub Export_Each_Column_As_CSV_File()

    Dim lastCol As Long, lastRow As Long, c As Long
    
    With ActiveSheet
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For c = 1 To lastCol
            lastRow = .Cells(Rows.Count, c).End(xlUp).Row
            Open "C:\path\to\folder\" & .Cells(1, c).Value & ".csv" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
            Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Join(Application.Transpose(.Cells(2, c).Resize(lastRow - 1).Value), vbCrLf)
            Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
        Next
    End With
    
End Sub

 

[steffen82]

Hi
Probably a silly question but is that a macro?

 

[John_w]

Yes! http://www.contextures.com/xlvba01.html#videoreg explains where and how to insert the code.