使用宏导入数据文件时，如何提示用户选择文件和工作表？ [英] How do I prompt the user to select the file and sheet, when using macro to import a data file?

问题描述

我有一个当前正在创建新工作表的宏，并将另一个Excel文件导入到这个新工作表中。然后将此工作表中的数据拉入工作簿的其他区域。

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.

正在导入的文件有多个选项卡，并且将始终具有不同的文件名。如何调整以下代码以提示用户选择文件和相应的选项卡？ （目录不会改变。）

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.)

我尝试使用 FileDialog 对象，但它似乎不是Excel对所选文件执行任何操作。而且，这不允许您选择要导入的标签/表格。

I tried using the FileDialog object, 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.

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

推荐答案

这是一种返回用户选择工作表名称的方法：

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

如何返回用户选择文件的路径已在上一个问题。使用 FileDialog 对象，或者如果您不喜欢它，使用 GetOpenFilename ：

How to return the path of a user-selected file was already explained in answers to your previous question. With the FileDialog object, or if you don't like it, with GetOpenFilename:

strPathOfFileToOpen = _
    Application.GetOpenFilename("Excel workbooks (*.xls), *.xls")

现在 FileDialog 或 GetOpenFilename 将返回要打开的文件的路径，例如\\Folder\ImportFile_2011.04.05.xls，但他们不实际打开该文件。 您必须以适当的方式使用返回的路径。从你的问题不清楚这是什么，但我猜：

Now FileDialog or GetOpenFilename will return the path of the file to open, e.g. "\\Folder\ImportFile_2011.04.05.xls", but they will not actually open the file. You have to use the returned path in the appropriate manner. From your question it isn't clear what this is, but I would guess:

.SourceDataFile = strPathOfFileToOpen 

和/或

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.

这篇关于使用宏导入数据文件时，如何提示用户选择文件和工作表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！