Subscript Out of Range error when importing into Access 2007 from Excel.
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.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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).
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.
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".
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.
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?
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?
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.
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.
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?