[VBA] Access Export Data

Long time since my last pos with VBA, so time for a little refreshment.

Table in Acces ready to export

Let’s create simple procedure with export methods from Acces to dedicated path:

Sub ExportFiles()

Dim ExpFilePath As String
Dim ExpFileName As String

On Error GoTo EmptySpace

ExpFilePath = "C:\Users\Agnieszka\Desktop\ExpFiles\"
ExpFileName = "Content" & Format(Now(), _
"_ddmmyyyy_hhmmss")

'SPACE FOR EXPORT METHODS

EmptySpace:
If Err.Number = 2302 Then 
MsgBox "Catalog does not exist!", _
vbInformation

End Sub
  1. Export to Excel
  2. Export to Txt file with encoding method
  3. Export to CSV (with sepcified method)
'EXCEL FILE
DoCmd.OutputTo acOutputTable, "PRODUCTS", acFormatXLSX, _
ExpFilePath & ExpFileName & ".xlsx", False

'TXT FILE
DoCmd.OutputTo acOutputTable, "PRODUCTS", acFormatTXT, _
ExpFilePath & ExpFileName & ".txt", False, , 65001

'CSV FILE
DoCmd.TransferText acExportDelim, "PRODUCTS_Query_Spec", _
"PRODUCTS", ExpFilePath & ExpFileName & ".csv", True

To specify the method we should create at first manually export in our current Acces database, go to Advanced and name it:

Full code:

Sub ExportFiles()

Dim ExpFilePath As String
Dim ExpFileName As String

On Error GoTo EmptySpace

ExpFilePath = "C:\Users\Agnieszka\Desktop\ExpFiles\"
ExpFileName = "Content" & Format(Now(), _
"_ddmmyyyy_hhmmss")

'EXCEL FILE
DoCmd.OutputTo acOutputTable, "PRODUCTS", acFormatXLSX, _
ExpFilePath & ExpFileName & ".xlsx", False
'TXT FILE
DoCmd.OutputTo acOutputTable, "PRODUCTS", acFormatTXT, _
ExpFilePath & ExpFileName & ".txt", False, , 65001

'CSV FILE
DoCmd.TransferText acExportDelim, "PRODUCTS_Query_Spec", _
"PRODUCTS", ExpFilePath & ExpFileName & ".csv", True

EmptySpace:
If Err.Number = 2302 Then
MsgBox "Catalog ExpFile does not exist!", _
vbInformation

End Sub

Result: