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'&'.Worksheetname' ? Is that the proper naming scheme for that?

something like:

StrFileName = StrFileName & ".WorkSheetName"

Stradivarius

Answer 1

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

Vigorous

something as simple as an exclamation mark can make all the difference. Thanxs. – Sadden

You must note that, if the target table already exists in the Access database and has the same structure, the imported data will be appended to it. – Panache

Its now working m having same table structure as in excel but every time its created new table – Yellowhammer