Solved

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

Posted on 2014-01-25
6
504 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 26

Expert Comment

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

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 26

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

708 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

13 Experts available now in Live!

Get 1:1 Help Now