Adding new records to Excel named range via ADODB does not expand named range automatically

Posted on 2014-01-25
Medium Priority
Last Modified: 2014-02-01
I've used this method frequently, but today for some reason it is not working:  

From within Word, I am using VBA to access an Excel file via ADODB.   SavedSystems in my code below is an Excel NAMED RANGE.  This routine adds records to the worksheet, and usually the named range expands automatically when I use the objrecset.AddNew.  That's important so that next time I read in the SavedSystems named range those newly added records are included.  

This exact code has worked many times for me before.   Now it is not.  The new rows are saved just after the named range, but the named range does NOT expand to include them.

What am I missing?

DataFile = Environ("AppData") & "\Microsoft\Word\SavedSystems.xlsx"
TableName = "SavedSystems"

Set objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=0"";"  
Set objRecSet = New ADODB.Recordset

    objRecSet.Open TableName, objConnection, adOpenKeyset, adLockOptimistic  

    For myrow = 0 To NewForm.ListBox3.ListCount - 1
        objRecSet.Fields(0) = Me.TextBox1.Text
        For mycol = 0 To 4
            objRecSet.Fields(mycol + 1) = NewForm.ListBox3.List(myrow, mycol)
        Next mycol
    Next myrow

' CLEANUP--------------------------------------------
Set objRecSet = Nothing
Set objConnection = Nothing

Open in new window

Question by:Bryce Bassett
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Author Comment

by:Bryce Bassett
ID: 39809218
One thing that has changed since this was working is I installed a trial of Office 2013, so I assume it's using Excel 2013 rather than 2010 as before.  Could that have anything to do with this?  

LVL 27

Expert Comment

ID: 39809773
How do you define the dynamic named range?
LVL 52

Expert Comment

ID: 39809776

Have you tested whether the supplementary rows are part of the table (named range) in the XL file?

Is it a named range or a table because named range do not expand automatically when you enter more data.

to have a dynamic named range you could define your named range like this


Open in new window

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 27

Expert Comment

ID: 39809886
You may also define your named range like this:

Accepted Solution

Bryce Bassett earned 0 total points
ID: 39814114
Thanks both for your replies.   After doing some more research, I confirmed from several sources that you cannot access dynamic named ranges in a closed Excel workbook.  You can only address static named ranges.  

Of the various workarounds suggested, the easiest (assuming you have only one range on the sheet) it to select the entire worksheet.  SQL will recognize the boundaries of the range from whatever is on the sheet.  This works like a charm.

SELECT * FROM [Sheet1$];

Author Closing Comment

by:Bryce Bassett
ID: 39826133
I don't feel I can accept the solutions offered because, while they did provide some suggestions for creating a dynamic named range, they missed my more important need to address that range through ADODB.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question