Xl to Access Error code 2391

[Stuart]

Error says Field 'F1' does not exist in destination table
'Drainage & Utilities'.

Here's what I'm doing:

In Excel VBA I make an ADO connection and then I create
a new mdb. To the database I then add a 'template' table
copied from an Excel workbook. The template includes
necessary formatting and Field names. The table is named as the
ActiveSheet (ie ws.Name).

Here's the code at this particular point:
For Each ws In ActiveWorkbook.Worksheets
With ws
.Select
StartRw = 2
EndRw = .Range("N65536").End(xlUp).Row
With CurrentDb
'Import the template table and name it as the sheet name
DoCmd.TransferDatabase acImport, "Microsoft Access", _
strTemplatePath, acTable, _
"Master Excel Template", ws.Name, True, False
this works fine.
I then define some variables and call the data import sub:
strTable = ws.Name
strFileName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
strWorkSheet = ActiveSheet.Name
strRange = "A" & StartRw & ":" & "N" & EndRw
Set dbs = appAccess.CurrentDb
Call ImportSpreadsheet(strTable, strFileName, _
strWorkSheet, strRange, dbs)
which passes to:
Public Sub ImportSpreadsheet(strTable As String, _
strFileName As String, strWorkSheet As String, _
strRange As String, dbs As Object)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
strTable, strFileName, , strWorkSheet & "!" & strRange

and it's this line throwing the error.

The Field names should already be in the table, since it was copied
from the Excel template 'table', hence why I start the data copy from
row 2. I had hoped this method would be more efficient than to
import Excel data one row at a time.

Can anyone educate me please?

Regards.

 

[onedaywhen]

This approach may be overly complicated. You can create the table and
data at the same time with one query using Jet's SELECT..INTO syntax
e.g. while connected to the .mdb database, execute this SQL:

SELECT *
INTO MyNewTable
FROM
[Excel 8.0;database=C:\MyPath\MyClosedWorkbook.xls;].[MyWorksheet$]