使用VBA宏在工作簿中对Excel表格执行SQL查询

Question

我试图创建一个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

谢谢！

- Joan-Diego Rodriguez

---

或许这并不会有太大的差别，但我认为自定义函数必须是一个 Function（而不是 Sub）。此外，在自定义函数的末尾，你应该加上类似于 SQL = 要在单元格中显示的答案 这样的语句。但这可能与你遇到的问题无关。 - Jake Bathman

@JakeB。你说得完全正确，谢谢。 - Joan-Diego Rodriguez

只是想在这里补充一下，您可以将范围命名为一个命名范围，并在查询中使用它（或者如果每个工作表只有一个表，则可以使用整个工作表）。因此，您可以使用 SELECT * FROM [Sheet1$] 或者您可以使用 SELECT * FROM MyNamedRange。 - user1274820

另外补充一点，您为什么不能在VBA中命名您的范围，然后运行代码呢？您说过“静态命名范围”，但如果需要，您可以简单地动态调整您的命名范围。 - user1274820

Answer 1

你可以尝试获取动态命名区域的地址，并将其作为 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]"

- Jake Bathman

---

有没有可能我们可以不需要工作表名称作为输入来完成它？ 真是个大烦恼，不能直接使用ADODB连接中的表名，不是吗？VBA相当薄弱，但在企业世界中我必须遵循规则 :) - Joan-Diego Rodriguez

假设您知道命名范围的名称（如果您不知道，这将失去其目的），则可以使用此方法而不是.Address：ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal，它会输出类似于=Sheet1!$C$1:$C$4的字符串。 - Jake Bathman

当我使用strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)并运行strSQL时，出现了一个错误信息Sheet1!$C$1:$C$4不是一个有效的名称。有人能帮帮我吗？ - Thiago Souza

@ThiagoSouza，你可以直接在查询中使用命名区域，像这样：SELECT * FROM MyNamedRange。 - user1274820

Answer 2

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") & ""

- Johan Kreszner

---

这个答案的主要观点是地址应该像这样“Sheet1$A1:X10”。1）在工作表名称和范围之间使用$，2）在范围本身中不使用$。这在第一个、最流行的答案中缺失了。 - Avraham Zhurba

顺便提一下，将范围名称放入方括号 [ ] 中也非常重要。"Select * from [Sheet1$A1:A200]" - Avraham Zhurba

Answer 3

在Joan-Diego Rodriguez的例程、Jordi的方法和Jacek Kotowski的代码基础上，此函数将活动工作簿中的任何表名转换为可用于SQL查询的地址。

注意：添加“[#All]”可以包括标题，避免了您报告的问题。

Function getAddress(byVal sTableName as String) as String 

    With Range(sTableName & "[#All]")
        getAddress= "[" & .Parent.Name & "$" & .Address(False, False) & "]"
    End With

End Function

- Craig Hatmaker

---

这将上面答案中的12行代码重构为3行。干得好！ - pheeper

Answer 4

我是一个初学者，正在研究别人的代码，所以请您宽容并纠正我的错误。我尝试了您的代码，并使用了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

希望你的功能能够进一步发展，我觉得它非常有用。祝你愉快！

- Jacek Kotowski

Answer 5

在这里补充一点，您可以将范围设置为命名范围，并在查询中使用它（或者如果每个表格只有一个表，则可以使用整个工作表）。

因此，您可以使用：

SELECT * FROM MyNamedRange

或者

SELECT * FROM [Sheet1$]

很多答案似乎都要解析地址，而我过去所做的就是将所有表格设置为命名范围，并直接在查询中引用它们（这样做也更清晰）。

在下面的一个更复杂的查询中，我引用的所有范围都是命名范围：

SELECT Unit_Type AS [Unit Type], COUNT(*) AS [Number of Units], SQFT, Deposit AS [Deposit{$}], Rent AS [Base Rent{$}], SUM(RENT) AS [Base Rent Total{$}], SUM(MR) AS [Market Rent Total{$}] FROM
(
    SELECT f1.Unit_Code, Unit_Type, SQFT, Rent, Deposit, SWITCH(MR IS NULL,Rent,MR IS NOT NULL,MR) AS MR FROM
    (   
        SELECT t2.Unit_Code, t2.Unit_Type, SQFT, Rent, Deposit FROM
        (
            SELECT DISTINCT Unit_Code, Unit_Type
            FROM CommUnits
            WHERE Unit_Code NOT LIKE "%WAIT%" AND Exclude=0
        ) t2
        LEFT JOIN
        (
            SELECT UnitType_Code, SQFT, Rent, Deposit
            FROM ResUnitTypes
        ) t1 ON (t1.UnitType_Code = t2.Unit_Type)
    ) f1
    LEFT JOIN
    (
        SELECT Unit_Code, SUM(Current_Charge) AS MR
        FROM ResUnitAmenities
        WHERE Unit_Code NOT LIKE "%WAIT%"
        GROUP BY Unit_Code
    ) f2 on (f1.Unit_Code = f2.Unit_Code)
)
GROUP BY Unit_Type, SQFT, Deposit, Rent

- user1274820

Answer 6

仅回答您的问题的第二部分，关于获取表所在工作表的名称：

Dim name as String

name = Range("Table1").Worksheet.Name

编辑：

为了更加清晰明了：有人建议在Sheet对象上使用Range。但在这种情况下，您不需要这样做；可以通过表格的名称获取表格所在的范围；此名称在整个工作簿中都可用。因此，仅调用Range即可正常工作。

- Jordi

Answer 7

最近我研究了一下这个问题，发现在 Excel 中引用命名表格（列表对象）时会出现问题。

如果在表格名称后面加上后缀“$”，那么一切都会很顺利。

Sub testSQL()

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

    ' Declare variables
    strFile = ThisWorkbook.FullName

    ' construct connection string
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

    ' create connection and recordset objects
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    ' open connection
    cn.Open strCon

    ' construct SQL query
    strSQL = "SELECT * FROM [TableName$] where [ColumnHeader] = 'wibble';"

    ' execute SQL query
    rs.Open strSQL, cn

    Debug.Print rs.GetString

    ' close connection
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

- MikeL

---

OP已经将TableName附加了$，所以问题不在于此：SELECT * FROM [Sheet1$A1:G3]。 - Bugs

但我没有使用工作表名称，而是在Excel中设置了实际表名，没有 $，查询会抱怨找不到具有“TableName”对象的 $，它返回数据。 - MikeL

接下来，我在使用实际列标题名称时遇到了一些混合的结果，出现了“未为一个或多个必需参数给定值”的消息，但是使用格式 [F1] 表示第 1 列，[F2] 表示第 2 列等索引却非常顺利。 - MikeL

Answer 8

我找到了这个方法，对我很有效。

strSQL = "SELECT * FROM DataTable" 

'DataTable是命名范围'

我怎样在Excel表格的命名范围内运行SQL语句？

- fultonec