7

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
  • 95,961
  • 11
  • 77
  • 135
Randal
  • 185
  • 2
  • 2
  • 6

1 Answers1

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!"
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • something as simple as an exclamation mark can make all the difference. Thanxs. – John Shaw Jul 01 '16 at 22:21
  • 1
    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. – Aris Aug 09 '17 at 22:47
  • Its now working m having same table structure as in excel but every time its created new table – Tanmay Nehete Feb 13 '18 at 09:18