Export Formatting to CSV

jarett

Board Regular
Joined
Apr 12, 2021
Messages
179
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am fumbling through some steps to automate an export from access to a csv file. I have created a macro to run a query when access opens;
VBA Code:
[CODE=vba][CODE=vba]'------------------------------------------------------------
' AutoExec
'
'------------------------------------------------------------
Function AutoExec()
On Error GoTo AutoExec_Err

    DoCmd.OutputTo acOutputQuery, "po_detail3", "MS-DOSText(*.txt)", "P:\Jarett\smtest1.csv", True, "", 1, acExportQualityPrint


AutoExec_Exit:
    Exit Function

AutoExec_Err:
    MsgBox Error$
    Resume AutoExec_Exit

End Function
[/CODE][/CODE]

All is good but the csv file that is produce has some weird formatting with all values in the first column. Also couldnt get the XL2BB add in on my excel.

My end goal is to automate this export the easiest way maybe through a task scheduler, only other issue is deleting the existing file before the new file is saved. (might be a new thread)
 

Attachments

  • Untitled.png
    Untitled.png
    25.2 KB · Views: 65

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
What is the query you are using to produce this output? If all values are in the first column, it seems that you are not using commas to separate the fields and instead are using spaces and vertical bars to emulate a table.
 
Upvote 0
The query produces the output from 3/4 different tables in the correct format. When I connect the query in excel it comes over in separate fields like it should, it just seems like when I go to a csv file or try to export the query through a macro that it goes haywire.
 
Upvote 0
Can you post the data, as it appears in your Access query (so show it to use from the Query view)?

Also, you appear to be exporting to a Text file format, instead of a CSV file format:
"MS-DOSText(*.txt)"
Why haven't you chosen a CSV option?
 
Upvote 0
Attached is the query results and in the autoexec macro they didnt have the options to put the "Output Format" as csv.
 

Attachments

  • query.png
    query.png
    22 KB · Views: 31
Upvote 0
It has been a few years since I exported CSV files out of Access, but I think I used to always use "TransferText" instead of "OutputTo".
Have you investigated trying to use that instead?

Also, I found this code that writes directly to a CSV file: Export Access query results to csv
 
Upvote 0
I did some research on the "TransferText" but that option is not available when I use the macro builder, I get completely lost when I try to go the VBA route cant seem to find a dummied down explanation. I create a module but don't know how to run it.
 
Upvote 0
Make sure you have selected "show all actions" from macro ribbon, find "ImportExportText" and see if the transfer type option you need is there. Note the "tell me more" link for topic help.

You don't run modules, you run procedures in modules and those procedures are triggered from events. A user driven event might be a button click or combo selection. You can also run code based on updates to data or other events. I don't think an AutoExcec macro is the way I'd initiate any kind of transfer though. If you get a macro working, it might present you with the option to convert to code if you're curious.
 
Upvote 0
Solution
Got the format to come out correctly through a macro, just need to figure out how to save over the existing file if I name the macro "AutoExec". Micron you mentioned you dont think "AutoExec" is not the way to go, my thought process was to use the task scheduler app to schedule opening up access and then access would run the export. Is this not a good train of thought? I know I need to figure out the code/VBA part because that's the way it seems I need to go instead of the macro.
 
Upvote 0
I forgot about the automated comment from before. The possible downside is that if anyone forgets or doesn't know of the autoexec and opens the db normally it will run the transfer regardless of whether or not it is a good or bad thing. You can live with that, or you can do the following:
- Task Scheduler opens db using a command line switch in the shortcut path
- macro first step would be to run code
- the code first tests the database Command property. If it is not a zls ("") then TS opened the file, so run the transfer code portion. If it is "" then someone manually opened the db so don't run the transfer code portion.
During initial testing, I'd have nothing more than a simple message box run while I get the TS & command line switch part setup right.

Don't know what method you ended up with, but I believe TransferSpreadsheet appends data, not over-writes it. So if all these transfer methods are the same in that aspect, then I believe you need to run a delete query first if the data is in Access. If it's in Excel, then I don't know what the approach is. Most of my Access data transport experience is in ODBC so I'm afraid I'm not much help with your issue.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top