在具有VBA宏的Workbook中的Excel表上执行SQL查询 [英] Performing SQL queries on an Excel Table within a Workbook with VBA Macro

问题描述

  = SQL（SELECT）从表1中选择标题______ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _数据在我的工作簿的表使用SQL查询。
 
 
 
这是我迄今为止所做的：
  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 _ 
& ;扩展属性=Excel 12.0; HDR =是; IMEX = 1; 
 
设置cn = CreateObject（ADODB.Connection）
设置rs = CreateObject（ADODB.Recordset）
 
 cn.Open strCon 
 
 strSQL =SELECT * FROM [Sheet1 $ A1：G3]
 
 rs.Open strSQL，cn 
 
 Debug.Print rs.GetString 
 
 End Sub 
  
我的脚本像一个硬编码范围的魅力，如上面的片段它也适用于静态命名范围。
 
 
 
但是，它对于动态命名范围或TABLE NAMES而言无效，这对我来说最重要。 p> 
 
 
最近我找到答案是这个人患有同样的痛苦：
  http://www.ozgrid.com/forum/showthread.php?t=72973  
 
 
 
帮助任何人？
 
 
 
 修改 
 
 
 
远，我可以使用结果名称在我的SQL查询。限制是我需要知道表格在哪张表上。我们可以做些什么吗？
 函数getAddress（）
 
 myAddress = Replace（Sheets（ Sheet1）。Range（Table1）。address，$，）
 myAddress =[Sheet1 $& myAddress& ]
 
 getAddress = myAddress 
 
结束功能
  
谢谢！
解决方案
您可以做的一件事是获取动态命名范围的地址，并使用它作为SQL字符串中的输入。例如：
  Sheets（shtName）。range（namedRangeName）。address 
  
哪个地址字符串会出现，如$ $ $ $ $ $ $ $ $ $ $ $ $ $ > 
 
 
 
 编辑： 
 
 
 
正如我在下面的评论中所说，动态获取完整的地址（包括工作表名称），并直接使用它或解析工作表名称供以后使用：
  ActiveWorkbook。 Names.Item（namedRangeName）。RefersToLocal 
  
这样会产生一个像 = Sheet 1中$ C $ 1：！$ C $ 4 。因此，对于上面的代码示例，您的SQL语句可能是
  strRangeAddress = Mid（ActiveWorkbook.Names.Item（namedRangeName） .RefersToLocal，2）
 
 strSQL =SELECT * FROM [strRangeAddress]
  
 
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'")
Allowing me to search (and maybe even insert) data in my Workbook's Tables using SQL queries.

This is what I have done so far:
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.

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

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

Help anyone?

Edit

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
Thanks!
解决方案
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
Which will spit out an address string, something like $A$1:$A$8

Edit:

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
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宏的Workbook中的Excel表上执行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！