Add a header row to CSV - not column names

[shank]

I'm using the Saved Exports to generate a CSV file - no problem.



My problem is the recipient needs a 3 digit text value XYZ to be row#1. Data starts on Row#2. I can't figure out how to get that first row into the CSV. I don't want to have to edit files manually. Is there a way to get this done in MS Access?

thanks!

[pbaldy]

Two methods come to mind: exporting a UNION query that joins the single line with the actual data, and doing the export without then using automation to open the file and insert a row. I lean towards the first.

[JoeM]

Two methods come to mind: exporting a UNION query that joins the single line with the actual data, and doing the export without then using automation to open the file and insert a row. I lean towards the first.

I have used this method Paul's describes myself. I often include a "Sort By" field, so I can ensure that the value I want shows up in the first row (so I would have a value of "1" for the first row, and "2" for the other rows). I don't return this row in my export, just using it for sorting.

[shank]

Thanks to both! Excellent solution and it worked as expected.

Now I have one last task. When I save the CSV I need to save it to the recipient's filename format. Specifically, it needs to be XYZ20120927.csv (vendorID + yyyymmdd). Any thoughts on how I can get that done in the export process?

Thanks again!

[pbaldy]

What is your process? TransferText and OutputTo both allow you to specify the file name if I'm not mistaken.

[shank]

I'm exporting to a text file, then saving that process as a Saved Export. In that process I can specify a filename, but that filename becomes hardcoded in the process. i.e. It outputs to the same filename each time I export. What I'd like to do is auto generate a filename that conforms to the recipients demands. I don't want to do it manually each time. Specifically, it needs to be XYZ20120927.csv (vendorID + yyyymmdd). Any thoughts on how I can get that done in the export process? I'm thinking it's going to take some VB but that's out of my league to write that code.

thanks!

[JoeM]

If you use the "Convert Macros to Visual Basic" feature, it will convert your Macro to VBA. From there, the code can be modified to make your file names dynamic, based on some variables. So try that part first, and if you get stuck updating the code, post the code that is generated from that conversion, and explain where this vendor ID is coming from.

[shank]

I created a macro with TransferText and converted to a VB and got the below. Looks simple enough, but not knowing VB I don't know how to generate an auto filename.

Filename object: "S:\Orders\Export\XYZ20120927.csv"

I'm guessing something like:
"S:\Orders\Export\XYZ" & Format(Now(), "yyyy") & Format(Now(), "mm") & Format(Now(), "dd") & "
.csv"

1) Is the above syntax correct?
2) Do paste the filename clause back into the TransferText macro when complete?

thanks!
'------------------------------------------------------------
' CCC_Uploads
'
'------------------------------------------------------------
Function CCC_Uploads()
On Error GoTo CCC_Uploads_Err
DoCmd.SetWarnings False
DoCmd.TransferText acExportDelim, "CCC Export Specification", "qry_CCC_Upload", "S:\Orders\Export\EYD20120927.csv", False, ""
DoCmd.SetWarnings True

CCC_Uploads_Exit:
Exit Function
CCC_Uploads_Err:
MsgBox Error$
Resume CCC_Uploads_Exit
End Function

[JoeM]

Something like this:

Function CCC_Uploads(Vendor_ID as String)

    Dim myFileName as String
    myFileName="S:\Orders\Export\" & Vendor_ID & Format(Now(), "yyyymmdd") & ".csv"

    On Error GoTo CCC_Uploads_Err
    DoCmd.SetWarnings False
    DoCmd.TransferText acExportDelim, "CCC Export Specification", "qry_CCC_Upload", myFileName, False, ""
    DoCmd.SetWarnings True

CCC_Uploads_Exit:
    Exit Function

CCC_Uploads_Err:
    MsgBox Error$
    Resume CCC_Uploads_Exit

End Function

Note that I added an Input of the Vendor_ID. So if you pass that to your function, it should incorporate it into the file name.
I find it easier to build the complete FileName outside of the TransferText action in a variable, and then reference that variable in the TransferText action.

[shank]

It does work as expected when I run the module. Great! However, my knowledge of modules are minimal at best. How do I incorporate the module into the macro? I tried "OpenModule", but it just opened it for editing. How do I get it to "run" in my process?

thanks!

[shank]

Never mind! Found it. RunCode then find the function. Works great!

Thanks again!