[VB/VBA]SQL操作EXCEL

最新推荐文章于 2024-08-17 10:03:36 发布

逐浪_一生悬命

最新推荐文章于 2024-08-17 10:03:36 发布

阅读量6.9k

点赞数

分类专栏： VB专区文章标签： sql microsoft excel properties security library

本文链接：https://blog.csdn.net/szwangdf/article/details/2659715

版权

VB专区专栏收录该内容

17 篇文章 1 订阅

订阅专栏

[VB/VBA]SQL操作EXCEL

1、添加引用

(1)、Microsoft Active Data Objects 2.8 Library

(2)、Microsoft ADO Ext . 2.8 For DDL and Security

2、用ADOX连接EXCEL文件

    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    Dim cmd As ADODB.command
    Dim rs As Recordset
    
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & ThisWorkbook.Path & _
        "/book2.xls;Extended Properties=Excel 8.0"

3、创建Sheet以及给单元格赋值

    Set tbl = New ADOX.Table
    tbl.Name = "TestTable"
    Set col = New ADOX.Column
    With col
        .Name = "Col1"
        .Type = adDouble
    End With
    tbl.Columns.Append col
    Set col = Nothing
    
    Set col = New ADOX.Column
    With col
        .Name = "Col2"
        .Type = adVarWChar
    End With
    tbl.Columns.Append col
    cat.Tables.Append tbl

4、执行SQL文（SELECT、INSERT、UPDATE、DELETE都可以，下面的代码以SELECT为例）

    Set cmd = New ADODB.command
    cmd.CommandText = "select TA.*, TB.w from [表1$] TA, [表2$] TB where TA.A = Tb.A and TA.A < 'A10'"
    cmd.CommandType = adCmdText
    cmd.ActiveConnection = cat.ActiveConnection
    Set rs = cmd.Execute()
    While Not rs.EOF
        Debug.Print rs.GetString()
    Wend