We help IT Professionals succeed at work.

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

Lev Seltzer
Lev Seltzer asked
on
Medium Priority
2,484 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.
Comment
Watch Question

Commented:
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

Commented:
it could also be done with the filesystemogject and a recordset if you dont want to depend on excel being installed.

Author

Commented:
where do I put/save the procedure you made? How do I make it run?

Commented:
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

Commented:
you probably also need to add a reference to microsoft office excel xx library.

Commented:
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

Author

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".

Commented:
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

Author

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.

Commented:
this might take a while, have to go and read up on runcode..
am on a very slow connection right now
Commented:
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..

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.