Subscript Out of Range error when importing into Access 2007 from Excel.

I have been importing Excel files for years. I recently upgraded to Access 2007 on a shared server (Windows Server 2003 R2, Service Pack 2) and now I can't import. Every time I get "Subscript out of Range" when I use the Import Wizard.

The file is correct with the correct field names and data. I have tried importing into different tables and always get this error. Ican try an .xls or an .xlsx file and neither work.

I have to paste the records in.

Sponsored Links:

Related Topics

"Subscript out of range" Error
I am trying to import data from Excel 2007 to Access 2007. However, after hitting the final button to submit the import I get this message: "Subscript out of range."

I have tracked down the fields that seem to be causing the problem. Below is a picture of the Design View for my table. The fields named Ht, College, Dob, State, and Country are the fields that give me the subscript message (I know this because I tried uploading different times with just one of these fields mixed in with the fields that would upload).


subscript out of range
I am trying to import an excel file into an access table.

I have set up the fields and datatypes but when I try to import it it says subscript out of range which is not the most informative error message.


Subscript out of range for LBound function on a dyanmic array
I am new to the forum and this is my first post.

I am getting a Subscript out of range error in the following code:

Dim ad_aveGallonsPerDay() As Double
ReDim da_aveGallonsPerDay(2 To 12)
Dim testLB As Long, testUB As Long
testLB = LBound(ad_aveGallonsPerDay) ' error occurs on this line

What is more, when I look at the value of da_aveGallonsPerDay(3) or something else with a valid subscript, the watch window also tells me <Subscript out of range>.

Can someone help me figure out what I should be doing differently


Access 2010 issue
I'm testing in Access 2010 a database that was created in Access 2003. I came across an error that neither I nor the person that created the database understand. The error message is "subscript out of range".

At first we thought it was the extra three columns that were in the most recent monthly file that were not in the file that she tested. She had set up a template to import the data into and so we added the three columns that weren't there before.

Still got the error. Next I thought I should save it in Excel 2010 since the original file was in Excel 2003.

But upon saving the file in Excel 2010, there was another strange error message having to do with a saved name range. So, we deleted that but I keep getting the same message "subscript out of range".


Tried to export from excel to Access, but errors shows "Subscript out of range"
As mention above. I've tried to export the company's database from Excel into Access, however an pop-up message pops up saying "subscript out of range"

I've tried to google this problem before coming here, and some says that we need to convert it to .csv file. I've tried, it works well for the 1st tale, but not the second and so on.


Tried to export from excel to Access, but errors shows "Subscript out of range"
As mention above. I've tried to export the company's database from Excel into Access, however an pop-up message pops up saying "subscript out of range"

Anyone has any ideas?

I've tried to google this problem before coming here, and some says that we need to convert it to .cvs file. I've tried, it works well for the 1st tale, but not the second and so on


TransferSpreadsheet Error 3434 "Cannot expand named range"
Development in Access 2007 & Excel 2007
To be deployed on Access 2007 & Excel 2007 AND Access 2007 (runtime) & Excel 2003

This code runs fine when it is executed on Access 2007 and Excel 2007. However, it fails on Access 2007 RUNTIME and Excel 2003 "Error 3434 "Cannot expand named range""

I have defined a Named Range in the Template (Data!A1:AC60000). The Data tab is the basis for predefined calculations in the ensuing Excel file . Should I be transferring to "Data!A1" instead of a Named range?


Database Split - Error Subscript out of range
I have a database on the server that I need to have anyone access at the same time. I believe splitting the database is the way to do this.

When I split it I got a "Subscript out of range error" and then an "Invalid procedure or call argument" error.

When I open the FE database the tables with the data are there. The forms and queries are empty as they should be.

When I open the BE database the tables are there with data in them and there is no indication that the tables are linked.


Subscript out of range error
I am using a function to delete the "@" from address field. this function is also supposed to remove the unnecessary "0" from the beginning of a street address. I am this function in a simple query but this error "Subscript out of range"


Subscript out of range error..
I am trying to store the characters in a string on a array. the following is the code I have implemented. I am trying to store the characters of the hexPassword (eg. 7A06C861737365F) into the array key() like so key(0)=7, key(1)=A, key(2)=0. so on. but I keep encountring the Subscript out of range error at the below mentioned line. any idea why it is and how to get rid of the error?

For I = 1 To Len(hexPassword)
key(i - 1) = Mid(hexPassword, i, 1) 'line of error


Importing Excel 2007 spreadsheet into Access 2002
I have an Access 2002 database which a number of users use to import worksheets from excel. Most of my users are using excel 2003 but a few have migrated to excel 2007 to move beyond the row limitation. The GUI for my db uses the docmd.transferspreadsheet command to import the excel tables. I get an error when trying to import an excel 2007 table. Any comment on modifing the vba code to allow import of the excel 2007 data.


Database Splitter - Subscript out of range
I have mostly done very rudimentary uses of Access to date for my own isolated use. I have a database that my client would like to share across multiple users. We are having difficulty with table locking just having whomever open the shared file.

I have tried to use the "Database Splitter", but with no success. It creates a ". . .be.mdb" file, but before it is finished displays the "Subscript out of range" message.

I followed the instructions and copied the database file to my laptop to split it, but that gave the error.

I tried to split the database from the "shared drive" on the network. Same error.

What else can I do? What might be causing this error?


Accessing Excel Worksheet Data | Need alternative to TransferSpreadsheet
I'm working in Access 2007 and having a lot of trouble with the Excel import process, both manually using the import wizard and in my VBA code.

Using the import wizard or the TransferSpreadsheet command, I keep getting the following error: Method 'Execute TempImexSpec' of object '_Wizhook' failed. Despite this, the Excel worksheet imports, but with some cells dropped.

Nothing in the cells I could find seems to be causing the problem. I also tried CSV and tab delimited files, but I had the same issues (with even more dropped cells).

I Googled the error and found very few pages on it, MSDN came up with nothing at all.

I chalked this up to general Vista/Access 2007 issues and began looking for a VBA workaround, but am having trouble finding one that works.

As of now, I've decided to loop through the contents of the Excel file and read them into a two-dimensional array, figuring that this might be a way to avoid the TransferSpreadsheet errors I was encountering.

but I'm getting a 'subscript out of range error' while looping through the sheet.


Database Split - Error Subscript out of range
I am totally new to splitting databases. I have a database on the server that I need to have anyone access at the same time. I believe splitting the database is the way to do this.

When I split it I got a "Subscript out of range error" and then an "Invalid procedure or call argument" error.

When I open the FE database the tables with the data are there. The forms and queries are empty as they should be.

When I open the BE database the tables are there with data in them and there is no indication that the tables are linked.

Can anyone tell me what is going on and how it effects the databases


Run time Error 9:Subscript out of range
I am making a simple data sheet in excel. I have sheets which contain supplier's information. I have made a home page which summarizes all the supplier names with hyperlinks to each supplier sheet.

In the supplier sheet, I have set activex buttons: Previous Page, Home Page, Next Page.

Private Sub CommandButton1_Click()
Worksheets("NAFFCO").Activate
End Sub

Private Sub CommandButton2_Click()
Worksheets("Home").Activate
End Sub

Private Sub CommandButton3_Click()
Worksheets("SFFECO").Activate
End Sub

I get an error message "Run time Error 9: Subscript out of range" on some of the sheets. I have used the same vba codes for all the 15 sheets but 3 sheets are giving me headache.


Importing Excel Data Tables into Access
Trying to cut my teeth on Access for the first time,

My objective is to find and open an excel file(got that code) and then import the contents of 7 excel Data Tables into the 3 corresponding Access Tables.
I've been looking at TransferSpreadsheet command but it does not seem to recognize the Excel Tables as a legit named range. I get an runtime error 3011, can't find object. I've checked spelling and a few other things I could think of based off of user error.


Subscript Outside of Range
When importing an Excel spreadsheet into an Access table, everything between the spreadsheet and table MUST be the same. The field names in the Excel spreadsheet must match EXCACTLY the same in the Access table field names.

If one column in Excel is called "FirstName" but in Access the name is "First Name", the import will produce errors. If the field name in Excel is "Phone", but the name in Access is "Phone Number", the import will produce errors.

The formating of the data type in the two tables must be the same. If Phone is a number in Excel and text in Access, the import will produce errors.

It is a good idea to analyze the Excel spreadsheet's makeup before attempting to import it into Access. Upper case or lower case letters do not matter. Spaces are text and are importantwhen going between different applications.


Assigning variable to worksheets
I keep getting subscript out of range errors when I try to set my excel worksheets. Port is the name of the tab in excel and stPort is the name in my query....


Import from Excel help
I am having trouble importing from Excel 2007 to Access 2007.

I have verified manually there is no duplicate information, but even so duplication is allowed in the db except for the ID field. When I use the import pane everything looks good in theexample data to upload including the name headers.

I tried making sure all the field types were the same in each column as well as trying to import as general format only.

I have little experience in importing so the issue is probably me. Here is the error I receive and when I select continue = yes two new tables are created as below image and no files are imported. Should I add in ID field to the spreadsheet?


Importing only some columns of an excel file to access
I have an excel file that has 15 columns but I only need 4. I need to pull them into an Access table w. VBA.
I need
Column A: Client,
Column C: City, and
Column D: State.
The file will be of variable length.

My problem is that Column B is a phone number field that I don't want and what ever I do I can't avoid my database creating a import errors table for this field.
It contains a variety of numbers and spaces so I whether I have my table set to text or number they all error.
I don't care whether I import all the excel fields, or only the 3 I need, but I need some way to not error on column B.

Is it possible to import only certain columns? If so,
I would love the whole thing to be text and use import specs, but that's for TransferText and for excel you need to do TransferSpreadsheet.

but keep coming across people importing a single range.