Unable to query dynamically named range on Excel sheet using ADO

Question

Suppose I have an Excel Sheet named Adjustments stored in a variable called wksName and, on that sheet, I have a named range defined as follows if you look in the Name Manager:

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

So that it expands to as many rows as we have data in columns A thru D.

Now I have the following code in my VBA module:

Set ExcelCon = CreateObject("ADODB.Connection")
Set ExcelRecSet = CreateObject("ADODB.Recordset")

ExcelCon.ConnectionString = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
                            "Data Source='" & ThisWorkbook.FullName & "';" & _
                            "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"

'Get the data for the update:
ExcelCon.Open

' Get the data from the spreadhseet for the update statement
Set ExcelRecSet = ExcelCon.Execute("SELECT * FROM [" & wksName & "$tblData]")

When I try and run this code, I get the error:

Run-time error '-2147217865 (80040e37)':
The Microsoft Jet database engine could not find the object 'Sheet1$tblData'.
Make sure the object exists and that you spell its name and the path name correctly.

Is this a limitation to using ADO - Named ranges can not be dynamically defined - Or is there a way I can get this to work?

Thanks!!

Try this... `Set ExcelRecSet = ExcelCon.Execute("SELECT * FROM tblData")` — Siddharth Rout, Feb 03 '14 at 22:34
or something like `Set ExcelRecSet = ExcelCon.Execute("SELECT * FROM [" & wksName & "$" & tblData.Address & "]")` — Siddharth Rout, Feb 03 '14 at 22:38
Thanks, @SiddharthRout, I did end up getting it to work with `Set ExcelRecSet = ExcelCon.Execute("SELECT * FROM [" & wksName & "$" & wks.Range("tblData").Address(0, 0) & "]")`, but I'd really like to know if there is a nicer way.... — John Bustos, Feb 04 '14 at 13:59
Sadly, no - I even tried to make the range `tblData` scope to the workbook rather than the sheet, but still no luck... — John Bustos, Feb 04 '14 at 14:30

John Bustos · Accepted Answer · 2014-03-25T14:41:09.010

0

As stated in the comments, the code that ended up working for me (for those of you that may find you have the same issue) was something along the lines of:

Set ExcelRecSet = ExcelCon.Execute("SELECT * FROM [" & wksName & "$" & _
                                    wks.Range("tblData").Address(0, 0) & "]")

This definitely works, but if there was a way to select a variable range without having to reference it as above, I'd love to see the way to do that.

Thanks!!

edited Mar 25 '14 at 14:41

answered Feb 19 '14 at 14:35

John Bustos

19,036
17
89
151

what is `wks` in this? is it one of `schemaRow`s in `schemaTable.Rows` which `connection.GetOleDbSchemaTable()` returns? – Mehrad Oct 27 '14 at 23:44
@Mehrad, no, it would be the regular sheet, like `Sheet1`. – John Bustos Oct 28 '14 at 13:16
Just to clarify, do I need a `new Microsoft.Office.Interop.Excel.Application()` to access the `Sheet1` you mentioned? cuz as far as I know `GetOleDbSchemaTable()` won't give you a `Range` property for the sheet which it returns in the sheets (called `Tables`). Oh, I just read the question again at it seems the OP didn't even use the `OleDbConnection` and I must have missed it in the heat of the moment since the starting of the question was exactly is the same as the issue I have. (and also there is a select) – Mehrad Oct 28 '14 at 22:51
@Mehrad, it seems you have a completely different problem... Read my question and the solution posted - My solution solves my question, but it seems you are looking for something else... Maybe post it as a separate question I and others could try and help you solve.... – John Bustos Oct 29 '14 at 13:01

Unable to query dynamically named range on Excel sheet using ADO

1 Answers1

Linked