Access Database Import Of Excel Worksheet Data..

Hi - I have been trying to use Patrick Mathews solution (posted a long time ago) for importing worksheet data to an Access table.  I have created the module in an Access database with the following code:

Sub GrabData()

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim LastRow As Long
Dim Addr As String
Dim Nam As Object
Dim Nams As Collection
Dim Counter As Long

Const SourcePath As String = "C:\Users\tom.farrar\Desktop\MergeSheets.xlsm" 'change as needed
Const DestTable As String = "Table1" 'change as needed

Set Nams = New Collection

With DoCmd
.SetWarnings False
.RunSQL "DELETE * FROM " & DestTable

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(SourcePath)

For Each xlWs In xlWb.Worksheets
LastRow = xlWs.[a65536].End(-4162).Row - 2
Counter = Counter + 1
On Error Resume Next
xlWb.Names("xxx" & Counter).Delete
On Error GoTo 0
Set Nam = xlWb.Names.Add(Name:="xxx" & Counter, RefersTo:="='" & xlWs.Name & "'!" & _
xlWs.Range(xlWs.Cells(2, 1), xlWs.Cells(LastRow, 4)).Address)
Nams.Add Nam.Name
Next

Set Nam = Nothing
Set xlWs = Nothing
xlWb.Save
xlWb.Close False
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

For Counter = 1 To Nams.Count
.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12, _
TableName:=DestTable, FileName:=SourcePath, HasFieldNames:=True, _
Range:=Nams(Counter)
Next

.SetWarnings True

End With

MsgBox "Done"

End Sub


I have only (at this point changed the folder source for the data and the table name in the Access DB.  See SourcePath and DestTable.  The module looks like it is gathering the data from the Excel spreadsheet, but I get this attached message when it begins the transfer of data to Access (I think).  

When using the debugging tool it shows the error occurring at:  

For Counter = 1 To Nams.Count
.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12, _
TableName:=DestTable, FileName:=SourcePath, HasFieldNames:=True, _
Range:=Nams(Counter)
Next

I am stumped at this point.  Any thoughts on where my error is?  Thanks.  -
Capture.PNG