VBA 用SQL查询Excel数据

原创文章,欢迎转载:http://miao.blog/article/vba-excel-sql

从Excel表中按照一定条件提取数据,如果遇到大表,用Range.Value的方式读取数据再取值判断的方式,将会非常慢,而如果直接用SQL的方式将Excel表数据作为Table来查询,则非常快~

' connection
Private conn As ADODB.Connection

' ****************************************************************************
' connect to db
Private Sub OpenExcelConn(excelPath As String)
    If conn Is Nothing Then Set conn = New ADODB.Connection
    With conn
        If .State = 0 Then
            .CursorLocation = adUseClient
            .ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0 Macro;hdr=yes';data source=" & excelPath
            .ConnectionTimeout = 2
            .Open
        End If
    End With
End Sub

' close db
Private Sub CloseExcelConn()
    If Not conn Is Nothing Then
        If conn.State = 1 Then
            conn.Close
            Set conn = Nothing
        End If
    End If
End Sub

' Query data from excel
' rng: value paste start cell
Sub QueryDataFromExcel(excelPath As String, sql As String, rng As Range)
    
    Call OpenExcelConn(excelPath)
    
    Dim rst As ADODB.Recordset: Set rst = New ADODB.Recordset
    
    ' execute sql
    rst.Open sql, conn, adOpenStatic, adLockReadOnly
    
    ' copy value freom record set
    rng.CopyFromRecordset rst
    
    Set rst = Nothing
    
    Call CloseExcelConn
End Sub
推荐阅读
使用VBA调用Outlook发送邮件
VBA发送GET、POST请求的方法
评论
1111

1111

1个月前

测试评论

echo 'test';

嘻嘻白眼