• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2834
  • Last Modified:

export multiple text / .csv files with correct formatting using Macro

this is my problem: I have about 20 different Queries that need to be exported into csv .txt files every day and then uploaded to our website. I want to create a macro to export all 20 with 1 click saving a lot of time, instead of doing each one seperately. That works too, but the problem is with the formatting: when I export the queries manually I click on File->Export. I can then choose "Delimited", "Seperate with Semicolon", Text Qualifier=" and "Include Field Names on First Row". But with the Macro I can't find any way of setting the format, and Access always exports the file as a formatted text file instead of a simple csv file.

What can I do to get Access to export the files the same way when running from a Macro as it does if i simply click on File->Export ?
1 Solution
Hello mpnet

I've exported queries to csv files, but I did it all with code.  I opened a recordset on the query & moved through it, writing the data to a text file and giving it a .csv file extension.

This works well and gives me complete control of delimiting charaters, headers etc.  This might be a bit slow though if there are thousands of records.

I had a quick look round and couldn't find a built in way of exporting and being able define parameters.  I'm not sure what a QueryTable is though...

If no one else knows then I could give the pure code way of doing it.

Rey Obrero (Capricorn1)Commented:
you could do it using this command

DoCmd.TransferText acExportDelim,"SpecificationName" , "qryName", "C:\Filename.csv", True

you first build your specification name by exporting a query/table using the wizard;
- select the query
- File >Export > Select Type of file  {.csv, .txt....
- Follow the wizard...
- Before the export , Click Advanced
- Give your spec a name, to be used in the succeeding export using the command above.

mpnetAuthor Commented:

thanks capricorn1, excellent, it works perfectly

To do this with a macro as you said you need to set up a export specification for then file. This is basically what you are doing every time you export a file. Save this by clicking on the advanced button and then choose the settings for the delimiter ie semi colon, test qualifier, data formats etc. If all the setting are then the same then you only will need one

Then to do the export in the macro use "TransferText" action. You  will then see in the pane below the "variables". The transfer type will be export delimited, choose the export specification that you have created, The table or queyry name, the file name (which should be the full path), the Has field names to yes if you require the field names.

SAve the step and repeat for all the files you want to export. The run the macro and enjoy the time saving.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now