VBA DoCmd.TransferText - 使用用户定义的文件路径将查询导出到 .csv
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14700192/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA DoCmd.TransferText - exporting query to .csv with user defined file path
提问by user1844098
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?
如何更改“C:\export.csv”部分以便用户能够定义文件路径和文件名？
Thanks.
谢谢。
回答by sgeddes
Assuming you want the user to be prompted for input, and then use that input in your TransferText call, try this:
假设您希望提示用户输入，然后在 TransferText 调用中使用该输入，请尝试以下操作：
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.
祝你好运。
回答by Ryan
This example will allow you to use the filedialog Save-As object:
此示例将允许您使用 filedialog Save-As 对象：
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:
要使用此功能，您必须添加对“Microsoft Office XX.0 对象库”的引用。添加一个新模块并粘贴以下函数：
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...")
现在在您要开始导出的代码中，使用： Call exportQuery("SELECT * FROM...")
I recommend defining a string variable for your SQL query.
我建议为您的 SQL 查询定义一个字符串变量。
Public Sub someButton_Click()
Dim queryStr as String
'Store Query Here:
queryStr = "SELECT * FROM..."
Call exportQuery(queryStr)
End Sub
