Better way than Transfer Spreadsheet 2

[JamieArvato]

I'm currently using the Transfer Spreadsheet in my code which imports data from 20 spreadsheets from a particular range in a particular sheet (all s/sheets) identical.

I'm getting Import errors though on type conversion as some fields have date & text in them.

My problem is, I can't change the spreadsheet as they are in constant use.

Is there a better way to get the data into access using VBA/SQL where I can validate on entry? The problems I'm having are;

#N/A in Excel
Date field and some are blanks
Date & text in the same field

Thanks

 

[PHV]

Import the spreadsheet into a temporary table with text fields.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

... in fact, you should NEVER import 'foregin' data directly into a 'working table'. All data entry should (manual keying or import) should have at least a rudimentry V&V before actually being used, When keyboard entry is used, you generally do get at least a minimal data type check as the 'add record' will fail. In many instances, you should 'go the extra inch' or so and check that entries are within some reasonableness range, such as a 'forecast date (e.g. "DateDue" is later than the current date or "DateStart", and that is is within some interval such as a month. Likewise, 'ammounts' can (and should) generally be checked to be within ranges.




MichaelRed