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

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.
Lev SeltzerAsked:
Who is Participating?
 
Surone1Commented:
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
 
Surone1Commented:
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
 
Surone1Commented:
it could also be done with the filesystemogject and a recordset if you dont want to depend on excel being installed.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Lev SeltzerAuthor Commented:
where do I put/save the procedure you made? How do I make it run?
0
 
Surone1Commented:
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
 
Surone1Commented:
you probably also need to add a reference to microsoft office excel xx library.
0
 
Surone1Commented:
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
 
Lev SeltzerAuthor Commented:
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
 
Surone1Commented:
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
 
Lev SeltzerAuthor Commented:
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
 
Surone1Commented:
this might take a while, have to go and read up on runcode..
am on a very slow connection right now
0
 
Lev SeltzerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.