Solved

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

Posted on 2014-01-25
6
578 Views
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"";"  
objConnection.Open
Set objRecSet = New ADODB.Recordset

    objRecSet.Open TableName, objConnection, adOpenKeyset, adLockOptimistic  

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

objRecSet.UpdateBatch
     
cleanup:
' CLEANUP--------------------------------------------
objRecSet.Close
objConnection.Close
Set objRecSet = Nothing
Set objConnection = Nothing

Open in new window

0
Comment
Question by:Bryce Bassett
[X]
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
6 Comments
 

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?  

Thanks!
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39809773
How do you define the dynamic named range?
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39809776
Hi,

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

=OFFSET($A$1,0,0,COUNTA($A:$A),4)

Open in new window

Regards
0
Industry Leaders: 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

by:MacroShadow
ID: 39809886
You may also define your named range like this:
=$A$1:INDEX($A$1:$A$1000,SUMPRODUCT(--($A$1:$A$1000<>"")))
0
 

Accepted Solution

by:
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$];
0
 

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

728 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