ADO:Excel:是否可以在表名上打开记录集? [英] ADO: Excel: Is it possible to open recordset on table name?

问题描述

我在网上看过，但没有找到答案.所以我把它放在这里.

I have looked on the web but did not find any answer. So i am putting it here.

代码段:(在情况1无效的情况下.)

Code snippet: ( where case 1 is not working.. )

_bstr_t    strCnn(L"Provider='Microsoft.ACE.OLEDB.12.0';DataSource=C:\\Book.xlsx;Extended Properties=\"Excel 12.0 Xml; HDR = YES\"");

hr = pConn->Open(strCnn, "", "", adConnectUnspecified); // success

**LPCSTR strQuery = "select * FROM Table1";          // case1: Not working**

LPCSTR strQuery = "select * FROM [Sheet1$]";       // case2: working

LPCSTR strQuery = "select * FROM [Sheet1$A1:D10]"; // case3: working

hr = pRst->Open(CComVariant(strQuery), _variant_t((IDispatch *)pConn, true),
                adOpenStatic, adLockOptimistic,adCmdText); // fails for case 1

这在excel中可能吗?或上面的代码有问题.

Is this possible in excel? or there is something wrong in the above code.

注意:我已经尝试使用Microsoft Access进行此操作，并且将recorset绑定到表名在这里可以正常工作.

NOTE: I have tried this with Microsoft Access and binding recorset to table name works fine there.

推荐答案

简称为:

插入到Excel电子表格中的表"在SQL中不可见.

您需要一个命名范围或命名工作表，或表"数据范围和标题的完整地址；并且与Excel表相关联的命名范围的明显存在是令人误解的.

You need a named range, or a named worksheet, or the full address of the 'table' data range and headers; and the apparent existence of a named range associated with an Excel table is misleading.

我想这需要一些解释，首先是:

I guess that needs some explanation, starting with:

表1"到底是什么?

这不是命名范围，也不是工作表，并且您的SQL无法将其解析为Excel Range对象.概括一下:

It's not a named range, it's not a sheet, and it cannot be resolved to an Excel Range object by your SQL. To recap that:

• 如果您创建了一个在工作簿中全局可见的命名范围， 并将其命名为"Table1"，对于您的SQL查询来说，它的显示方式为 表1 .
• 如果您已将工作表重命名为"Table1"，则对 您的SQL查询为 Table1 $ .
• 如果您创建了一个命名范围，则该范围是工作表范围的局部 名为"Sheet1"，并将其命名为"Table1"， SQL为 Sheet1 $ Table1 .

• If you had created a named range, globally visible in the workbook, and called it 'Table1', it would be visible to your SQL query as Table1.
• If you had renamed the worksheet to 'Table1', it would be visible to your SQL query as Table1$.
• If you had created a named range, local to the scope of a worksheet named 'Sheet1', and named it 'Table1', it would be visible to your SQL as Sheet1$Table1.

Excel表"不是这些内容，因此它对于数据库引擎而言是不可见的.

An Excel 'Table' is none of those things, so it isn't visible as a table to the database engine.

是的，它显示在名称管理器"对话框中以显示命名范围:但是表1"不是命名范围的名称，也不是工作表，也没有指定具有可识别为SQL的地址的范围:它必须是这三件事之一，才能在ExcelB的OLEDB和ODBC数据库驱动程序的数据库表中可见.

Yes, it shows up in the 'Name Manager' dialog for named ranges: but 'Table1' is not the name of a named range, nor a worksheet, nor does it specify a range with an address recognisable to SQL: and it needs to be one of those three things in order to be visible as a database table to the OLEDB and ODBC database drivers available for Excel.

您已创建为"Table1"的对象是 ListObject ，您可以在Excel VBA IDE的调试窗口中查询它，如下所示:

The object you've created as 'Table1' is a ListObject, and you can interrogate it in the Excel VBA IDE's debug window as:

Thisworkbook.Worksheets("Sheet1").ListObjects("Table1").Name

所以您需要的是ListObject范围的地址，这很简单...

So what you need is the address of the ListObject's range, and that's a simple matter of...

实际上，这根本不简单.

Actually, it isn't simple at all.

... ListObject具有 DataBodyRange 属性，该属性覆盖标题下的数据行；和 HeaderRowRange 引用标题.两者都是范围，都有地址；并且您需要 UNION 来获得一个地址，您的SQL可以将其作为命名表读取.

...The ListObject has a DataBodyRange property, which covers the rows of data below the header; and a HeaderRowRange which refers to the headers. Both, being ranges, have an address; and you'd need to UNION them to get a single address that your SQL can read as a named table.

或者使用 Sheet1.ListObjects("Table1").Sort.Rng.address ，因为每个ListObject都具有 Sort 对象属性，无论它是否已排序；并且 Rng 属性为 entire 范围，无需考虑一致的对象名称，也没有提示要排序的列或列的地址.

Or use Sheet1.ListObjects("Table1").Sort.Rng.address because every ListObject has a Sort object property, whether it's sorted or not; and the Rng property is the entire range, with a cavalier disregard for consistent object names and no hint as to the column or columns' address being sorted.

...而且这些信息对您的问题和代码毫无用处:您无法将任何可将"Table"对象解析为有效表的SQL查询放入

...And that information is useless for your question and your coding: there's nothing you can put into an SQL query that will parse the 'Table' object as a valid table.

在运行SQL之前，您必须提取并解析整个表"范围的地址. ADO和ODBC Excel驱动程序将在格式为' SheetName $ A1:Z1024 '的连续范围地址上工作，但是它们无法从Excel'Table'对象的父工作簿中提取该地址.

You have to extract and parse the address of the full 'table' range before you run the SQL. The ADO and ODBC Excel drivers will work on a contiguous range address of the form 'SheetName$A1:Z1024' but they have no ability to extract that address from the parent workbook of the Excel 'Table' object.

对于令人失望的答案表示歉意.

Apologies for the disappointing answer.

顺便说一句，您知道知道用可用的ODBC和OLEDB SQL驱动程序的 any 查询Excel会导致内存泄漏吗?至少，如果您从VBA中这样做，它也会这样做:我建议您不要重复运行此程序，除非您在代码上运行Purify或类似工具并找出问题所在.

By the way, you do know that querying Excel with any of the available ODBC and OLEDB SQL drivers gives you a memory leak? At least, it does if you do it from VBA: I would advise you not to run this repeatedly unless you run Purify or a similar tool on your code and isolate the problem.

这篇关于ADO:Excel:是否可以在表名上打开记录集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！