# Solved: Subscript out of range error without using macro!



## dctdatabase (May 11, 2009)

Hi there
Something for Excel specialists out there. Am trying to import Excel data into Access (using 2007 versions but all files saved as 2000 format) and getting 'subscript out of range' error.
I looked around for similar problems and found it mostly occurs when running macros, which I am not. I have tried renaming the Excel file to something shorter and without space, also making the active cell on saving the last one in the worksheet (as suggested somewhere) but no luck.
Any help welcome!!


----------



## slurpee55 (Oct 20, 2004)

Do you have any dynamic arrays in your Excel file?


----------



## dctdatabase (May 11, 2009)

What are dynamic arrays slurpee55?? I guess if I don't know what these are the answer is probably no..
The workbook I'm trying to import data from is based on another original one (ie this one has been amended and renamed) from which I managed to import data last week. Only change is that same data has been separated onto different worksheets based on certain criteria (using autofilter function to identify ranges). Would this have anything to do with it?


----------



## slurpee55 (Oct 20, 2004)

I think so - the autofilter would interfere with Access properly importing the file. I would instead import the whole file into Access and then build a series of queries that filter the data table in the same manner that you have worked with autofilter.


----------



## dctdatabase (May 11, 2009)

Hi again
That sounds quite interesting and it seems you were right about the autofilter. I tried copying the data and pasting values only into another workbook (this process highlighted the issue with autofilter in that some of the hidden/excluded records had been carried over) but still came up with same error when importing.
Would you know a way of removing the 'ill effect' of this autofilter any other way? Problem is I have cleansed data since filtering it and going back to original data would mean re-cleansing thousands of records (many hours of work)!...
I thought that a last resort remedy may be to scan the data with an OCR scanner so the values alone can be read and 'imported' into MS Office (probably Word or Excel). That would give me a clean sheet again and I could import into Access from this??
Thanks (and fingers crossed)


----------



## slurpee55 (Oct 20, 2004)

Oh, in Excel, copy the data, open a new worksheet, go to Paste Special. Put a check mark in the box "Skip Blanks." There you go.
As for the OCR - I wouldn't bet 10 cents on that working....


----------



## dctdatabase (May 11, 2009)

Hi Slurpee55
Success at last!! Finally worked out what the problem was and, after all that, it was a very basic thing. Nothing to do with the spreadsheet at all but simply that I was trying to import the data directly into an existing Access table rather than into a new one (and then create an append query based on that). I'd done it several times before but somehow managed to overlook this lately...
Still you'd think that importing into an existing table should work when you've made sure all the fields match (same name, order and number and same data type)! Maybe something for Microsoft to look into?
Thanks for your patience and suggestions anyway. I sure learnt something along the way.


----------



## slurpee55 (Oct 20, 2004)

Bizarre...thanks, though, I learned something today!


----------

