Currently my code is this:
Dim testSQL As String
Dim qd As DAO.QueryDef
testSQL = "SELECT * FROM qryExample WHERE exampleID IN (" & strExampleIDList & ")"
Set qd = db.CreateQueryDef("tmpExport", testSQL)
DoCmd.TransferText acExportDelim, , "tmpExport", "C:\export.csv"
db.QueryDefs.Delete "tmpExport"
How do I change the "C:\export.csv" part so that the user is able to define the file path and the file name?
Thanks.
Assuming you want the user to be prompted for input, and then use that input in your TransferText call, try this:
Dim UserInput As String
UserInput = InputBox("Please enter the file path.", "I WANT A VALUE!")
DoCmd.TransferText acExportDelim, , "tmpExport", UserInput
There are other approaches out there, but this is perhaps the easiest to implement.
Good luck.
This example will allow you to use the filedialog Save-As object:
To use this function, you must add a reference to the "Microsoft Office XX.0 Object Library". Add a new module and paste the following function:
Public Sub exportQuery(exportSQL As String)
Dim db As DAO.Database, qd As DAO.QueryDef
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogSaveAs)
Set db = CurrentDb
'Check to see if querydef exists
For i = 0 To (db.QueryDefs.Count - 1)
If db.QueryDefs(i).Name = "tmpExport" Then
db.QueryDefs.Delete ("tmpExport")
Exit For
End If
Next i
Set qd = db.CreateQueryDef("tmpExport", exportSQL)
'Set intial filename
fd.InitialFileName = "export_" & Format(Date, "mmddyyy") & ".csv"
If fd.show = True Then
If Format(fd.SelectedItems(1)) <> vbNullString Then
DoCmd.TransferText acExportDelim, , "tmpExport", fd.SelectedItems(1), False
End If
End If
'Cleanup
db.QueryDefs.Delete "tmpExport"
db.Close
Set db = Nothing
Set qd = Nothing
Set fd = Nothing
End Sub
Now within your code where you want to start the export, use: Call exportQuery("SELECT * FROM...")
I recommend defining a string variable for your SQL query.
Public Sub someButton_Click()
Dim queryStr as String
'Store Query Here:
queryStr = "SELECT * FROM..."
Call exportQuery(queryStr)
End Sub
