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.
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.
it could also be done with the filesystemogject and a recordset if you dont want to depend on excel being installed.
ASKER
where do I put/save the procedure you made? How do I make it run?
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
then you could paste it in a module, and press tthe run button while the cursor is within the code
you probably also need to add a reference to microsoft office excel xx library.
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
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
ASKER
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".
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
deedCSV
everytime you click the button it should run
ASKER
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.
this might take a while, have to go and read up on runcode..
am on a very slow connection right now
am on a very slow connection right now
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.Applic
Set xldoc = xlapp.Workbooks.Open(filep
xlapp.DisplayAlerts = False
xldoc. SaveAs filepath2, xlCSV
xldoc.Close False
xlapp.Quit
End Sub