Export Formatting to CSV

[jarett]

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;

[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)

 

[jarett]

After a lot of clicking buttons on the task scheduler and playing with the macro got it to work.

Function AutoExec()
On Error GoTo AutoExec_Err

    DoCmd.TransferText acExportDelim, "Smtest123 Export Specification", "po_detail3", "P:\Jarett\123.csv", True, ""
    DoCmd.Quit acSave


AutoExec_Exit:
    Exit Function

AutoExec_Err:
    MsgBox Error$
    Resume AutoExec_Exit

End Function

 

[Micron]

Glad you got it working, but what's with the acSave. Not only do I think there's nothing to save, it doesn't appear to be a valid option:

AcQuitOption enumeration (Access)

Office VBA reference topic

docs.microsoft.com

 

[jarett]

I need access to close after the autoexec runs so I added a "QuitAccess" to the end of the AutoExec macro. Figured I wanted it to save anything if something changed, which nothing should change once I get it going.

 

[jarett]

Well should of know it wouldn't be that easy, now I am getting this error when the macro tries to close access

 

[Micron]

ditch the save and see what happens. Like I said, there's nothing to save and AFAIK it's the wrong option. Did you review the link?