如何在vba中用ado合并不同excel工作簿的内容？

在如何在vba中用ado访问各种不同的数据源？ 一文中我们介绍了用ado访问excel数据源的方法。

但是我们经常需要访问的数据位于多个不同的文件中，这时候可以采用如下的步骤：

1. 先用连接字符串连接其中一个excel工作簿。
2. 在SQL语句中用[Excel 12.0;Database=” & Excel.ThisWorkbook.Path & “\test1.xlsx].[Sheet1$] 这样的形式访问其它的excel工作簿。
3. 如果要合并的excel工作簿特别多，可以先用代码构造合并的SQL语句。

Sub QQ1722187970()
    Dim oRecrodset
    Dim oConStr
    Dim sSql As String
    Dim oWk As Worksheet
    Dim sConStr As String
    Dim oWB As Workbook
    Set oWk = Excel.ThisWorkbook.Worksheets.Add
    Dim sPath As String
    sPath = Excel.ThisWorkbook.Path
    sKC = VBA.Dir(sPath & "\*库存*.xls*", vbNormal)
    sXs = VBA.Dir(sPath & "\*销售*.xls*", vbNormal)
    Set oWB = Excel.Workbooks.Open(sPath & "\" & sKC)
    Set oWk1 = oWB.Worksheets(1)
    sKCName = oWk1.Name
    oWB.Close
    set oWB = Excel.Workbooks.Open(sPath & "\" & sXs)
    Set oWk1 = oWB.Worksheets(1)
    sXsname = oWk1.Name
    oWB.Close
    sVersion = Excel.Application.Version
    If sVersion <= 12 Then
        sConStr = "Provider='Microsoft.Jet.OLEDB.4.0';Data Source=" & Excel.ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=YES;MAXSCANROWS=16'"
    Else
        sConStr = "Provider='Microsoft.ACE.OLEDB.12.0';Data Source=" & Excel.ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES;MAXSCANROWS=16'"
    End If
    Debug.Print sConStr
    Set oConStr = CreateObject("ADODB.Connection")
    '使用Connection连接数据源，并用Execute方法执行对应的SQL语句生成Recrodset对象
    oConStr.Open sConStr
    '在SQL语句中连接外部数据源的标准语法
    sSql = "select a.商品名称 as 商品名称,a.规格名称 as 规格,b.销售数量 as 销售数量,a.实际库存 as 实际库存 from [Excel 12.0;Database=" & sPath & "\" & sKC & "].[" & sKCName & "$] as a,[Excel 12.0;Database=" & sPath & "\" & sXs & "].[" & sXsname & "$] as b where a.商品编码=b.商品编码"
    Debug.Print sSql
    Set oRecrodset = oConStr.Execute(sSql)
    With oRecrodset
        '循环导入字段名
        For i = 1 To .Fields.Count
            oWk.Cells(1, i) = .Fields(i - 1).Name
        Next
        oWk.Cells(2, 1).CopyFromRecordset oRecrodset
    End With
    Set oRecrodset = Nothing
End Sub

如果用的是Microsoft.Jet.OLEDB.4.0，则需要使用”[Excel 8.0;Database=”这样的形式访问外部的数据源。

如果用的是Microsoft.ACE.OLEDB.12.0，则推荐使用”[Excel 12.0;Database=”这样的形式访问外部的数据源。