export .csv with filename dependent on date

[kchang]

I am not sure if this is even possible. All of my research has me leaning towards "no", but maybe I missed something. I am trying to export and save a .csv file whose name depends on the date entered. That is, I have a Microsoft Access program with a button that exports a .csv file. The button asks for a date and then gathers all of the information inputed on that day and exports it. Is there a way to code it such that it takes the date given by the user (ex. 9/1) and turns that into the filename (ex. 070901.csv)?

Also, would it be possible to have the file be csv without actually naming it .csv (i.e. .pos)?

[SammyB]

Should be possible & easy. Access doesn't care what you call the file:

DoCmd.TransferText acExportDelim, "delim", "tablename", "filename", False, "headersrequired"
which usually translates to
DoCmd.TransferText acExportDelim, "", "tablename", "filename", False, ""
where tablename is the name of your table and filename is the name of your file
 

[MMcCarthy]

I am not sure if this is even possible. All of my research has me leaning towards "no", but maybe I missed something. I am trying to export and save a .csv file whose name depends on the date entered. That is, I have a Microsoft Access program with a button that exports a .csv file. The button asks for a date and then gathers all of the information inputed on that day and exports it. Is there a way to code it such that it takes the date given by the user (ex. 9/1) and turns that into the filename (ex. 070901.csv)?

Also, would it be possible to have the file be csv without actually naming it .csv (i.e. .pos)?

All you have to do is create a variable to hold the filename. You don't say how you are getting it so I'm presuming an input box

Dim filename As String
Dim path As String
 
    path = "full directory path to the folder where the file will be placed" 
    ' remember to end the path string with a \
 
    filename = Format(InputBox("Enter date:"), "dd/mm/yyyy")
    filename = path & filename & ".pos"
 
    ' then use the code sammyB gave you
 
 

You can name the file anything you like (e.g. .pos) but I can't guarantee how it will behave. Try it and see.

[VBPhilly]

I am not sure if this is even possible. All of my research has me leaning towards "no", but maybe I missed something. I am trying to export and save a .csv file whose name depends on the date entered. That is, I have a Microsoft Access program with a button that exports a .csv file. The button asks for a date and then gathers all of the information inputed on that day and exports it. Is there a way to code it such that it takes the date given by the user (ex. 9/1) and turns that into the filename (ex. 070901.csv)?

Also, would it be possible to have the file be csv without actually naming it .csv (i.e. .pos)?

Most .csv files ive seen are simply comma separated values (csv). In other words, you can open them with any text editor.
A good test is if you created a .csv, Excel defaults to opening it. If the format is bad, Excel will complain.
Not sure if this answers your question or not.

[kchang]

All you have to do is create a variable to hold the filename. You don't say how you are getting it so I'm presuming an input box

Dim filename As String
Dim path As String
 
    path = "full directory path to the folder where the file will be placed" 
    ' remember to end the path string with a \
 
    filename = Format(InputBox("Enter date:"), "dd/mm/yyyy")
    filename = path & filename & ".pos"
 
    ' then use the code sammyB gave you
 
 

You can name the file anything you like (e.g. .pos) but I can't guarantee how it will behave. Try it and see.

Thanks, your suggestion worked for the most part. Access doesn't seem to like the file format I'm giving it (.pos), saying that it "Cannot update. Database or object is read-only." It works if I name it something conventional (i.e. .csv). Any thoughts?

As for how I got the information/filename here is my original code:

Private Sub exportreport_Click()
On Error GoTo Err_exportreport_Click
 
    DoCmd.TransferText acExportDelim, Query, "ExportFile", "Q:\Messengers\postage_export.csv"
 
Exit_exportreport_Click:
    Exit Sub
 
Err_exportreport_Click:
    MsgBox Err.Description
    Resume Exit_exportreport_Click
 
End Sub
 

I basically hard coded the filename and used a query to prompt the user for a date and grab the information.

[MMcCarthy]

I've never tried to export a .pos file. Try exporting it as a .csv and then rename the file.

[SammyB]

Thanks, your suggestion worked for the most part. Access doesn't seem to like the file format I'm giving it (.pos), saying that it "Cannot update. Database or object is read-only." It works if I name it something conventional (i.e. .csv). Any thoughts?

As for how I got the information/filename here is my original code:

Private Sub exportreport_Click()
On Error GoTo Err_exportreport_Click
 
DoCmd.TransferText acExportDelim, Query, "ExportFile", "Q:\Messengers\postage_export.csv"
 
Exit_exportreport_Click:
Exit Sub
 
Err_exportreport_Click:
MsgBox Err.Description
Resume Exit_exportreport_Click
 
End Sub
 

I basically hard coded the filename and used a query to prompt the user for a date and grab the information.

I think your parameters are wrong for line 4. See my post above. Did you open the file in the editor to see if it looked OK?

[kchang]

I think your parameters are wrong for line 4. See my post above. Did you open the file in the editor to see if it looked OK?

That's how I used to export the file. For all intents and purposes it seemed to work; however, the users want the file to be named based on the date. Your code combined with what mmccarthy gave me does the job.

I've never tried to export a .pos file. Try exporting it as a .csv and then rename the file.

Yeah, that's what I ended up doing.