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"

HansUp · Accepted Answer · 2016-01-20 04:25:03Z

16

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

edited Jan 20, 2016 at 4:25

answered Sep 13, 2011 at 16:57

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Shaw Over a year ago

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

Aris Over a year ago

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.

Tanmay Nehete Over a year ago

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

Collectives™ on Stack Overflow

Import an Excel worksheet into Access using VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related