Solved

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

Posted on 2006-06-25
4
2,814 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
ID: 16978456
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 16978506
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
ID: 16982472

thanks capricorn1, excellent, it works perfectly
0
 
LVL 3

Expert Comment

by:RohitPattni
ID: 16982503
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

831 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