Missing last record when importing from Excel

[Guest]

Using Access 2002, I am importing data from Excel (which is a file created
from AS400) and have used various methods, i.e. linking to the excel file,
importing the excel file, and currently using the "Transfer Spreadsheet"
function in VB. With each method of importing the data, the last record of
the excel file is omitted. Using the Transfer spreadsheet function I get an
eror message saying that 1 record was deleted because either the fieldsize
property or the field data types do not match. Which is wierd because all
the data in each record is the same (as it came from AS400) and it only
deletes the last record. I have found that if I name the range of records in
excel before I transfer the data I can capture all of the records, but I
don't want to have to do this every time I do the import unless I can
automate that from Access.

Does anyone have an idea of what is going on with my spreadsheet import and
how I can fix it?

Thank you for your time and attention.

 

[Guest]

Is it possible that some fields have both text & numbers? If, for example, a
column (field) was a text field on the AS400 but just happened to have
numbers in that column except for the last row, like this:

12345987
456189
2131896
2ABC

then Access may interpret the field as numeric and reject the last item
because it is not numeric.

You can, by the way, automate the named range from Access. I would just
create a blank Excel sheet. Record a macro that opens the one with the data
and does the formatting/naming you need, then saves it and finally closes
itself. Make this an Auto_Open macro. Then, all you have to do from within
Access is shell out to Excel, passing the name of your blank Excel sheet as a
parameter, and it will do the external work for you automatically.

If you do this, make sure to put this action and the import on separate
buttons, or Excel will still be busy formatting the other sheet when Access
attempt to import it (Access will not wait for Excel to finish its work
before it goes to the next line of code, TransferSpreadsheet or whatever).

 

[Guest]

Yes, there is a field (a product code field) that could do that however, it
always omits the last row even when it is all numeric. Wierd.

I'm gonna try your suggestion on automating range naming from access.