CODEWITHSUNDEEP
vbaexcel
user2694306
user2694306

Reputation: 4050

Exporting to CSV, Non Sequential Columns from a Table

I am trying to write a VBA macro to export the content of table to a csv file. I would like to include only certain columns and ignore all other ones. My code works fine for a single column (in the case below, col_a), but it fails when trying to export multiple columns (e.g. col_a and col_c).

Sub saveTableToCSV()

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

    Set tbl = Worksheets("primary").ListObjects("main")
    csvFilePath = "C:\Users\USERNAME\Downloads\testsheet.csv"

    Application.DisplayAlerts = False
    tblArr = Range("main[[col_a]]").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
    Application.DisplayAlerts = False

    Close #fNum
    Set tblArr = Nothing
    Set rowArr = Nothing
    Set csvVal = Nothing
End Sub

An example of my data would be below. The table name is "main" and the sheet name is "primary":

-------------------------
| col_a | col_b | col_c |
-------------------------
|   1   |   2   |   3   |
|   4   |   5   |   6   |
-------------------------

My desired output would be a csv file that looks like the following:

a,c
1,3
4,6

Can anyone point out what I am doing wrong? Thanks.

Upvotes: 1

Views: 112

Answers (1)

YasserKhalil
YasserKhalil

Reputation: 9538

Replace that line

tblArr = Range("main[[col_a]]").Value

with these two lines

    tblArr = tbl.DataBodyRange.Value
    tblArr = Application.Index(tblArr, Evaluate("ROW(1:" & UBound(tblArr) & ")"), [{1,3}])

Upvotes: 4

Related Questions