使用VBA将Excel工作表导入Access [英] Import an Excel worksheet into Access using VBA
问题描述
我正在尝试使用一些简单的VBA代码将Excel电子表格导入Access.我遇到的问题是Excel文件中有2个工作表,我需要导入第二个工作表.是否可以在VBA代码中指定所需的工作表?
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?
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
我应该将StrFileName设置为'StrFileName'&'.Worksheetname'
吗?那是正确的命名方案吗?
Should I set StrFileName to 'StrFileName'&'.Worksheetname'
? Is that the proper naming scheme for that?
类似:
StrFileName = StrFileName & ".WorkSheetName"
推荐答案
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.
此代码从名为"temp.xls"的工作簿中名为"temp"的工作表中导入,并将数据存储在名为"tblFromExcel"的表中.
This code imports from a sheet named "temp" in a workbook named "temp.xls", and stores the data in a table named "tblFromExcel".
Dim strXls As String
strXls = CurrentProject.Path & Chr(92) & "temp.xls"
DoCmd.TransferSpreadsheet acImport, , "tblFromExcel", _
strXls, True, "temp!"
这篇关于使用VBA将Excel工作表导入Access的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!