Although not as common as exporting to Excel, some projects may have a need to export in .csv format. Here is my solution.
VBA Export With User Defined SQL Query
This example uses the QueryDef function. Basically, the user passes a SQL query in, the file dialog prompts for where to save-as and the export is completed.
'To use file-dialog, you must add a reference
'to Microsoft Office XX.X Object Library
'From the VBA code editor window
'Tools>References>Microsoft Office XX.00 Object Library
'''''''''''''''''''''''''''''''''''''''''
Public Sub exportQuery(exportSQL As String)
Dim db As DAO.Database, qd As DAO.QueryDef
Dim fd As FileDialog, iQdef As Variant
Set fd = Application.FileDialog(msoFileDialogSaveAs)
Set db = CurrentDb
'Check to see if querydef exists and delete to redefine
For Each iQdef In db.QueryDefs
If iQdef.Name = "tmpExport" Then
db.QueryDefs.Delete ("tmpExport")
End If
Next iQdef
Set qd = db.CreateQueryDef("tmpExport", exportSQL)
fd.InitialFileName = "export_" & Format(Date, "mmddyyyy") & ".csv"
If fd.Show = True Then
If Format(fd.SelectedItems(1)) <> vbNullString Then
DoCmd.TransferText acExportDelim, , "tmpExport", fd.SelectedItems(1), True
Else
End
End If
End If
'Cleanup
db.QueryDefs.Delete "tmpExport"
db.Close
Set db = Nothing
Set qd = Nothing
Set fd = Nothing
End Sub
How to Use Export Function
Call the above code in a button, other sub or where ever you have your call set up. Here is an example in a button:
Private Sub cmdTest_Click()
Dim iQuery As String
iQuery = "SELECT * FROM table"
'Call Sub to Complete the Export
Call exportQuery(iQuery)
End Sub
Miriam Hall says
This worked perfectly. Many thanks.