unexpected results importing excel data

[Paul James]

I wrote a VBA procedure to import an excel table into Access using the
following code:

DoCmd.TransferSpreadsheet _
acImport, , _
"tblTMAimport", _
"\\sf_file\data1$\category1\processes\amtApplications.xls", _
True, _
"dataTable!A1:AP1000"

It works fine as long as the Excel spreadsheet is not open. If the Excel
file is open when you run this procedure, it only imports some of the
records, but not all of them. In addition, it produces an error message
that begins with:

"MS Access was unable to append all the data to the table.
The contents of fields in 0 record(s) were deleted, and 0 record(s) were
lost due to key violations."

Then the error messages goes on to tell you what might cause records being
deleted or lost, but of course this isn't relevant because it thinks 0
records were deleted or lost. In any event, those conditions don't exist in
this situation. Besides, it works just fine if the Excel file is closed.
So something is going on when the Excel file is open, that prevents the
DoCommand from importing all the records.

Does anyone know what might cause this?

Thanks in advance.

Paul