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.