Results 1 to 5 of 5
  1. #1
    radicrains is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    29

    Unhappy Tried to export from excel to Access, but errors shows "Subscript out of range"


    hi everyone.

    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..

    any ideas how i can overcome this?

    thanks in adv..

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    most likely it has nothing to do with a file extension.

    the times that I've seen people post about this message, the cause is related to the default field types that Access uses when it reads the data coming in. I can't remember what Access does, but I remember people saying that it looks at the first 3-5 rows of data to determine a proper type for assignment to the field.

    I'd bet a dollar that's the problem here as well. Take a look at your maximum numbers in Excel, in your numeric columns. For example, a column that starts like this:

    • 39
    • 540
    • 38
    • 20
    • 400


    but ends like this:

    • 20039372
    • 2999028374
    • 34
    • 3


    would most likely throw the same error. Not 100% sure, but I would bet. Other data types may be a problem too. I think I remember alphanumeric mixtures also cause problems.

  3. #3
    radicrains is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    29
    Hi,

    I am actually aware that MS Excel has direct formulas that do not work on MS Access. Hence, before i exported the file, I have copy column by column of the data i need and "paste special" on a new workbook...

    This paste special allows me to choose to paste the "value and format" that has appearred in the box, w/o the formulas and etc. Hence it is as good as a "fast data entry" method..

    That was what i did.. if there's other ideas, please state, I'm willing to experiment...


    thanks in adv..

  4. #4
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Try saving the xls file as a csv file and then importing the csv file. Otherwise I'd suspect that you've got a bad value somewhere in the excel spreadsheet (or a lingering value at the bottom of the excel spreadsheet somewhere and when MSAccess imports it, it's expecting a different kind of value (ie. you've got date values in a column but one of the dates is 12/33 or something like that versus 12/01/2010.) If you copied/pasted all the columns, a value somewhere at the bottom of the excel spreadsheet might also have gotten pasted.

    Keep in mind that when MSAccess imports an xls file, it tries to interpret what the field type is for the xls column based on the first row of values it sees. So if your first row of data for the first xls column is something like 123, MSAccess may think this is supposed to be a number type. If it sees something like 12/22/2010, it will think it's supposed to be a date field type. As ajetrumpet stated, values that might be buried elsewhere in the excel column could cause a problem based on what the MSAccess import interpreted that column's field type should be.

    I'd look at the values on the spreadsheet and see if any of them look like they would cause mis-intepretation when importing. You could also design an Import Specification in MSAccess and then import the csv file to make sure the field types are setup correctly when importing.

    If you get this kind of error when importing 'any' xls file (even a simple one with only a couple rows of data), then check your MDAC which has the driver for excel importing.

  5. #5
    pacittk is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Posts
    3
    Thank you so very much! This was the problem. Some of my formatting had changed and after I streightened out all the formatting my record finally came and appended the old record! Everything has to match!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. "Subscript out of range" Error
    By yes sir in forum Access
    Replies: 21
    Last Post: 08-16-2012, 08:02 PM
  2. File Export "sort" issues
    By jgelpi16 in forum Programming
    Replies: 24
    Last Post: 09-15-2010, 12:14 PM
  3. aSTR = Dir("C:\*.*") >> "Type Mismatch"
    By JGrant in forum Programming
    Replies: 1
    Last Post: 03-28-2009, 05:17 AM
  4. Export to .rtf of "104-" converts to "-655&qu
    By Sherri726 in forum Import/Export Data
    Replies: 0
    Last Post: 12-19-2006, 03:16 PM
  5. "Count" and "Countif" in Reports
    By JMantei in forum Reports
    Replies: 1
    Last Post: 06-20-2006, 02:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums