Tons of issues importing excel sheet for leads

[udfxrookie]

I have hundreds of excel sheets with leads in them, some with a few, some with over 150MB of data.
I have ensured that the table I created in Access labeled "Leads" has certain field names that match the field names of all the
excel sheets I am attempting to import.
The issue I'm facing is when I attempt to import the files some work some throw me error code 1, some throw error code 2.
Error code 1:
"Cannot open database ''.It may not be a database that your application recognizes, or the file may be corrupt"

Error code 2:
"Subscript out of range"

I've gone through ALL of the excel sheets and they all contain the same data, the ones importing, the ones not importing, the ones giving error1 and the ones giving error 2, I just don't get it.

This is mixed data types too:
phone_number, first_name, last_name, address1, address2, address3, city, state, postal_code, credit_rating, age, source_id, title, comments, dnc_flag, insurance provider, vehicle, dob, coverage, email

While some files contain all of the above information, some may only have half of those columns in the spreadsheets.
The successful imports vary just as much as the unsuccessful, some having all the info, some only portions.

Can anyone help.

[rpeare]

Are you still working on this problem?

[AnandKhinvasara]

I usually link to spreadsheets rather than import them. And once linked, I use a simple query to have them imported to a new table.
SELECT FROM LINKEDEXCELTABLE INTO ACCESSTABLE