使用 VBA 将 Excel 工作表导入 Access
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7405261/
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
Import an Excel worksheet into Access using VBA
提问by Randal
I am attempting to import an Excel spreadsheet into Access using some simple VBA code. The issue I have run into is there are 2 worksheets in the Excel file, and I need the 2nd worksheet to be imported. Is it possible to specify the needed worksheet in the VBA code?
我正在尝试使用一些简单的 VBA 代码将 Excel 电子表格导入 Access。我遇到的问题是 Excel 文件中有 2 个工作表，我需要导入第二个工作表。是否可以在 VBA 代码中指定所需的工作表？
Private Sub Command0_Click()
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls", 1
.Filters.Add "All Files", "*.*", 2
If .Show = -1 Then
StrFileName = .SelectedItems(1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "COR Daily", StrFileName, True
Else
Exit Sub
End If
End With
End Sub
Should I set StrFileName to 'StrFileName'&'.Worksheetname'? Is that the proper naming scheme for that?
我应该将 StrFileName 设置为'StrFileName'&'.Worksheetname'吗？这是正确的命名方案吗？
something like:
就像是：
StrFileName = StrFileName & ".WorkSheetName"
回答by HansUp
Pass the sheet name with the Range parameter of the DoCmd.TransferSpreadsheet Method. See the box titled "Worksheets in the Range Parameter" near the bottom of that page.
使用DoCmd.TransferSpreadsheet Method的 Range 参数传递工作表名称。请参阅该页面底部附近标题为“范围参数中的工作表”的框。
This code imports from a sheet named "temp" in a workbook named "temp.xls", and stores the data in a table named "tblFromExcel".
此代码从名为“temp.xls”的工作簿中名为“temp”的工作表导入，并将数据存储在名为“tblFromExcel”的表中。
Dim strXls As String
strXls = CurrentProject.Path & Chr(92) & "temp.xls"
DoCmd.TransferSpreadsheet acImport, , "tblFromExcel", _
strXls, True, "temp!"
