Importing Excel to Access...subscript out of range

[hbrod]

Hi all,

I am getting pretty fed up with all the hassle I am having just trying to import data from Excel 2007 to Access 2007 so I hope that someone is able to help!

All I want to do is import a spreadsheet from excel to access. I have checked that all the fields and headings and content are correct. i have actually imported this data previously but had to remove it and start again. However now when I use the import data from excel button and follow the wizard through it comes up saying 'subscript out of range'.
For a start I dont even know what this means and Access helpfully doesn't tell you. How do I find out, fix it and import data that happily went in before??
Help!

 

[Joe4]

Welcome to the Board!

Importing Excel files into Access can sometimes be a fickle thing, especially if you are importing to an existing Access table, or there are inconsistencies in your data.
Have a look here, and see if any of the situations mentioned here apply to you: Subscript Out of Range error when importing into Access 2007 from - Microsoft Community

Note that when Access imports Excel files, it generally looks at about the first ten records to determine the format of each field. If something changes after that, it could cause problems. Like let's say that you have a field that looks numeric, but down on line 100, there is an alpha character in there. That will cause problems.

I have spent hours on some of these imports before. As a last resort, I will export the Excel file to a text file format (CSV, tab-delimited, Fixed-Width, etc), and then import that into Access. The advantage there is you control the Date Types for all the fields you are importing, and it tends to work a little better.

 

[jclubbe]

I have had the same problem. I tried copying all my Excel data and pasting it as values only into a new worksheet and then made it into a table. That still did not work. Then tried it with CSV as well. Kept returning an error that Field 65 does not exist in my table! Went back to my Excel spreadsheet (to the values sheet) and re-sized all my columns. Saved it and tried importing again into Access. Hehe! Only got "Unparsable Record" errors, but still imported all the data. Well Done Microsoft!! LOL!

 

[Crocdundee]

yes I have had this problem on many occasions, as I import daily many csvs
Personally I think that a macro formatting all columns is the way to go and also make sure that column names are the same
However I have only today just discovered that saving as a "Tab Delimited" csv is the way to go
I also have Vba code to delete Import error tables after the import takes place
Regards
Graham

 

[brelee]

yes I have had this problem on many occasions, as I import daily many csvs
Personally I think that a macro formatting all columns is the way to go and also make sure that column names are the same
However I have only today just discovered that saving as a "Tab Delimited" csv is the way to go
I also have Vba code to delete Import error tables after the import takes place
Regards
Graham

Hi there - I just had the same problem and it turned out that I had an Input Mask for my PostCode field in .accdb and the Excel field wasn't matching the input mask. I removed the input mask and it imported! Hope that helps someone.

 

[amiami53]

Whenever I run into an issue, I import the file into Access as a new table, compare field value types and formats to the existing table. This helps me solve for my import issues.

 

[ecoman007]

Today, for the first time, I received the 'subscript out of range' error while performing an import I have been doing every week for over a year now. Out of frustration, I closed & re-opened Access, tried the import again and was successful. Hope this helps some...

 

[MichaelpEynon]

Today, for the first time, I received the 'subscript out of range' error while performing an import I have been doing every week for over a year now. Out of frustration, I closed & re-opened Access, tried the import again and was successful. Hope this helps some...

Worked like a charm. thank you

 

[SYSCO_RIVERSIDE320]

Hi all,

I am getting pretty fed up with all the hassle I am having just trying to import data from Excel 2007 to Access 2007 so I hope that someone is able to help!

All I want to do is import a spreadsheet from excel to access. I have checked that all the fields and headings and content are correct. i have actually imported this data previously but had to remove it and start again. However now when I use the import data from excel button and follow the wizard through it comes up saying 'subscript out of range'.
For a start I dont even know what this means and Access helpfully doesn't tell you. How do I find out, fix it and import data that happily went in before??
Help!

\

Try importing data as its own table then perform append query to the table you want. It worked for me