无法使用ADO在Excel工作表上查询动态命名的范围 [英] Unable to query dynamically named range on Excel sheet using ADO

问题描述

假设我有一个名为调整的Excel表格存储在一个名为 wksName 的变量中，在该表格上，我如果您查看名称管理器，则命名范围定义如下：

  tblData = OFFSET（Adjustments！$ A $ 1,0 ，0，COUNTA（调整！$ A：$ A），4）
  

我们在列A到D中有数据的行数很多。

现在我在VBA模块中有以下代码：

 设置ExcelCon = CreateObject（ADODB.Connection）
设置ExcelRecSet = CreateObject（ADODB.Recordset）
 
 ExcelCon.ConnectionString =Provider ='Microsoft.Jet.OLEDB.4.0'; &安培; _ 
Data Source ='& ThisWorkbook.FullName& ; &安培; _ 
扩展属性='Excel 8.0; HDR =是; IMEX = 1'
 
'获取更新的数据：
 ExcelCon.Open 
 
'从spreadhseet获取更新语句的数据
设置ExcelRecSet = ExcelCon.Execute（SELECT * FROM [& wksName&$ tblData]）
  

当我尝试运行此代码时，我收到错误：

 运行时错误'-2147217865（80040e37）'：
 Microsoft Jet数据库引擎找不到对象Sheet1 $ tblData。 
确保对象存在，并正确拼写其名称和路径名。 
  

这是使用ADO的限制 - 命名范围不能动态定义 - 还是有一种方式我可以让这个工作吗？

谢谢!!

解决方案

如评论所述，最终为我工作的代码（对于那些可能会发现你有同样问题的代码）是符合以下条件的：

 设置ExcelRecSet = ExcelCon.Execute（SELECT * FROM [& wksName&$& _ 
 wks.Range（tblData）。 0）&]）
  

这绝对有效，但如果有办法选择可变范围，无需如上所述，我很乐意看到这样做。

谢谢!!

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

解决方案

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

这篇关于无法使用ADO在Excel工作表上查询动态命名的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！