excelsql备忘

最新推荐文章于 2024-03-08 15:32:47 发布

efu0387

最新推荐文章于 2024-03-08 15:32:47 发布

阅读量99

点赞数

分类专栏： SQL EXCEL VBA 文章标签： sql

本文链接：https://blog.csdn.net/EFU0387/article/details/117636030

版权

SQL 同时被 3 个专栏收录

29 篇文章 1 订阅

订阅专栏

EXCEL

10 篇文章 1 订阅

订阅专栏

VBA

7 篇文章 1 订阅

订阅专栏

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]