By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,035 Members | 1,555 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,035 IT Pros & Developers. It's quick & easy.

export .csv with filename dependent on date

P: 7
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)?
Sep 5 '07 #1
Share this Question
Share on Google+
7 Replies


SammyB
Expert 100+
P: 807
Should be possible & easy. Access doesn't care what you call the file:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText acExportDelim, "delim", "tablename", "filename", False, "headersrequired"
  2. which usually translates to
  3. DoCmd.TransferText acExportDelim, "", "tablename", "filename", False, ""
  4. where tablename is the name of your table and filename is the name of your file
  5.  
Sep 5 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1. Dim filename As String
  2. Dim path As String
  3.  
  4.     path = "full directory path to the folder where the file will be placed" 
  5.     ' remember to end the path string with a \
  6.  
  7.     filename = Format(InputBox("Enter date:"), "dd/mm/yyyy")
  8.     filename = path & filename & ".pos"
  9.  
  10.     ' then use the code sammyB gave you
  11.  
  12.  
You can name the file anything you like (e.g. .pos) but I can't guarantee how it will behave. Try it and see.
Sep 5 '07 #3

VBPhilly
P: 95
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.
Sep 5 '07 #4

P: 7
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

Expand|Select|Wrap|Line Numbers
  1. Dim filename As String
  2. Dim path As String
  3.  
  4.     path = "full directory path to the folder where the file will be placed" 
  5.     ' remember to end the path string with a \
  6.  
  7.     filename = Format(InputBox("Enter date:"), "dd/mm/yyyy")
  8.     filename = path & filename & ".pos"
  9.  
  10.     ' then use the code sammyB gave you
  11.  
  12.  
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub exportreport_Click()
  2. On Error GoTo Err_exportreport_Click
  3.  
  4.     DoCmd.TransferText acExportDelim, Query, "ExportFile", "Q:\Messengers\postage_export.csv"
  5.  
  6. Exit_exportreport_Click:
  7.     Exit Sub
  8.  
  9. Err_exportreport_Click:
  10.     MsgBox Err.Description
  11.     Resume Exit_exportreport_Click
  12.  
  13. End Sub
  14.  
I basically hard coded the filename and used a query to prompt the user for a date and grab the information.
Sep 6 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
I've never tried to export a .pos file. Try exporting it as a .csv and then rename the file.
Sep 6 '07 #6

SammyB
Expert 100+
P: 807
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub exportreport_Click()
  2. On Error GoTo Err_exportreport_Click
  3.  
  4. DoCmd.TransferText acExportDelim, Query, "ExportFile", "Q:\Messengers\postage_export.csv"
  5.  
  6. Exit_exportreport_Click:
  7. Exit Sub
  8.  
  9. Err_exportreport_Click:
  10. MsgBox Err.Description
  11. Resume Exit_exportreport_Click
  12.  
  13. End Sub
  14.  
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?
Sep 6 '07 #7

P: 7
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.
Sep 6 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.