Import an Excel worksheet into Access using VBA

Question

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   Should I set StrFileName to  StrFileName  amp   Worksheetname    Is that the proper naming scheme for that   something like   StrFileName   StrFileName  amp    WorkSheetName

User · Answer

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.

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!"

[ms-access] Import an Excel worksheet into Access using VBA

Examples related to ms-access

Examples related to vba

Examples related to ms-access-2007