寻找ADODB.Recordset替代方案：Mac Excel 2019用SQL操作工作表

Mac Excel 2019不支持ActiveX组件，因此ADODB无法正常运行。以下是几种无需依赖ADODB、仍可通过SQL或类SQL逻辑操作工作表数据的可行方案：

方案1：使用Power Query（推荐） #

Power Query是Excel内置工具，支持直接执行SQL查询，Mac Excel 2019已集成该功能，无需额外组件。

GUI操作步骤 #

• 选中目标工作表或数据区域，点击「数据」选项卡 →「从表格/范围」（自动识别表头）
• 在Power Query编辑器中，点击「转换」→「运行SQL」，输入查询语句（如SELECT * FROM [Sheet1$]），点击确定获取结果
• 点击「关闭并上载」，将查询结果导入到指定位置

VBA自动化执行代码 #

如果需要通过VBA自动化完成，可使用以下代码：

Sub PowerQuerySQL()
    Dim qry As WorkbookQuery
    Dim sqlStr As String
    Dim connStr As String
    
    sqlStr = "SELECT * FROM [Sheet1$]"
    ' 构建指向当前工作簿的连接字符串
    connStr = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"
    
    ' 创建SQL查询
    Set qry = ThisWorkbook.Queries.Add( _
        Name:="SQLQuery", _
        Formula:= _
            "let" & Chr(13) & "" & Chr(10) & _
            "    Source = Sql.Database("""", """", [Query=""" & sqlStr & """, ConnectionString=""" & connStr & """])" & Chr(13) & "" & Chr(10) & _
            "in" & Chr(13) & "" & Chr(10) & _
            "    Source" _
    )
    
    ' 将结果加载到新工作表
    qry.EnableRefresh = True
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;" & connStr, Destination:=Range("$A$1")).QueryTable
        .CommandText = sqlStr
        .PreserveFormatting = True
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .AdjustColumnWidth = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

方案2：纯VBA模拟SQL逻辑 #

如果不想依赖Power Query，可以通过VBA结合数组、字典等对象模拟常用SQL操作。以下是基础的SELECT *示例：

Function VBASimulateSQL()
    Dim ws As Worksheet
    Dim dataArr As Variant
    Dim resultArr As Variant
    Dim i As Long, j As Long
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' 获取工作表所有数据到数组
    dataArr = ws.UsedRange.Value
    
    ' 复制所有数据到结果数组
    ReDim resultArr(1 To UBound(dataArr, 1), 1 To UBound(dataArr, 2))
    For i = 1 To UBound(dataArr, 1)
        For j = 1 To UBound(dataArr, 2)
            resultArr(i, j) = dataArr(i, j)
        Next j
    Next i
    
    ' 将结果输出到新工作表
    Dim newWs As Worksheet
    Set newWs = ThisWorkbook.Worksheets.Add
    newWs.Range("A1").Resize(UBound(resultArr, 1), UBound(resultArr, 2)).Value = resultArr
End Function

如需实现WHERE筛选、ORDER BY排序等复杂逻辑，可扩展该函数，比如添加条件判断、使用字典去重、集成排序算法等。

方案3：Mac专属——AppleScript结合SQLite #

借助Mac系统内置的SQLite数据库，通过AppleScript将Excel数据导入SQLite执行查询后再导回Excel，示例如下：

VBA调用AppleScript代码 #

Sub SQLiteSQLViaAppleScript()
    Dim appleScriptStr As String
    Dim headerStr As String
    
    ' 获取表头字符串
    headerStr = Join(ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows(1).Value, ", ")
    
    appleScriptStr = "tell application ""Microsoft Excel""" & vbCrLf & _
        "    set dataRange to used range of sheet ""Sheet1""" & vbCrLf & _
        "    set dataList to value of dataRange" & vbCrLf & _
        "end tell" & vbCrLf & _
        "do shell script ""sqlite3 /tmp/excel_data.db 'CREATE TABLE IF NOT EXISTS Sheet1 (" & headerStr & ")'""" & vbCrLf & _
        "repeat with rowItem in rest of dataList" & vbCrLf & _
        "    set sqlStr to ""INSERT INTO Sheet1 VALUES ('"" & (join items of rowItem with ""', '"")) & ""')""" & vbCrLf & _
        "    do shell script ""sqlite3 /tmp/excel_data.db '"" & sqlStr & ""'""" & vbCrLf & _
        "end repeat" & vbCrLf & _
        "set queryResult to do shell script ""sqlite3 -header -csv /tmp/excel_data.db 'SELECT * FROM Sheet1'""" & vbCrLf & _
        "tell application ""Microsoft Excel""" & vbCrLf & _
        "    set newSheet to make new sheet" & vbCrLf & _
        "    set value of range ""A1"" of newSheet to (split queryResult by linefeed)" & vbCrLf & _
        "end tell"
    
    MacScript appleScriptStr
End Sub

内容的提问来源于stack exchange，提问作者EvaHHHH