在excel中执行Sql语句，实现复杂查询

引子

Excel过滤与查找数据的功能很强，但如果是非常复杂的查询还是用SQL语句来得直接。但这需要有一些数据库的专业知识。

实事上excel支持对Sheet上的数据进行sql查询，这时Sheet名就是要查询的数据表的名字，只不过要加上中括号。

为了方便用sql语句直接操作sheet上的数据，特地用VBA写了一个小程序。

实例操作

程序的文件结构如下：

execlSQL.xls是主程序所在的excel文件，testData.xls是存放测试数据的excel文件。
打开execlSQL.xls，效果如下图所示：

点击左上方的“打开数据源”按钮,选择要做为数据源的excel文件，这里选择包含测试数据的testData.xls.
然后点击右下方的“执行查询“按钮，就可以看到查询的结果，如下图所示：

如果想修改查询所用的sql语句，直接在文本框中修改sql语句即可。

工作原理

在“打开数据源时” 通过如下语句建立了数据源

Set cnn = CreateObject("adodb.connection")          '创建数据库连接cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & srcFileName

在“执行查询”时，通过adodb的connection对象执行了sql查询

 Set rs = CreateObject("adodb.recordset")            '创建一个数据集
 SQL = Trim(txtSql.Text)
 Set rs = cnn.Execute(SQL)                           '执行查询

VBA代码

Option Explicit

Private cnn, rs, SQL$
Private srcFileName As String


'打开数据源xls文档
Private Sub cmdOpen_Click()

    Dim strFileName As Variant
    If getOpenFiles(strFileName) = True Then
        srcFileName = strFileName
    Else
        srcFileName = ""
    End If
    
End Sub


'执行查询
Private Sub cmdExec_Click()

    If srcFileName = "" Then
        MsgBox "没有打开数据源"
        Exit Sub
    End If

    On Error GoTo errHandle
    
    Set cnn = CreateObject("adodb.connection")          '创建数据库连接
    cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & srcFileName

    Set rs = CreateObject("adodb.recordset")            '创建一个数据集
    SQL = Trim(txtSql.Text)
    Set rs = cnn.Execute(SQL)                           '执行查询
    
    Sheet2.Cells.ClearContents
    
    Dim i As Integer
    For i = 1 To rs.Fields.Count
        Sheet2.Cells(1, i) = rs.Fields(i - 1).Name
    Next
    Sheet2.Range("a2").CopyFromRecordset rs
    
    cnn.Close
    Set cnn = Nothing
    
    Sheet2.Activate
    
    Exit Sub
errHandle:
    MsgBox Err.Description
    
End Sub


'取得打开的文件名,如果没有打开文件则返回false
Private Function getOpenFiles(strFileName As Variant) As Boolean

    Dim workPath
    workPath = ThisWorkbook.Path
    ChDrive Split(workPath, ":")(0)
    ChDir workPath
    
    Dim FileNames As Variant
    
    FileNames = Application.GetOpenFilename("Excel文件 (*.xls),*.xls", , "选择要执行查询的源文件", , False)
  
    'VarType(varname)　返回一个 Integer，指出变量的子类型 ,TypeName 返回类型名称
   
    If TypeName(FileNames) = "Boolean" Then
       getOpenFiles = False
    Else
        strFileName = FileNames
        getOpenFiles = True
    End If
    
End Function

完整程序下载

可以直接执行的完整程序请到CSDN资源中下载