vbaexceladojet

Unable to query dynamically named range on Excel sheet using ADO

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!!

Solution

  • 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!!