ACCESS 2007, "subscript out of range" what's it mean; how to corre

[Guest]

In ACCESS 2007, in trying to import from Excel, I get the message "subscript
out of range". what does it mean and how do I go about to correct it?

 

[John Nurick]

This is the first time I've heard of this with Access 2007. It's not
clear what it means in earlier versions but it seems to be associated
with some sort of problem with the worksheet or the data on it.

One thing to try is (in Excel) to copy the rectangular range of cells
you want to import, paste them into a new workbook and save it. Then try
importing from the new workbook.

Another is to save the data from Excel as a CSV text file, then import
from that into Access. If the import errors persist, this may at least
produce more information about them.

On Tue, 14 Nov 2006 15:54:01 -0800, apprentice idiot <apprentice

 

[Guest]

Pasting the data into a new workbook did not work. Hence I tried with another
list in Unicode text form, which did. I then attepted to introduce the
original data also via Unicode text, only to receive the message "Method
'ExecuteTempImexSpec' of object '_WizHook' failed. The same with other lists.
As this is a one-field table, when I try to import using fixed width, I get
the error message "No current record".
Pleae advise.

 

[John Nurick]

I've never come across that error message, and nor had Google until it
indexed your message. _WizHook is a hidden undocumented part of the
Access object model. _WizHook.ExecuteTempImexSpec doesn't seem to exist
in Access 2003, so I guess it's new to Access 2007. From its name I
guess it's used by the text import wizard. Have you tried importing the
data from a delimited rather than fixed width file.

If you're using a beta version of Access 2007 I suggest you wait until
the final version is released.

 

[Guest]

Clarification: the first message came when I imported from delimited files;
the second came from fixed width files.
I suppose I shall have to wait then for the final version.

 

[flyerman85]

I got the same error message when importing 2003 excel files, on the
one sheet I had I found when I opened it in excel 2007 one of the cells
has a corrupt name it is possible when it is opening it in
compatibility mode it messes something up. I fixed that one cell then
it imported it fine

 

[sirrobles]

If you are trying to import the data into an new table you need to select "Import the source data into a new table in the current database" which is the first selection in the get external data dialog box then select [OK]. Next the dialog box opens, select your worksheet then press [next]. Select "First row contains column headings" press [next], then press [next] again. You can choose to save the import steps. Click [OK] and this should work but only if you are trying to export your data into a new data table. Hope this helps.

 

[Eric Thorell]

I experience the same problem. My solution was to delete all commands, formulas, etc from the excel-sheet. Do so by copying the data, and then using "paste special" to paste it back as "text-only". This sorted out the problem with "Subscript out of range".

Best
/ Eric Thorell (Sweden, Lund University)

 

[mpinder]

I'm using Access 2007 and importing several excell sheets into the same database. I got the "subscript our of range" query from one particular sheet, couldn't see anything different with it.Pasting into a new sheet and formating the row height as 15, as I did for all the sheets before, and importing from that worked in my case.
mpinder

 

[TheCpd]

Since this is currently the top result on google for "access 2007 subscript out of range" I figured I would add in all possible solutions I've used to solve this issue in the past. Hopefully for those looking for help one of these techniques works:


-Format entire excel sheet as “general”
-Delete Extra Columns/Rows
-Unhide all columns
-Format number cells as numbers (correct all “number stored as text” errors)
-Make sure field types are aligned between excel and access (numbers are numbers, text is text, etc.)
-Copy whole excel table to a new Sheet before importing
-Copy whole excel table to a new Sheet (Paste Special: Values) before importing
-Save excel file as a text file (e.g. .csv) and import that instead

Good luck to all those with the same issue. I know how frustrating access can be and hopefully I can help someone.