我想做一个excel宏,让我在Excel中获得以下功能。
=SQL("SELECT heading_1 FROM Table1 WHERE heading_2='foo'")
允许我使用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 _
& ";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
我的脚本对于硬编码的范围,如上面的片段中的范围,工作得很好。它对静态命名的范围也能很好地工作。
然而,它不能与动态命名的范围或表名一起工作,这对我来说是最重要的。
我所找到的最接近的答案是这个人患有同样的病症。 http://www.ozgrid.com/forum/showthread.php?t=72973
谁能帮帮我?
编辑
到目前为止,我已经完成了这个工作,我可以在我的SQL查询中使用所得的名称。限制是,我需要知道这些表在哪张表上。我们能在这方面做些什么吗?
Function getAddress()
myAddress = Replace(Sheets("Sheet1").Range("Table1").address, "$", "")
myAddress = "[Sheet1$" & myAddress & "]"
getAddress = myAddress
End Function
谢谢!
你可以做的一件事是获得动态命名范围的地址,并将其作为你的SQL字符串的输入。比如说。
Sheets("shtName").range("namedRangeName").Address
这将吐出一个地址字符串,类似于"$A$1:$A$8"。
编辑:
正如我在下面的评论中所说,你可以动态地获得完整的地址(包括工作表名称),并直接使用它或解析工作表名称以便以后使用。
ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal
其结果是一个字符串,如=Sheet1!$C$1:$C$4
。所以对于你上面的代码例子,你的SQL语句可以是
strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)
strSQL = "SELECT * FROM [strRangeAddress]"
Public Function GetRange(ByVal sListName As String) As String
Dim oListObject As ListObject
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In wb.Sheets
For Each oListObject In ws.ListObjects
If oListObject.Name = sListName Then
GetRange = "[" & ws.Name & "$" & Replace(oListObject.Range.Address, "$", "") & "]"
Exit Function
End If
Next oListObject
Next ws
End Function
在你的SQL中这样使用它
sSQL = "Select * from " & GetRange("NameOfTable") & ""
我是一个初学者,在别人的代码上做手脚,所以请宽大处理,进一步纠正我的错误。我尝试了你的代码,并使用了VBA帮助,以下内容对我有用。
Function currAddressTest(dataRangeTest As Range) As String
currAddressTest = ActiveSheet.Name & "$" & dataRangeTest.Address(False, False)
End Function
当我为我的函数选择数据源参数时,它被变成了Sheet1$A1:G3格式。如果excel在我的公式中把它改为Table1[#All]的引用,函数仍能正常工作
然后我在你的函数中使用了它(试图播放并添加另一个参数注入到WHERE...
Function SQL(dataRange As Range, CritA As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim currAddress As String
currAddress = ActiveSheet.Name & "$" & dataRange.Address(False, False)
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 [" & currAddress & "]" & _
"WHERE [A] = '" & CritA & "' " & _
"ORDER BY 1 ASC"
rs.Open strSQL, cn
SQL = rs.GetString
End Function
希望你的函数能进一步发展,我觉得它非常有用。祝你有个愉快的一天