有时您想在Excel中使用SQL吗? 这是您的功能。
我创建此模块以直接将结果作为字符串获取(来自fieldloop的所有项目,例如带有“ /”定界符的a / b / c / d)
或者您可以将此函数调用为sub并返回一个数组以指定工作表上的范围(如Access中的查询)
甚至直接使用ctrl + Shift + Enter应用数组公式(请记住首先选择多个单元格->在左上角写入= SQL())
SQL(dataRange作为范围,FieldLoop作为字符串,可选CritA作为字符串,可选分隔符作为字符串,可选唯一作为布尔值) DataRange =包含头的范围FieldLoop =要循环的字段名称,可以使用通配符(例如“ *”)或任何类似字符串普通查询选择语句CritA =查询条件(应为“ Where”和某些条件) Delimiter =“,”或“ /”或任何字符,如果不将该可选空白留空,SQL函数将返回如下字符串: “ a分隔符b分隔符c分隔符d” 唯一 = True / False,默认= false,使您能够从TableRange中“选择DISTINCT”多田! 您已经在Excel中完成了查询数据。
Public Function SQL(dataRange As Range, FieldLoop As String, Optional CritA As String, Optional Delimiter As String, Optional Unique As Boolean)
On Error GoTo err:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim TableName As String
Dim StrResult As Variant
Dim strFile As String, strCon As String, strSQL As String
TableName = dataRange.Parent.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
If Not Unique = True Then
strSQL = "SELECT " & FieldLoop & " FROM [" & TableName & "] " & CritA
Else
strSQL = "SELECT DISTINCT " & FieldLoop & " FROM [" & TableName & "] " & CritA
End If
rs.Open strSQL, cn
With rs
Do While Not (.BOF Or .EOF)
If (InStr(1, FieldLoop, ",", vbBinaryCompare) > 0 Or FieldLoop = "*") And Delimiter = "" Then
StrResult = Application.Transpose(.GetRows)
GoTo NextStep:
Else
If .Fields(0).Value <> "" Then
StrResult = StrResult & .Fields(0).Value & Switch(Delimiter = ",", ",", True, Delimiter)
End If
.MoveNext
End If
Loop
End With
NextStep:
If Not IsArray(StrResult) And Not IsEmpty(StrResult) Then
If Len(StrResult) > 0 Then
SQL = Left$(StrResult, Len(StrResult) - 1)
Else
SQL = "No Item code Found!!"
End If
Else
SQL = StrResult
End If
ExitF:
Exit Function
err:
Debug.Print err.Number & " - " & err.Description
Resume ExitF:
End Function
From: https://bytes.com/topic/access/insights/965264-use-sql-other-application-vba