Export data from Access table to .csv file

[Derek]

Hi Guys

I want to write a piece of code that will export data from Access table to .csv file and save it down in "U:\Daily Work" folder. The .csv file naming convention will be PO_Daily_Extract. Also can we do some validation on export like checking if the file name already exists in the folder then replace with the new one or display a message box saying "The file name already exists, do you want to replace the existing file or save it with new name "...

Can anyone please help me with this?

Thanks

 

[Mark_]

Something like the "External Data" -> "Export" options up in the ribbon or something different?

 

[moke123]

something like this?

 

[jdraw]

Derek,

Further to the advice so far, have you tried looking for an example on Youtube?
eg. https://www.youtube.com/watch?v=f_nAPaX1s8w

 

[Derek]

Guys I want to do it using vba ? Any help will be much appreciated.

Thanks

 

[jdraw]

Here is some vba that may be helpful. You seem to want to do this from scratch.

'---------------------------------------------------------------------------------------
' Procedure : testWrite
' Author    : mellon
' Date      : 20-Mar-2017
' Purpose   : Test routine to show WRITE statement produces csv file.
'---------------------------------------------------------------------------------------
'
Sub testWrite()


10       On Error GoTo testWrite_Error

20    Open "C:\users\jack\documents\TESTFILE.txt" For Output As #1    ' Open file for output.
30    Write #1, "Hello World", 234    ' Write comma-delimited data.
40    Write #1,    ' Write blank line.

    Dim MyBool, MyDate, MyNull, MyError
    ' Assign Boolean, Date, Null, and Error values.
50    MyBool = False: MyDate = #2/12/1969#: MyNull = Null
60    MyError = CVErr(32767)
    ' Boolean data is written as #TRUE# or #FALSE#. Date literals are
    ' written in universal date format, for example, #1994-07-13#
    ' represents July 13, 1994. Null data is written as #NULL#.
    ' Error data is written as #ERROR errorcode#.
70    Write #1, MyBool; " is a Boolean value"
80    Write #1, MyDate; " is a date"
90    Write #1, MyNull; " is a null value"
100   Write #1, MyError; " is an error value"
110   Close #1    ' Close file.

120      On Error GoTo 0
testWrite_Exit:
130      Exit Sub

testWrite_Error:

140       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testWrite of Module ModuleTesting_CanKill"
150       GoTo testWrite_Exit
End Sub

Sample output:

[COLOR="Blue"]"Hello World",234

#FALSE#," is a Boolean value"
#1969-02-12#," is a date"
#NULL#," is a null value"
#ERROR 32767#," is an error value"[/COLOR]

 

[moke123]

Guys I want to do it using vba

Did you look at the TransferText Method? I used it in the example above.
I also included a process to check for an existing filename and rename it if needed.

 

[Derek]

Thanks for this. I am getting an error on FileExists statement saying sub or function not defined .

something like this?

 

[moke123]

Are you running just what I posted or did you import it into your db?
If you imported it did you include the fileexist procedure?

 

[Pat Hartman]

Derek,
We can't see your code. It is hard enough to debug with it. Without it it is impossible.