[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2006-06-25
4
Medium Priority
?
2,829 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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