vba 使用宏导入数据文件时，如何提示用户选择文件和工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5600533/
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
How do I prompt the user to select the file and sheet, when using macro to import a data file?
提问by Jon
I have a macro that is currently creates a new sheet, and imports another Excel file into this new sheet. Data from this sheet is then pulled into other areas of the workbook.
我有一个宏当前正在创建一个新工作表，并将另一个 Excel 文件导入到这个新工作表中。然后将来自该工作表的数据拉入工作簿的其他区域。
The file that is being imported has several tabs and will constantly have a different file name. How do I adjust the below code to prompt the user to select the file AND the appropriate tab? (The directory will not change.)
正在导入的文件有几个选项卡，并且将始终具有不同的文件名。如何调整以下代码以提示用户选择文件和适当的选项卡？（目录不会改变。）
I tried using the FileDialogobject, but it doesn't seem that Excel takes any action on the file selected. And, this does not allow you to choose the tab/sheet to import.
我尝试使用该FileDialog对象，但 Excel 似乎并未对所选文件执行任何操作。而且，这不允许您选择要导入的标签/工作表。
Sheets.Add
Sheets(2).Select
Sheets(2).Name = "ImportedDemand"
Range("E42").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=""""; _
User ID=Admin; _
Data Source=\Folder\ImportFile_2011.04.05.xls; _
Mode=Share Deny Write;Extended Properties=""HDR=YES;""; _
Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""; _
Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35; _
Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2; _
Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=""""; _
Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False; _
Jet OLEDB:Don't Copy Locale on Compact=False; _
Jet OLEDB:Compact Without Replica Repair=False; _
Jet OLEDB:SFP=False"), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("_All_Demand$")
.Name = "ImportFile_2011.04.05"
'Other Settings
.SourceDataFile = _
"\Folder\ImportFile_2011.04.05.xls"
.Refresh BackgroundQuery:=False
End With
回答by Jean-Fran?ois Corbett
Here's a way to return the name of a user-selected sheet:
这是一种返回用户选择的工作表名称的方法：
varCellContent = Application.InputBox _
(prompt:="Choose a sheet by clicking on any cell in it.", Type:=8)
strDestinationSheetName = ActiveSheet.Name
How to return the path of a user-selected file was already explained in answers to your previous question. With the FileDialogobject, or if you don't like it, with GetOpenFilename:
如何返回用户选择的文件的路径已在您上一个问题的答案中进行了说明。使用该FileDialog对象，或者如果您不喜欢它，请使用GetOpenFilename：
strPathOfFileToOpen = _
Application.GetOpenFilename("Excel workbooks (*.xls), *.xls")
Now FileDialogor GetOpenFilenamewill return the path of the file to open, e.g. "\\Folder\ImportFile_2011.04.05.xls", but they will notactually open the file. Youhave to use the returned path in the appropriate manner. From your question it isn't clear what this is, but I would guess:
现在FileDialogorGetOpenFilename将返回要打开的文件的路径，例如"\\Folder\ImportFile_2011.04.05.xls"，但他们实际上不会打开文件。您必须以适当的方式使用返回的路径。从你的问题中不清楚这是什么，但我猜想：
.SourceDataFile = strPathOfFileToOpen
and/or
和/或
Data Source=strPathOfFileToOpen ; _
I'm not quite sure why you don't have quotes around the latter in your question.
我不太确定为什么你在你的问题中没有引用后者。
Same logic with the sheet name: I've pointed out how to return it, but without more details I can't say how you should use it.
与工作表名称相同的逻辑：我已经指出了如何返回它，但没有更多细节我不能说你应该如何使用它。
回答by Jon49
From Excel 2002 VBA: Programmer's Reference:
来自Excel 2002 VBA：程序员参考：
dim fd as filedialog
dim ffs as filedialogfilters
dim stFileName as string
dim wkb as workbook
set fd=application.filedialog(msofiledialogopen)
with fd
set ffs=.filters
with ffs
.clear
.add "Excel", "*.xls" 'Or whatever version you are using.
end with
.allowmultiselect=false
if .show=false then exit sub
set wkb=getobject(.selecteditems(1))
end with
Here you can just use an input box to have the user write the name of the tab or you can launch the filedialog box from a userform (the better option).
在这里，您可以只使用输入框让用户输入选项卡的名称，或者您可以从用户表单启动文件对话框（更好的选择）。
Assuming you use a userform you can load the tab names into a drop down box and have the user select which tab it will be. Then do your code.
假设您使用用户表单，您可以将选项卡名称加载到下拉框中，并让用户选择它将是哪个选项卡。然后做你的代码。
Another alternative is loading the tabs into your main workbook and have the user select it from there.
另一种选择是将选项卡加载到您的主工作簿中，并让用户从那里选择它。
