Cannot export a table to CSV together with header with VBA in Excel

Question

I have a table inside a worksheet. I used the following found here

http://learnexcelmacro.com/wp/2017/09/save-excel-range-data-as-csv-file-through-excel-vba/#VBA-to-save-excel-table-to-csv

Sub saveTableToCSV()

Dim tbl As ListObject
Dim csvFilePath As String
Dim fNum As Integer
Dim tblArr
Dim rowArr
Dim csvVal

Set tbl = Worksheets("EXPORT").ListObjects("TableName")
csvFilePath = "C:\CSVFile.csv"
tblArr = tbl.DataBodyRange.Value

fNum = FreeFile()
Open csvFilePath For Output As #fNum
For i = 1 To UBound(tblArr)
    rowArr = Application.Index(tblArr, i, 0)
    csvVal = VBA.Join(rowArr, ",")
    Print #1, csvVal
Next
Close #fNum
Set tblArr = Nothing
Set rowArr = Nothing
Set csvVal = Nothing
End Sub

How to include the table header in the csv? My thought is to do the following

tblArr2 = tbl.HeaderRowRange.Value

But how I will join the tables?

Additionally, how to include UTF-8 support to the saved CSV?

Jorge Cavaleiro Dias · Accepted Answer · 2019-01-12 09:49:20Z

1

When you're selecting the range of cells, you are just selecting the table data:

tblArr = tbl.DataBodyRange.Value

What you need to do is to select the entire table range, like this:

tblArr = tbl.Range.Value

You can take a look at this article for some more reference

edited Jan 12, 2019 at 9:49

answered Jan 12, 2019 at 9:42

Jorge Cavaleiro Dias

3112 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user10358702 · Accepted Answer · 2019-01-12 09:45:35Z

0

I added the following

rowArr2 = Application.Index(tblArrHeaders, 0, 0)
csvVal2 = VBA.Join(rowArr2, ",")
Print #1, csvVal2 + csvVal

so I got

Set tbl = Worksheets("FOR EXPORT").ListObjects("table")
csvFilePath = "C:\CSVFile.csv"
tblArr = tbl.DataBodyRange.Value
tblArrHeaders = tbl.HeaderRowRange.Value

fNum = FreeFile()
Open csvFilePath For Output As #fNum

rowArr2 = Application.Index(tblArrHeaders, 0, 0)
csvVal2 = VBA.Join(rowArr2, ",")
Print #1, csvVal2 + csvVal

For i = 1 To UBound(tblArr)
    rowArr = Application.Index(tblArr, i, 0)
    csvVal = VBA.Join(rowArr, ",")
    Print #1, csvVal
Next
Close #fNum
Set tblArr = Nothing
Set rowArr = Nothing
Set csvVal = Nothing

answered Jan 12, 2019 at 9:45

user10358702

Collectives™ on Stack Overflow

Cannot export a table to CSV together with header with VBA in Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related