Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Location
    Richmond, VA
    Posts
    38

    Unanswered: Cannot Expand Named Range

    This is very humbling, but perhaps someone can learn from this.

    I want to export a query to a named range in an excel spreadsheet. I've done this tons of times, but not lately. Here's what happens.

    I export once and bam it slams into the spreadsheet just the way I want it to. Yes, I prepped the spreadsheed with the valid field names needed.

    Next I repeat the process to make sure I can do this repeatedly and BAM, no go. I get the dreaded "Cannot Expand Named Range". I open the spreadsheet and a section of my data is erased along with the headers. I restore the spreadsheet headers, erase most of the earlier data and BAM I'm back in business only to bump into the same error again the next time I try this.

    Sorry for the colorful language, but I really feel like BAMMING something after stubbing my toe on this over and over.

    Here's a clue. My original names range only goes to row 11. These are the rows that get erased each time. Trial and error is not working for me. I'll pick up your suggestions in the morning.

    Thanks in advance.

    Paul Hammond
    Richmond, VA

  2. #2
    Join Date
    Aug 2011
    Location
    Richmond, VA
    Posts
    38

    Please Help

    It's getting lonely down here at the bottom of the list. Help if you can.

    Thanks.

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I think this is more of an excel problem tbh.

    Access, to the best of my knowledge won't be able to export into Excel, and change some of Excel's settings... I imagine as it's all created with VB that technically it would be possible, but not worth the trouble.

    I'm not really an Excel expert, but as far as I know, Excel won't automatically expand its named range. So if you have, "My_Range" as cells: B2:B12, it will only hold those 11 values, and nothing more.

    You would have to redefine the cell boundaries to accommodate for the oversized collection.

    You can write a simple script in Excel to do this I'm sure, but as I said, this probably belongs in the Excel forum where you'll find help writing that script.

    If I've totally misunderstood you, then please let me know.
    Looking for the perfect beer...

  4. #4
    Join Date
    Aug 2011
    Location
    Richmond, VA
    Posts
    38
    This is kind of a hybrid Access/Excel issue since I am working from Access. I am considering just linking directly to my query from Excel and skipping the export procedure. That actually may be the better solution. I am confused as to why the procedure works in the 1st instance, but not the 2nd. I'll let this percolate here and see if anything turns up. If not I'll pop over to an excel forum and see what they got for me.

    Thanks for the reply.

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I imagine it works the first time as it sets the upper boundry at that point, then on the subsequent update, it can't expand beyond that size.

    I'm pretty sure it's giving you a kind of 'visual' Array, which has a static (unchanging) number of spaces in it. Arrays need to be told explicitly that they need to be made larger, otherwise they cannot store anymore data... Having never seen the source code for Excel I can't say for sure, but I would imagine that's how it's working at a programming level.

    If you expand the size manually before the second import to cover the increased number of records, does it still get errors? I would suspect not...
    Looking for the perfect beer...

  6. #6
    Join Date
    Aug 2011
    Location
    Richmond, VA
    Posts
    38
    OK, I've experimented some more. Here's a few factw.

    I expanded my named range in Excel to include the number of rows in the query, which was 50. I ran the export multiple times without error, surprise! I do receive a warning that object already exists and do I want to replace it, to which I answer Yes.

    I then added 10 more rows to my export and ran through it several more times and received no error message, BUT only 50 rows loaded into my spreadsheet. I still receive the same warning and respond the same way.

    I played a bit more and this is what I concluded.

    1. If I make my destination range large enough, it will always populate.
    2. As long as my output never shrinks the export will replace existing data.
    3. If my output is smaller, any remaining data in the existing rows will remain.
    4. This method will not work for me.


    Linking to Access from Excel appears simpler. As long as I select the correct options, i.e. "Refresh data when opening file.", my data always stays current. As I can't find a downside in this, this is how I will proceed.

    The experience from these growing pains is almost always beneficial, but the process can be grueling. It's like going back to elementary school all over again. Thanks for allowing me to work this out in public. I hope to be able to help you someday soon.

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    That's what forums are for!

    Feel free to post back whenever you need to vent or whatever.

    You could set up a bit of code linked to a button... When you click the button an input box appears, asking you how many rows you are going to import, which would then resize the number of cells referenced by the named range.

    But if you've found a different solution that works for you, that's great!

    All's well that ends well.
    Looking for the perfect beer...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •