Solved

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

Posted on 2006-06-25
4
2,811 Views
Last Modified: 2008-01-09
Hi,
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 ?
0
Comment
Question by:mpnet
4 Comments
 
LVL 3

Expert Comment

by:IainTheVBALearner
Comment Utility
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.

Iain
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
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.

0
 

Author Comment

by:mpnet
Comment Utility

thanks capricorn1, excellent, it works perfectly
0
 
LVL 3

Expert Comment

by:RohitPattni
Comment Utility
HI

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.

Rohit

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now