Problem importing Excel Spreadsheet

[Tony Wainwright]

Hi guys
I am writing an application in Access 2003 that takes data from an Excel
Spreadsheet that currently has 24,000 rows. Currently I am using the
TransferSpreadsheet argument of DoCmd to import the spreadsheet. However,
the Spreadsheet has around 800 errors, usually #DIV0! or DataType which are
put into an Errors table. What I want to do is, using VBA, read each row in
the Spreadsheet, as in a Do While Not rst.EOF loop and error trap from
within the application. Using the TransferSpreadsheet method I can't do
this. What I had thought about was using the TransferDatabase method and
linking to an ODBC datasource. Would this allow me to read the spreadsheet
1 row at a time? If not, is there anyway I can do this?

Thanks
Tony

 

[Frank Stone]

from my experiance, i have found that access doesn't seem
to like excel formulas. so i am sure you will have
problems reqardless what method you use. the problem seems
to be formulas.
suggestion 1. convert the excel formulas to hard numbers
before the import. may need to use excel macro to do this.
suggestion 2. Link to the excel file. link to the a named
range not the whole file. use the linked excel named range
as an access table.
P.S. if anyone does know how to import excel formulas,
please post. i have this problem too.