使用 VBA 宏对工作簿中的 Excel 表执行 SQL 查询 [英] Performing SQL queries on an Excel Table within a Workbook with VBA Macro

问题描述

我正在尝试制作一个 excel 宏，它将在 Excel 中为我提供以下功能:

I am trying to make an excel macro that will give me the following function in Excel:

=SQL("SELECT heading_1 FROM Table1 WHERE heading_2='foo'")

允许我使用 SQL 查询在我的工作簿表中搜索(甚至可能插入)数据.

Allowing me to search (and maybe even insert) data in my Workbook's Tables using SQL queries.

这是我目前所做的:

Sub SQL()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * FROM [Sheet1$A1:G3]"

rs.Open strSQL, cn

Debug.Print rs.GetString

End Sub

我的脚本就像一个带有硬编码范围的魅力，比如上面代码片段中的那个.它也适用于静态命名范围.

My script works like a charm with hardcoded ranges such as the one in the snippet above. It also works very well with static named ranges.

但是，它不适用于动态命名范围或对我来说最重要的 TABLE NAMES.

However, it won't work with either dynamic named ranges or TABLE NAMES which is the most important to me.

我找到的最接近的答案是这个人患有同样的痛苦:http://www.ozgrid.com/forum/showthread.php?t=72973

The closest I have found of an answer is this guy suffering from the same affliction: http://www.ozgrid.com/forum/showthread.php?t=72973

帮助任何人?

编辑

到目前为止我已经完成了这个，然后我可以在我的 SQL 查询中使用结果名称.限制是我需要知道表格在哪张纸上.我们可以做些什么吗?

I have cooked this so far, I can then use the resulting name in my SQL queries. The limitation is that I need to know on which sheet the tables are. Can we do something about that?

Function getAddress()

    myAddress = Replace(Sheets("Sheet1").Range("Table1").address, "$", "")
    myAddress = "[Sheet1$" & myAddress & "]"

    getAddress = myAddress

End Function

谢谢！

推荐答案

您可以做的一件事是获取动态命名范围的地址，并将其用作 SQL 字符串中的输入.类似的东西:

One thing you may be able to do is get the address of the dynamic named range, and use that as the input in your SQL string. Something like:

Sheets("shtName").range("namedRangeName").Address

哪个会吐出一个地址字符串，比如$A$1:$A$8

Which will spit out an address string, something like $A$1:$A$8

正如我在下面的评论中所说，您可以动态获取完整地址(包括工作表名称)并直接使用它或解析工作表名称以备后用:

As I said in my comment below, you can dynamically get the full address (including sheet name) and either use it directly or parse the sheet name for later use:

ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal

这会产生类似 =Sheet1!$C$1:$C$4 的字符串.因此，对于上面的代码示例，您的 SQL 语句可能是

Which results in a string like =Sheet1!$C$1:$C$4. So for your code example above, your SQL statement could be

strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)

strSQL = "SELECT * FROM [strRangeAddress]"

这篇关于使用 VBA 宏对工作簿中的 Excel 表执行 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！