Excel: Specify table columns in .csv export macro

Question

I have a workbook with several worksheets that each contain an inventory list. Within that workbook I have another "Generate Order" worksheet that contains a table, which I've set up to consolidate the data from the other sheets. I would like a button on that sheet called "Export Order," which would export the contents of the generateOrder table as a .csv file.

The catch is, in order to upload the order to the vendor's system, the .csv file must only contain the item number and the quantity, set up as "number,quantity" with each item on its own line.

Currently I have this macro set up:

Sub export_button()

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

    Set tbl = Worksheets("Generate Order").ListObjects("generateOrder")
    csvFilePath = "C:\Users\username\Desktop\order.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

Through a lot of Googling I've managed to get it working so that it does export the contents of generateOrder and save it as a .csv, but I'm trying to figure out the following modifications:

I would like to export only the two columns named PUBLISHER ITEM #,REORDER QTY (in that order). If it matters, there's a line break between PUBLISHER and ITEM in the first column header.
I would like the file to be named value in cell C3-order-current date.csv
If they don't already exist, I'd like the macro to create a subfolder called "Orders" within the same folder where the workbook is located, and then create a subfolder inside Orders named value in cell C3, and then save the file there (instead of to the Desktop as it currently does).

I need to figure out all of that but if you can at least help me figure out how to get only the two columns, I can try figuring out how to save it the way I want later. Many thanks!

Unknown Unknown · Accepted Answer · 2018-05-31T21:38:12

Collect the column index numbers of the two desired columns from the listobject's header row and use those to parse the array of databodyrange values.

dim c1 as long, c2 as long

Set tbl = Worksheets("Generate Order").ListObjects("generateOrder")
csvFilePath = "C:\Users\username\Desktop\order.csv"
tblArr = tbl.DataBodyRange.Value

'you should really know where the two columns are but this should fetch their
'position from the header row. Hard-code the positions if you run into trouble.
c1 = application.match("publisher" & vblf & "item #", tbl.HeaderRowRange, 0)
c2 = application.match("reorder qty", tbl.HeaderRowRange, 0)

fNum = FreeFile()
Open csvFilePath For Output As #fNum
For i = LBound(tblArr, 1) To UBound(tblArr, 1)
    csvVal = Join(array(tblArr(i, c1), tblArr(i, c2)), ",")
    Print #1, csvVal
Next

Note that I'm looking up "publisher" & vblf & "item #" with no space after publisher. Multi-line values in an excel cell only use line feed; not carriage return and line feed.

Excel: Specify table columns in .csv export macro

1 Answers