Sometimes the users face the error of subscript out of range in access. This error occurs when you trying to reference an index for a collection that is invalid. Most likely, the index in Windows doesn’t actually include .xls. The index for the windows should be as same as the name of the displayed workbook in the title bar of Excel. Well, there can be many other reasons for subscript out of range access import error.
Reasons Behind Subscript Out of Range Error
The possible reasons for this error are given below:
- In the Excel spreadsheet presence of an excessive number of columns.
- There can be some corruption issues that occurred in Excel files.
- Access is unable to translate the formatted or calculated Excel fields.
- While using the disabled Macros to access Excel.
These are the reasons responsible for the error. Now, let’s forward to the methods to resolve this issue.
Methods to Fix Error of Subscript Out of Range in Access
Subscript Out of Range Error is not a big issue that requires so much professional knowledge to fix. Generally, it occurs because of silly mistakes. Review all the given below checklist for fixing the error by making a few adjustment settings.
Method#1: Don’t Put Anything Over the Limit
Firstly, check out how many numbers of columns are present in your Excel spreadsheet? It’s necessary because MS Access Table can not consist of more than 255 number of files.
The best option to resolve this error or Subscript Out of Range Access import by reducing the unwanted column numbers. Only keep those columns that contain important data or the primary key.
The second step is that you can use is splitting the Access database
Method#2: Remove the Calculated Columns
If your Excel workbook consists of any calculated column and you are transferring it Access directly. Then it may create Subscript Out of Range Error. As it’s found that the Access database fails in transferring the formatted or calculated Excel fields.
So, for this, you have to copy the calculated column. After then only paste the calculated column’s value into a new column. At last, delete the calculated column.
Method#3: Make Your Access or Excel Error Free
Just make a quick move over complete your Excel spreadsheet, so that no error will found in your Excel workbook. It can help you to resolve the subscript out of range in access error.
It’s necessary to check because if you are importing an error in an Excel spreadsheet into Access, then it will harm your Excel database. As a result, you will get an error of subscript out of range in the access. So it’s better to go for a regular check.
The same rule of making the Access database error fee is also applicable if you are facing the error while splitting the Access database.
Method#4: Enable Your Disabled Macros
The steps are below for enabling and disabling Macros in all MS Office files.
When you open a file that shows the error of disabled macro then at the top bar, you will a yellow message bar along with the enable content button.
Here are the steps to enable it and fix the subscript out of range access import
- Firstly, tap on the file.
- Now, go to the security warnings section and click on the enable content.
- Select advanced options.
- A dialog box of MS Office security options will pop-up. From this, choose the option of enable content for this session, for each of the Macros.
- At last, click on OK.
Method#5: Professional Method
There is an alternative solution to fix the subscript out of range in access error. This contains a third-party tool that you can try. There is professional third-party software i.e, Access Database Recovery Tool. It can easily repair the subscript out of range import issue easily. It comes with some advanced features which are given below:
Features
- It can easily repair corrupt of MDB and ACCDB files.
- Also resolves the head corruption and the data misalignment issues.
- Have the tendency to restore, OLE, MEMO, & BLOB data of MS Access.
- Supports all Windows OS edition.
Wrap Up
If you have gone through the above post sincerely, then you have got a perfect idea to fix the error. Now, you can work according to these methods and can fix the subscript out of range in access error. As we have discussed the different methods. If we talk about the best, then you can opt for the Access Database Recovery Tool. This can easily fix this error without any consequences. The other methods contain many limitations and also consumes time. So choose the best for a better experience.
Related Post
- Remove From My Forums
-
Question
-
I’ve created two identical tables in two different databases. The one gets no subscript errors, the other does. I’ve triple checked that all the field names and properties are IDENTICAL. Are there any other factors that would be causing
the subscript error?
Answers
-
Hi W85,
Can you make sure that the following suggestions?
1. The data type in the Excel sheet columns correspond to the data types in the Access table
2. You may want to make sure Excel sheet has no hidden columns or rows. Others have ever encountered a such a problem with one Excel sheet that gave a «subscript out of
range» message.As for the problem, please try to highlight all columns and then select the command to «unhide columns» in the format menu. Although I had no hidden columns, this command
itself was sufficient for the Excel sheet to adjust itself and allow it to be imported into Access. It seems that Access is very sensitive to certain formatting situations in Excel that prevent Excel tables from being imported.Hope this can help you to resolve the problem.If your problem persists, just
let me know, I will try to help you.Best Regards,
Bruce Song [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
-
Marked as answer by
Friday, July 8, 2011 11:29 AM
-
Marked as answer by
Joe4
MrExcel MVP, Junior Admin
-
#2
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.
-
#3
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!
-
#4
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
-
#5
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.
-
#6
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.
-
#7
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…
-
#8
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
-
#9
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
Sometimes when we write and attempt to run sub-procedures in Excel VBA, Excel VBA returns a message box with the error message “Runtime error 9: Subscript out of range.”
The “Subscript out of range” error happens when we reference a non-existent collection member or a non-existent array element.
By the error, Excel VBA is telling us, “I have not found what you are looking for.”
This tutorial gives examples of specific causes of this error and how to fix it.
Before fixing the “Subscript out of range” error, you must identify its cause.
When you press the Debug button on the error message box, Excel VBA takes you to the statement that caused the error. By examining the line of code, you should be able to tell what caused the error. We outline some of the causes and solutions below.
Cause #1: Referencing a Non-Existent Item in a Collection
We can only access members of collections within their delimited ranges.
Therefore, attempting to access a collection member outside the defined scope will cause the “Subscription out of range” runtime error.
Suppose we have only Sheet1 and Sheet2 in our active workbook.
If we write the following sub-procedure and press F5 to run it:
We get the “Subscript out of range” error:
The error occurs because the Sheet3 object is not present in the Sheets collection, which consists of all the worksheets and chart sheets in the active workbook.
Therefore the code is referencing a non-existent entity in the Sheets collection.
How to Fix It
You can fix this error in any of the following ways:
- Create the non-existent object; in this case, add Sheet3 to the workbook.
- Check the spelling of the name of the collection member you want to access and ensure it is correct.
- In the Excel VBA code, refer to an object present in the collection; in this case, you can refer to either Sheet1 or Sheet2.
- Use the For Each…Next loop instead of referencing specific collection members. The loop allows us to loop through collection members and repeat particular actions, such as unhiding worksheets.
Also read: Not Enough Memory to Complete This Action in Excel – How to Fix?
Cause #2: Attempting to Access a Closed Workbook
If you try to access a closed workbook, you get the “Subscript out of range” error.
For example, if we have a closed workbook called “Employees” on our computer, running the following sub-procedure will generate the “Subscript out of range” error.
This error is generated because a closed workbook is not part of the collection of workbooks.
All the open workbooks on your computer make up the workbooks collection.
How to Fix It
You can use any of the following methods to fix the error.
- Open the workbook you want to access, then run the sub-procedure.
- Use the For Each…Next loop to check whether the workbook you wish to access is open. The loop goes through all the available workbooks and matches the name of the workbook you want to access against each open workbook. If a match is found, the workbook is open otherwise; the workbook is closed.
Cause #3: Referencing a Non-existent Array Element
If you reference a non-existent array element in your code, VBA displays the “Subscript out of range” to indicate something wrong with the code.
For example, the following code refers to an array element 11, which is not in the array declaration.
When we attempt to run the code, we get the “Subscript out of range” error.
How to Fix It
You can use any of the following techniques to solve the problem:
- Verify the upper and lower bounds of the array in the array declaration. If the dimensions are different from what you intended, adjust them accordingly.
- Ensure that you only refer to the array elements in the array declaration.
- Use LBound and UBound functions to direct the access of redimensioned arrays. The LBound function returns the lower boundary of the array, which can be either 0 or 1. The UBound function returns the upper limit of the array, which is equivalent to the number of items in the array.
- If the array dimensions are declared variables, ensure the variable names are spelled correctly.
Cause #4: Not Specifying the Number of Elements in an Array
If you declare an array but do not specify the number of elements in the array and attempt to access an element in the array, VBA returns the “Subscript out of range” error.
For example, the following code results in the error:
How to Fix It
- Explicitly specify the number of elements in the array in the array declaration.
Cause #5: Misspelling the name of a Workbook
If you misspell the name of the workbook you want to access, Excel VBA will return the “Subscript is out of range error.”
For example, if you want to activate a workbook named “Employees.xlsx,” the following code will not activate the workbook but return an error:
Although the workbook is open, the sub-procedure does not activate it because its name needs to be corrected.
How to Fix It
- Check the spelling of the workbook’s name in the sub-procedure and ensure it is correct.
Cause #6: Specifying an Invalid Element
Sometimes when you use the shorthand form of a subscript, you may mistakenly specify an invalid element.
For example, the shorthand for ActiveSheet.Range(“C3”) is [C3]. If this shorthand form of the subscript refers to an invalid element, you will get the “Subscript is out of range error.”
How to Fix It
Use a valid index or name for the collection.
This tutorial has given reasons and solutions for the “Subscript out of range” error. This error happens when we reference a non-existent collection member or a non-existent array element.
Examples of specific causes include
- Referencing a worksheet or workbook not present in the collection.
- Misspelling object names in the code.
- Referencing a non-existent array element.
- Not specifying the number of elements in the array.
- Attempting to access a closed workbook.
- Specifying an invalid element.
The solutions to the error include using the For Each…Next construct, instead of referencing specific items in the code and specifying the elements in an array.
We hope you found the tutorial helpful.
Other articles you may also like:
- Using Application.GetSaveAsFilename in VBA in Excel
- Using Application.EnableEvents in VBA in Excel
- SetFocus in Excel VBA – How to Use it?
- How to Open Excel Files Using VBA
- #NAME? Error in Excel – How to Fix!
- #NUM! Error in Excel – How to Fix it?
- SPILL Error in Excel – How to Fix?
I found a macro on the web to protect a worksheet with a password. It works fine, but when I save the file I get the message: run-time error ‘9’: subscription out of range. I have never programmed or used visual basic before and could use some help . Thank you
The macro is:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1:Protect the sheet with a password
Sheets("Sheet1").protect Password:="btfd"
'Step 2: Save the workbook
ActiveWorkbook.Save
End Sub
asked Feb 20, 2014 at 22:06
«Subscript out of range» indicates that you’ve tried to access an element from a collection that doesn’t exist. Is there a «Sheet1» in your workbook? If not, you’ll need to change that to the name of the worksheet you want to protect.
answered Feb 20, 2014 at 22:10
Why are you using a macro? Excel has Password Protection built-in. When you select File/Save As… there should be a Tools button by the Save button, click it then «General Options» where you can enter a «Password to Open» and a «Password to Modify».
answered Feb 20, 2014 at 22:21
Gordon BellGordon Bell
13.2k3 gold badges45 silver badges64 bronze badges
When you get the error message, you have the option to click on «Debug»: this will lead you to the line where the error occurred. The Dark Canuck seems to be right, and I guess the error occurs on the line:
Sheets("Sheet1").protect Password:="btfd"
because most probably the «Sheet1» does not exist. However, if you say «It works fine, but when I save the file I get the message: run-time error ‘9’: subscription out of range» it makes me think the error occurs on the second line:
ActiveWorkbook.Save
Could you please check this by pressing the Debug button first?
And most important, as Gordon Bell says, why are you using a macro to protect a workbook?
answered Feb 21, 2014 at 9:17
Matteo NNZMatteo NNZ
11.9k11 gold badges52 silver badges89 bronze badges