?
Solved

How do I export in MS Access 2007 WITHOUT formatting and layout

Posted on 2009-02-15
12
Medium Priority
?
1,530 Views
Last Modified: 2012-05-06
I have a query in MS Access that contains a data subset of a table (some data is filtered out). I need to export the data in one step to a CSV/txt file. WHen I choose "export text file" the option to export with Formatting and Layout is selected, and also read-only, so I can't disable it.

that makes the exported file useless,since it has hundreds of horizontal and verticle lines (made of characters).

How do I disable this feature and export a standard CSV from the query?

I need to this be as automated as possible. SO while I could export to excel and then save my excel file as a CSV, I would prefer to do this in one automated step.
0
Comment
Question by:Lev Seltzer
  • 8
  • 4
12 Comments
 
LVL 13

Expert Comment

by:Surone1
ID: 23644360
Sub deedCSV()
    Dim filepath As String, xlapp As Excel.Application, xldoc As Excel.Workbook
     
    filepath = "C:\ACC_CSV.xls"
    filepath2 = "C:\ACC_CSV.txt"
    DoCmd.OutputTo acOutputTable, "Table_Name", acFormatXLS, filepath
     
    Set xlapp = CreateObject("Excel.Application")
    Set xldoc = xlapp.Workbooks.Open(filepath)
    xlapp.DisplayAlerts = False
    xldoc. SaveAs filepath2, xlCSV
     
    xldoc.Close False
    xlapp.Quit
     
End Sub
0
 
LVL 13

Expert Comment

by:Surone1
ID: 23644366
it could also be done with the filesystemogject and a recordset if you dont want to depend on excel being installed.
0
 

Author Comment

by:Lev Seltzer
ID: 23644465
where do I put/save the procedure you made? How do I make it run?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Expert Comment

by:Surone1
ID: 23644472
do you want to run it just once?
then you could paste it in a module, and press tthe run button while the cursor is within the code
0
 
LVL 13

Expert Comment

by:Surone1
ID: 23644478
you probably also need to add a reference to microsoft office excel xx library.
0
 
LVL 13

Expert Comment

by:Surone1
ID: 23644500
1. make backup of database
2. open database
3. click modules
4. click new
5. paste code in the code window (make sure tablename and filepath are correct for your db/system)
6. goto tools>references
7. scroll down and check microsoft office excel xx library.
8. click o.k
9. press the run button
0
 

Author Comment

by:Lev Seltzer
ID: 23644519
I got it to run once. Thanks! How I can put it in a convenient place within the MS Access 2007 menu structure? The frame at the left listing "all access objects" doesn't have a choice for modules. Neither does the "customize the quick access toolbar".
0
 
LVL 13

Expert Comment

by:Surone1
ID: 23644524
i have no experience with 2007 menus but you could place a button on a form, and in the onclick event just place:

deedCSV

everytime you click the button it should run
0
 

Author Comment

by:Lev Seltzer
ID: 23644567
How can I make a macro that runs this? I tried putting deedCSV and deedCSV() in the arguments field of the RunCode action, but it didn't find the deedcsv code.
0
 
LVL 13

Expert Comment

by:Surone1
ID: 23644592
this might take a while, have to go and read up on runcode..
am on a very slow connection right now
0
 
LVL 13

Accepted Solution

by:
Surone1 earned 2000 total points
ID: 23644619
o.k. not sure if it will work, but it has to be a function it seems..

Function deedCSV()
    Dim filepath As String, xlapp As Excel.Application, xldoc As Excel.Workbook
     
    filepath = "C:\ACC_CSV.xls"
    filepath2 = "C:\ACC_CSV.txt"
    DoCmd.OutputTo acOutputTable, "Table_Name", acFormatXLS, filepath
     
    Set xlapp = CreateObject("Excel.Application")
    Set xldoc = xlapp.Workbooks.Open(filepath)
    xlapp.DisplayAlerts = False
    xldoc. SaveAs filepath2, xlCSV
     
    xldoc.Close False
    xlapp.Quit
     deedcsv = 1
End Function

then put deedcsv() in the runcode part and try again..
0
 

Author Closing Comment

by:Lev Seltzer
ID: 31547058
Thanks. The function worked. I made a macro, and now with (approximately) one click, I can export the query to a CSV file. I appreciate your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

839 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