What does subscript out of range mean?

Discussion in 'Microsoft Access External Data' started by Guest, Mar 21, 2006.

  1. Guest

    Guest Guest

    When I'm importing an Excel spreadsheet into an Access DB the import wizard
    gives me a dialog box stating that the subscript is out of range. I am
    importing the table to be used in a report that has already been created.
    I've tried importing the spreadsheet to a new DB and get the same error. I'm
    working with Office 2003.
     
    Guest, Mar 21, 2006
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    I was having same problem trying to import from Excel. I posted a request
    for help but did not get any responses. In the meantime, I deleted all empty
    columns and rows (they were empty cells I deleted, but the worksheet
    previously had content in cells out to last column of the worksheet). I then
    had no problem importing the worksheet into Access. I think that once cells
    in the worksheet have content or have formatting applied to them, even if
    later deleted, Excel (or Access) looks at those cells as part of the import.
    Deleting the empty columns and/or rows restores them to a "pristine"
    condition (I'm guessing). You might also try copying the active cells to a
    new worksheet. Hope this helps.



    "Joe Ramos" wrote:

    > When I'm importing an Excel spreadsheet into an Access DB the import wizard
    > gives me a dialog box stating that the subscript is out of range. I am
    > importing the table to be used in a report that has already been created.
    > I've tried importing the spreadsheet to a new DB and get the same error. I'm
    > working with Office 2003.
     
    Guest, Mar 22, 2006
    #2
    1. Advertisements

  3. Guest

    gbothma

    Joined:
    Feb 18, 2009
    Messages:
    1
    Likes Received:
    0
    Hi there

    My database contained an Auto Number field in the first column. I had to remove the columns from the Excel spreadsheet completely after which the import worked fine.

    The Excel spreadsheet is not allowed to contain any references to the associated Auto Number column in the DB.
     
    gbothma, Feb 18, 2009
    #3
  4. "Joe Ramos" wrote:

    > When I'm importing an Excel spreadsheet into an Access DB the import wizard
    > gives me a dialog box stating that the subscript is out of range. I am
    > importing the table to be used in a report that has already been created.
    > I've tried importing the spreadsheet to a new DB and get the same error. I'm
    > working with Office 2003.
     
    Francis of Harvey, May 8, 2009
    #4
  5. I also had this message when importing from Excel into an Access DB and later
    solved the problem. The trouble was that there was more than one type of data
    in one of the columns of the Excel spreadsheet. There were both text fields
    and date fields in the same Excel column and Access would not accept this.
    When I removed the date fields, the Excel data was imported as normal. Try to
    check your spreadsheet to make sure that the data type in each column matches
    with the data type format in the Access DB columns and I think that this will
    solve your problem. Hope this helps you.

    "Joe Ramos" wrote:

    > When I'm importing an Excel spreadsheet into an Access DB the import wizard
    > gives me a dialog box stating that the subscript is out of range. I am
    > importing the table to be used in a report that has already been created.
    > I've tried importing the spreadsheet to a new DB and get the same error. I'm
    > working with Office 2003.
     
    Francis of Harvey, May 8, 2009
    #5
  6. In addition to making sure that the data types in the Excel sheet columns
    correspond to the data types in the Access table, you may want to make sure
    that the Ecel sheet has no hidden columns or rows. I encountered a persistent
    problem with one Excel sheet that gave a ""subcript out of range" message.
    The only way this sheet would import was for me 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. Whatever the reason, this action
    finally allowed the import to take place. Hope this info helps you.

    "Joe Ramos" wrote:

    > When I'm importing an Excel spreadsheet into an Access DB the import wizard
    > gives me a dialog box stating that the subscript is out of range. I am
    > importing the table to be used in a report that has already been created.
    > I've tried importing the spreadsheet to a new DB and get the same error. I'm
    > working with Office 2003.
     
    Francis of Harvey, May 28, 2009
    #6
  7. In addition to making sure that the data types in the Excel sheet columns
    correspond to the data types in the Access table, you may want to make sure
    that the Ecel sheet has no hidden columns or rows. I encountered a persistent
    problem with one Excel sheet that gave a ""subcript out of range" message.
    The only way this sheet would import was for me 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. Whatever the reason, this action
    finally allowed the import to take place. Hope this info helps you.

    "Joe Ramos" wrote:

    > When I'm importing an Excel spreadsheet into an Access DB the import wizard
    > gives me a dialog box stating that the subscript is out of range. I am
    > importing the table to be used in a report that has already been created.
    > I've tried importing the spreadsheet to a new DB and get the same error. I'm
    > working with Office 2003.
     
    Francis of Harvey, May 28, 2009
    #7
  8. Guest

    Helen Guest

    having spent half a working day on this and doing all the usual things re
    data types, named ranges etc etc, the only way round it I can find it to
    import the spreadsheet into a new table rather than appending it to an
    existing one, and then taking the existing data from the Access table to the
    newly created one. A real pain but at least it worked. Only trouble is - if
    you have existing queries, reports etc they may have to be re-done.

    "Francis of Harvey" wrote:

    > In addition to making sure that the data types in the Excel sheet columns
    > correspond to the data types in the Access table, you may want to make sure
    > that the Ecel sheet has no hidden columns or rows. I encountered a persistent
    > problem with one Excel sheet that gave a ""subcript out of range" message.
    > The only way this sheet would import was for me 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. Whatever the reason, this action
    > finally allowed the import to take place. Hope this info helps you.
    >
    > "Joe Ramos" wrote:
    >
    > > When I'm importing an Excel spreadsheet into an Access DB the import wizard
    > > gives me a dialog box stating that the subscript is out of range. I am
    > > importing the table to be used in a report that has already been created.
    > > I've tried importing the spreadsheet to a new DB and get the same error. I'm
    > > working with Office 2003.
     
    Helen, Jun 2, 2009
    #8
  9. Like others here I spent hours looking for a way to get around this error.
    When I figured it out, i felt like kicking myself. You know how when you
    start Access there is that "Security Warning - certain content blah blah
    disabled" message is there below the ribbon? Just click options and select
    "allow this content". The data came in after that! I couldn't believe it -
    Access was denying access to Access...

    I know this is too late for you Joe, but hopefully it will help others in
    the future.

    Cheers,
    -sw

    "Joe Ramos" wrote:

    > When I'm importing an Excel spreadsheet into an Access DB the import wizard
    > gives me a dialog box stating that the subscript is out of range. I am
    > importing the table to be used in a report that has already been created.
    > I've tried importing the spreadsheet to a new DB and get the same error. I'm
    > working with Office 2003.
     
    sponge.worthy, Aug 10, 2009
    #9
  10. Guest

    i.need.help Guest

    Hallelujah! I had tried all of the other helpful hints but nothing worked
    until your suggestion. BRILLIANT! Thanks so much!

    "sponge.worthy" wrote:

    > Like others here I spent hours looking for a way to get around this error.
    > When I figured it out, i felt like kicking myself. You know how when you
    > start Access there is that "Security Warning - certain content blah blah
    > disabled" message is there below the ribbon? Just click options and select
    > "allow this content". The data came in after that! I couldn't believe it -
    > Access was denying access to Access...
    >
    > I know this is too late for you Joe, but hopefully it will help others in
    > the future.
    >
    > Cheers,
    > -sw
    >
    > "Joe Ramos" wrote:
    >
    > > When I'm importing an Excel spreadsheet into an Access DB the import wizard
    > > gives me a dialog box stating that the subscript is out of range. I am
    > > importing the table to be used in a report that has already been created.
    > > I've tried importing the spreadsheet to a new DB and get the same error. I'm
    > > working with Office 2003.
     
    i.need.help, Aug 20, 2009
    #10
  11. Guest

    Virginia K Guest

    I tried all of the things that were suggested and ended up just closing
    Access and coming back in. I still don't know what the problem was.

    "Joe Ramos" wrote:

    > When I'm importing an Excel spreadsheet into an Access DB the import wizard
    > gives me a dialog box stating that the subscript is out of range. I am
    > importing the table to be used in a report that has already been created.
    > I've tried importing the spreadsheet to a new DB and get the same error. I'm
    > working with Office 2003.
     
    Virginia K, Jan 13, 2010
    #11
  12. Guest

    Gina Whipp Guest

    Virginia,

    Unfortunately, I don't know what all those things are because I only see
    your message... So pardon me if some of the below has already been
    suggested.

    Several reasons for this... Could be the Excel spreadsheet has too many
    columns, could be there is some corruption in the Excel spreadsheet, could
    be the way Access is translating your columns in Excel, could be there are
    calculated fields in Excel that Access can't translate for what ever reason.

    So going to need a little more information...

    1. How many columns is the spreadsheet?
    2. Do you have any calculated columns?
    3. Have you looked at the spreadsheet to confirm there are no errors on the
    sheet?
    4. Just on a whim here, do you column headings use any Reserved Words (or
    Wildcard characters) that might confuse Access?
    5. Are you using the wizard or did you write some code? (If you wrote some
    code can you post it here?)
    6. Have you tried it manually? (You know, File... Import...)

    --
    Gina Whipp
    2010 Microsoft MVP (Access)

    "I feel I have been denied critical, need to know, information!" - Tremors
    II

    http://www.regina-whipp.com/index_files/TipList.htm

    "Virginia K" <Virginia > wrote in message
    news:...
    >I tried all of the things that were suggested and ended up just closing
    > Access and coming back in. I still don't know what the problem was.
    >
    > "Joe Ramos" wrote:
    >
    >> When I'm importing an Excel spreadsheet into an Access DB the import
    >> wizard
    >> gives me a dialog box stating that the subscript is out of range. I am
    >> importing the table to be used in a report that has already been created.
    >> I've tried importing the spreadsheet to a new DB and get the same error.
    >> I'm
    >> working with Office 2003.
     
    Gina Whipp, Jan 13, 2010
    #12
  13. Guest

    msdwin

    Joined:
    Aug 15, 2010
    Messages:
    1
    Likes Received:
    0
    Thank You, Thank You, Thank You

    You are such an awesome person. I am currently at work running reports to be our financial reporting season and I tried everything everyone said and was just about to give up when I read comment. I did it and works just fine. This is my first time working with appending and I was ready to pull my hair out.. I removed the security deal and it imported with no problems.


    Thanks and Bless You,

    Ms. Dwin


     
    msdwin, Aug 15, 2010
    #13
  14. Guest

    ucitare

    Joined:
    Feb 21, 2011
    Messages:
    1
    Likes Received:
    0
    Had the same problem; I import the Excel file into Access as a separate table and then do an append query to the table I want updated and no problem. Couple of things to make sure of-
    field names are the same and I like to have those fields indexed with no duplicates so that
    only "fresh" records are added. Hope this helps.
     
    ucitare, Feb 21, 2011
    #14
  15. Guest

    pep296

    Joined:
    Mar 1, 2011
    Messages:
    1
    Likes Received:
    0
    This gave me another idea: I did a compact and repair and now it works !!!:fool:

    "sponge.worthy" wrote:

    > Like others here I spent hours looking for a way to get around this error.
    > When I figured it out, i felt like kicking myself. You know how when you
    > start Access there is that "Security Warning - certain content blah blah
    > disabled" message is there below the ribbon? Just click options and select
    > "allow this content". The data came in after that! I couldn't believe it -
    > Access was denying access to Access...
    >
    > I know this is too late for you Joe, but hopefully it will help others in
    > the future.
    >
    > Cheers,
    > -sw
    >
    > "Joe Ramos" wrote:
    >
    > > When I'm importing an Excel spreadsheet into an Access DB the import wizard
    > > gives me a dialog box stating that the subscript is out of range. I am
    > > importing the table to be used in a report that has already been created.
    > > I've tried importing the spreadsheet to a new DB and get the same error. I'm
    > > working with Office 2003.
    [/QUOTE]
     
    pep296, Mar 1, 2011
    #15
  16. Guest

    Analyst Adrian

    Joined:
    Aug 11, 2011
    Messages:
    1
    Likes Received:
    0
    Location:
    UK
    Thanks gbothma - it was the autonumber column problem for me - I'd left it blank assuming Access would complete it.

    Regards
    Adrian

     
    Analyst Adrian, Aug 11, 2011
    #16
  17. Guest

    rinam

    Joined:
    Oct 8, 2011
    Messages:
    1
    Likes Received:
    0
    Hi Guys
    I tried all these but still having same problem.please any other recommendation.
    Rinam
     
    rinam, Oct 8, 2011
    #17
  18. Guest

    Ezgikar

    Joined:
    Oct 20, 2011
    Messages:
    1
    Likes Received:
    0
    Hi,
    I have the same problem but I try to import a text file instead of excel. I read all the info here but nothing applies to my problem.

    While I am trying to import a text file to Access with "append a copy of records to the table" option, it gives a "subscript out of range" error. I was able to import it many times in access 2007 or earlier versions, but after I updated office to 2010 version, it started to give that error. What can be the reason of it? My text file doesn't have column headings, the table in which I want to import my text file has column headings and they are not reserved words.
    I can import the text file if I don't choose the "append a copy of records to the table" option.
    Please help!
     
    Ezgikar, Oct 20, 2011
    #18
  19. Guest

    lollie009

    Joined:
    Dec 9, 2011
    Messages:
    1
    Likes Received:
    0
    Unfreezing panes fixed the problem for me :thumb:
     
    lollie009, Dec 9, 2011
    #19
  20. Guest

    cobolforever

    Joined:
    Feb 9, 2012
    Messages:
    1
    Likes Received:
    0
    Tried all the above. Decided to log out and back into Access. Import worked fine. Go figure.
     
    cobolforever, Feb 9, 2012
    #20
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike

    "Subscript Out of Range" error messge

    Mike, Jan 8, 2004, in forum: Microsoft Access External Data
    Replies:
    0
    Views:
    289
  2. Dev Kumar

    Export error "Subscript out of range"

    Dev Kumar, Mar 1, 2004, in forum: Microsoft Access External Data
    Replies:
    0
    Views:
    327
    Dev Kumar
    Mar 1, 2004
  3. Guest

    What does 'subscript out of range' mean when linking a table in Ac

    Guest, Feb 23, 2006, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    275
    Guest
    Mar 7, 2006
  4. Guest

    ACCESS 2007, "subscript out of range" what's it mean; how to corre

    Guest, Nov 14, 2006, in forum: Microsoft Access External Data
    Replies:
    9
    Views:
    33,292
    TheCpd
    Feb 1, 2012
  5. Guest

    What does it mean when it say subscript out of range

    Guest, Aug 20, 2007, in forum: Microsoft Access External Data
    Replies:
    8
    Views:
    803
    saima
    Nov 1, 2007
Loading...

Share This Page