[已解决]不打开数据工作簿导入到汇总表

爱疯

blD95ESP.rar (12.57 KB, 下载次数: 74)

2010-11-11 10:52 上传

点击文件名下载附件
不打开数据工作簿导入到汇总表

最佳答案

月排行榜 / 总排行榜

amulee

2010-11-11 11:10

Sub a1()
    Dim conn As Object, rst As Object
    Dim Sql As String, i%
    Set conn = CreateObject("adodb.connection")    '建立ADO连接对象
    Set rst = CreateObject("adodb.recordset")    '建立ADO记录集对象
    conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" _
            & ThisWorkbook.Path & "/1.xls"
    'Sql语句
    Sql = "Select * from [Sheet1$]"
    '执行查询，将结果存放于RST中
    Set rst = conn.Execute(Sql)
    '清空表格
    Cells.Clear
    '将标题写入表格
    For i = 0 To rst.fields.Count - 1
        Cells(1, i + 1) = rst.fields(i).Name
    Next i
    '将RST的结果复制到表格
    Range("A2").CopyFromRecordset rst
    rst.Close
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
End Sub
Sub a2()
    Dim conn As Object, rst As Object
    Dim Sql As String
    Set conn = CreateObject("adodb.connection")    '建立..
    Set rst = CreateObject("adodb.recordset")    '建立..
    conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" _
            & ThisWorkbook.Path & "/1.xls"
    'Sql语句
    Sql = "Select * from [Sheet1$] where 语文>=60"
    '执行查询，将结果存放于RST中
    Set rst = conn.Execute(Sql)
    '清空表格
    Cells.Clear
    '将RST的结果复制到表格
    Range("A1").CopyFromRecordset rst
    rst.Close
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
End Sub

跳转到最佳答案楼层

tlj588

挺高深，我想学，了解中

amulee

Sub a1()
    Dim conn As Object, rst As Object
    Dim Sql As String, i%
    Set conn = CreateObject("adodb.connection")    '建立ADO连接对象
    Set rst = CreateObject("adodb.recordset")    '建立ADO记录集对象
    conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" _
            & ThisWorkbook.Path & "/1.xls"
    'Sql语句
    Sql = "Select * from [Sheet1$]"
    '执行查询，将结果存放于RST中
    Set rst = conn.Execute(Sql)
    '清空表格
    Cells.Clear
    '将标题写入表格
    For i = 0 To rst.fields.Count - 1
        Cells(1, i + 1) = rst.fields(i).Name
    Next i
    '将RST的结果复制到表格
    Range("A2").CopyFromRecordset rst
    rst.Close
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
End Sub
Sub a2()
    Dim conn As Object, rst As Object
    Dim Sql As String
    Set conn = CreateObject("adodb.connection")    '建立..
    Set rst = CreateObject("adodb.recordset")    '建立..
    conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" _
            & ThisWorkbook.Path & "/1.xls"
    'Sql语句
    Sql = "Select * from [Sheet1$] where 语文>=60"
    '执行查询，将结果存放于RST中
    Set rst = conn.Execute(Sql)
    '清空表格
    Cells.Clear
    '将RST的结果复制到表格
    Range("A1").CopyFromRecordset rst
    rst.Close
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
End Sub

爱疯

谢谢阿木！

学习了，好好看看去

[em04]