VBA通过ado方式提取数据汇总表格操作-20200811

 

用ADO方式提取表格里面的数据，比vba操作excel打开方式快很多。我这里是全部读取再筛选的。如果第一行不是标题【也是数据的话】提取不到。

 参考 https://www.cnblogs.com/zhujie-com/p/11762044.html

j = 1

For i = 1 To filecount - 1
Application.StatusBar = GetProgress(i, filecount - 1, "数据合并处理中")


    strPath = mypath & myFilename(i)

    Set cnn = CreateObject("adodb.connection")
    If Application.Version < 12 Then
        str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strPath
    Else
        str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
    End If
    cnn.Open str_cnn
    
    
    Sheets("temp").Select
    Cells.ClearContents

    strSQL = "select * from [Sheet1$]"
    Set rst = cnn.Execute(strSQL)
    
    
    'Range("a1").CopyFromRecordset conn.Execute("select * from [Sheet1$]") '前面Range("a1").CopyFromRecordset的作用是将抓取到的数据放到以“a1”为头的单元格里
    
    
    'For i = 0 To rst.Fields.Count - 1
    '    Cells(1, i + 1) = rst.Fields(i).Name
    'Next
    
    
    Range("a1").CopyFromRecordset rst
    
    cnn.Close
    Set cnn = Nothing
    
    CN = Range("A65535").End(xlUp).Row
    arr = Range("L1:L" & CN)
    
    Sheets(Sht2).Cells(1, j).Value = myFilename(i)
    Sheets(Sht2).Cells(2, j).Resize(CN, 1).Value = arr
    j = j + 1

''''''''''''''''''''''''''''''''''''''''''''''
     

Next