Solved

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

Posted on 2006-06-25
4
2,816 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

828 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