当使用宏将数据文件导入新标签时，如何提示用户选择文件？ [英] How do I prompt the user to select the file, when using macro to import a data file into a new tab?

问题描述

我有一个宏当前正在创建一个新的工作表，并导入另一个excel文件到这个新的工作表。

此表格中的数据不会被拉入工作簿的其他区域。

正在导入的文件将不断有不同的文件名。如何调整以下代码提示用户选择文件？ （目录不会更改）。

Sub ImportDemand（）
Sheets.Add

<$ p $ （2）。选择
表（2）.Name =ImportedDemand
范围（E42）。选择
使用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 d_
，_
atabase =; Jet OLEDB：注册表路径=; Jet OLEDB：数据库密码=; Jet OLEDB：引擎类型= 35; Jet OLEDB：数据库锁定模式= 0;_
，_
全局部分批量操作= 2; Jet OLEDB：全局批量交易= 1; Jet OLEDB：新建数据库密码=; Jet OLEDB：创建Sys_
，_
tem Database = False ; Jet OLEDB：Encrypt Database = False; Jet OLEDB：Do not Copy Locale on Compact = False; Jet OLEDB：Compact Without Repli_
，ca Repair = False; Jet OLEDB：SFP = False）， Desti国家：=范围（A1）
.CommandType = xlCmdTable
.CommandText = Array（_ All_Demand $）
.Name =ImportFile_2011.04.05
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
\\Folder\ImportFile_2011.04.05.xls
.Refresh BackgroundQuery：= False
结束

End Sub

解决方案

您可以使用 GetOpenFilename ：

  .SourceDataFile = Application.GetOpenFilename（Excel工作簿（* .xls），* .xls ）
  

如果您使用Excel 2003， n我将使用FileDialog对象。它提供了更多的灵活性。

  Dim fdgOpen As FileDialog 
设置fdgOpen = Application.FileDialog（msoFileDialogOpen）
 fdgOpen.Title =请打开一个数据文件...
 fdgOpen.InitialFileName =C：\MyDocuments\MyDir\
'其他设置... 
 fdgOpen。显示
 .SourceDataFile = fdgOpen.SelectedItems（1）
  




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 than pulled into other areas of the workbook.

The file that is being imported will constantly have a different file name. How do I adjust the below code to prompt the user to select the file? (The directory will not change).

Sub ImportDemand() 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 d" _
    , _
    "atabase="""";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 Sys" _
    , _
    "tem Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Repli" _
    , "ca Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("A1"))
    .CommandType = xlCmdTable
    .CommandText = Array("_All_Demand$")
    .Name = "ImportFile_2011.04.05"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .SourceDataFile = _
    "\\Folder\ImportFile_2011.04.05.xls"
    .Refresh BackgroundQuery:=False
End With

End Sub

解决方案

You can use GetOpenFilename:

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

If you're using Excel 2003, then I would use the FileDialog object instead. It offers more flexibility.

Dim fdgOpen As FileDialog
Set fdgOpen = Application.FileDialog(msoFileDialogOpen)
fdgOpen.Title = "Please open a data file..."
fdgOpen.InitialFileName = "C:\MyDocuments\MyDir\"
'Other settings...
fdgOpen.Show
.SourceDataFile = fdgOpen.SelectedItems(1)

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