VBA DoCmd.TransferText - exporting query to .csv with user defined file path

Question

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.

Answer 1

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.