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?
24 Answers
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 SubThe 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.
0Here's my version of chuff's answer for Excel 2013. It also disables the modal dialogs:
Sub ExportCSV() Dim wb As Workbook, wbNew As Workbook Dim ws As Worksheet, wsNew As Worksheet Set wb = ThisWorkbook Set ws = ActiveSheet Set wbNew = Workbooks.Add Application.DisplayAlerts = False With wbNew Set wsNew = wbNew.Sheets("Sheet1") ws.Rows.Copy wsNew.Paste .SaveAs Filename:=ws.name & ".csv", FileFormat:=xlCSV, CreateBackup:=True wsNew.Delete End With Windows(ws.name & ".csv").Activate ActiveWindow.Close Application.DisplayAlerts = True
End SubI needed to do the same, but needed to specify which tables had to be exported as CSV files. I created a range named 'ExportTables', with the VBA as follows:
Public Sub Export() Dim i As Integer Dim iMax As Integer Dim sTable As String iMax = Range("ExportTables").Rows.Count For i = 1 To iMax sTable = Range("ExportTables").Cells(i, 1).Value Call ExportTable(sTable) Next i
End Sub
Public Sub ExportTable(tableName As String) Dim wkb As Workbook Dim wkbNew As Workbook Dim wks As Worksheet Dim wksNew As Worksheet Set wkb = ThisWorkbook Application.Goto Reference:=tableName Set wks = ActiveSheet Set wkbNew = Workbooks.Add Set wksNew = wkbNew.Sheets(1) wks.ListObjects(tableName).Range.Copy wksNew.Range("A1").PasteSpecial Paste:=xlPasteValues Application.DisplayAlerts = False wkbNew.SaveAs Filename:=wkb.Path & "\" & tableName & ".csv", _ FileFormat:=xlCSV, CreateBackup:=False wkbNew.Close SaveChanges:=False Application.DisplayAlerts = True ' Release object variables. Set wkb = Nothing Set wkbNew = Nothing Set wks = Nothing Set wksNew = Nothing
End SubI was getting a 1004 error from the code above .Delete in Thorsten's code. I merged it with this example and got this, which works for me:
Sub ExportCSV2() Dim wb As Workbook, wbNew As Workbook Dim ws As Worksheet, wsNew As Worksheet Set wb = ThisWorkbook Set ws = ActiveSheet Application.DisplayAlerts = False ws.Copy ActiveWorkbook.SaveAs Filename:=ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=True Windows(ws.Name & ".csv").Activate ActiveWorkbook.Close False Application.DisplayAlerts = True
End SubPlease note, this (Thorsten's) approach exports the whole sheet, not just the table, so you get a LOT of empty rows.
