Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-25
6
Medium Priority
?
611 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 53

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

610 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