Export Office Excel table to csv using a macro

I'm using Excel Tables in Excel 2010. I would like to quickly export the contents of these tables to *.csv.

My current workflow: 1. Select table manually 2. Copy the contents into a new workbook 3. Save the workbook as a *.csv file

Desired workflow: 1. Select table manually 2. Run a macro that writes to a pre-defined file name

Since the tables have unique names (e.g. CurrentDataTable), is there a function that takes the table name, target file, and desired output format and writes the output file?

Answer

There is no built-in Excel command or function that would do the kind of thing you want, but you can use VBA to program it.

The following code may be close to what you are looking for:

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

The code assumes that you have one table in each worksheet. It creates a new workbook, copies the table into Sheet 1 of that workbook, and saves the workbook as a CSV file with the same name as the table.