Exporting from Excel 2007

[Ronnie]

I am unable to export information from an Excel 2007 spreadsheet to an Access
2007 table. I am getting the following message: "Subscript Out Of Range".

Any ideas what this is telling me?

 

[Gina Whipp]

Ronnie,

Several reasons for this... Could be the Excel spreadsheet has too many
columns, could be there is some corruption in the Excel spreadsheet, could
be the way Access is translating your columns in Excel, could be there are
calculated fields in Excel that Access can't translate for what ever reason.

So going to need a wee bit more information...

1. How many columns is the spreadsheet?
2. Do you have any calculated columns?
3. Have you looked at the spreadsheet to confirm there are no errors on the
sheet?
4. Just on a whim here, do you column headings use any Reserved Words (or
Wildcard Characters) that might confuse Access?
5. Are you using the wizard or did you write some code? (If you wrote some
code can you post it here?)
6. Have you tried it manually? (You know, File... Import...)

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Ronnie]

1. Exact # of Columns with Exact column headings (also tried deleting headings)
2. No calculated columns, however, database does contain some combo box
columns - but there is no data in any of these fields on the spreadsheet.
3 & 4. Recheck the spreadsheet several times for errors - cannot find
anything wrong.
5. I am using the wizard.
6. Do not know how to import manually because I don't know where "File" is
on Acesss 2007. This would be my last resort before giving up! Exactly how
could I try this manually?

Thanks for your help!

Ronnie

 

[Gina Whipp]

Ronnie,

1. I was asking for a count not whether they matched. Do you have more
then 255 columns?
4. When you say nothing wrong, can you give me an example of some header
names... Are there any with the title Description or Name or Date, etc?
Are there any characters other than letters or numbers?
6. Sorry that would be the External Data tab (you did after all, say Access
2007). Try importing manually and let us know what happens...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Ronnie]

1. There are 26 columns in both the spreadsheet and the database.

4. Header names: FirstName; LastName; Address; City; State; Zip. Phone. The
format in the Phone column was set up in Access as follows: (000) 000-0000.
I changed the spreadsheet to removal all special characters and then I tried
changing the format to the phone format. This didn't help except that now the
message I am getting is that something in the format or field size is not
matching.

6. I am already importing from the External Data tab. Is there another way?

Ronnie

 

[Gina Whipp]

In your phone number field, try removing the formatting. Also you might
want to confirm your field lengths with that of Access. In Excel you really
have no control but in Access you might want to start with Text, 255 and
then check the lengths after import to see where the problem is. Chances
are there some bogus data somewhere....

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm