Solved

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

Posted on 2014-01-25
6
540 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:versatilebb
  • 3
  • 2
6 Comments
 

Author Comment

by:versatilebb
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 49

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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:
versatilebb 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:versatilebb
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now