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中进行copy和paste的时候,无规律的出现 “类Worksheet的Paste方法无效”错误 怀疑是电脑太快,copy操作还没有完成,就直接运行paste,导致paste出错。 所以在copy操作后,sleep一小段时间,结果真的解决了问题。
评论
1111

1111

2020-06-13

测试评论

echo 'test';

嘻嘻白眼