excelsql备忘

Sub CreateQuery3()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim myPath As String
    Dim SQL As String
    Dim i As Integer
    myPath = ThisWorkbook.Path & "\技巧180 创建查询记录集.xlsm"
    On Error GoTo ErrMsg
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & myPath
    SQL = "SELECT * FROM [Sheet1$a:b]"
    rst.Open SQL, cnn, adOpenKeyset, adLockOptimistic
    Cells.ClearContents
    For i = 0 To rst.Fields.Count - 1
        Cells(1, i + 1) = rst.Fields(i).Name
    Next
    Range("A2").CopyFromRecordset rst
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
ErrMsg:
    MsgBox Err.Description, , "错误报告"
End Sub

SELECT * FROM [Sheet1$a:b]