How to export multiple columns of Excel to CSV or TEXT

Question

Following my question Excel merge all columns with VBA

I have tried to move more than 255 columns and 2072 rows into 1 column with a macro and somehow Excel can't handle this and stops the macro. Due to lack of resources explained further in http://support.microsoft.com/kb/2655178. This must be an bug because when i run my macro after a certain column it randomly duplicates empty cells for selected columns. Leaving me without any possible way of deleting rows or undo and I'm forced to close the XLSM worksheet.

I have tried to find a workaround by importing the XLSM-sheet from MS Excel 2010 into MS Access 2010. Unfortunately MS Access also has a limitation and does not import more than 255 columns! I also failed linking the XLSM-sheet with a MS Access Table because of the same 255 limitation restrictions.

note: my worksheet does not contain any headers

Now I would like to Save or Export every individual column from my spreadsheet to a text document (see example) which would make it easier to import it later by drag and drop into MS Access or PHP DB or Excel.

Export example

column A ; column B ; column C; column D (etc. until column WC)

Save as

export0001.txt
export0002.txt
export0003.txt
export0004.txx
(etc.........)
export0999.txt

As far as I know there is no built-in Excel command or function that would do the kind of thing I want. There is a VBA code I might be able to use

Set NewBook = Workbooks.Add 
Do 
    fName = Application.GetSaveAsFilename 
Loop Until fName <> False 
NewBook.SaveAs Filename:=fName

https://msdn.microsoft.com/en-us/library/office/ff841185.aspx

Cross-site duplicate referenced in OP's self-answer: http://superuser.com/questions/541460/export-office-excel-table-to-csv-using-a-macro — Jean-François Corbett, Mar 09 '15 at 14:35

score 0 · Answer 1 · edited Mar 20 '17 at 10:04

Reference: https://superuser.com/questions/541460/export-office-excel-table-to-csv-using-a-macro

Sub ExportTable()

    Dim wb As Workbook, wbNew As Workbook
    Dim ws As Worksheet, wsNew As Worksheet
    Dim wbNewName As String


   Set wb = ThisWorkbook
   Set ws = ActiveSheet

   Set wbNew = Workbooks.Add

   With wbNew
       Set wsNew = wbNew.Sheets("Sheet1")
       wbNewName = ws.ListObjects(1).Name
       ws.ListObjects(1).Range.Copy
       wsNew.Range("A1").PasteSpecial Paste:=xlPasteAll
       .SaveAs Filename:=wb.Path & "\" & wbNewName & ".csv", _
             FileFormat:=xlCSVMSDOS, CreateBackup:=False
   End With

End Sub

How to export multiple columns of Excel to CSV or TEXT

1 Answers1