|
楼主 |
发表于 2018-12-24 17:18
|
显示全部楼层
- 'http://club.excelhome.net/thread-1281113-2-1.html
- Sub 同名工作薄_分别合并成一薄一表_ADO加字典法() '''已入代码宝库
- Dim 字典 As Object
- Application.ScreenUpdating = False: Application.DisplayAlerts = False
- Set 字典 = CreateObject("scripting.dictionary")
- Set 连接 = CreateObject("adodb.connection")
- Set Fso = CreateObject("Scripting.FileSystemObject")
- Set Folder = Fso.GetFolder(ThisWorkbook.Path & "\数据")
- Call 子程序(Folder, 字典)
- Set 新生薄 = Workbooks.Add(xlWBATWorksheet)
- 连接.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=excel 12.0;Data Source=" & ThisWorkbook.FullName
- 关键字 = 字典.keys
- 项 = 字典.items
- For i = 0 To 字典.Count - 1
- 项拆分数组 = Split(项(i), ",")
- With 新生薄.Sheets(1)
- .Cells.ClearContents
- For j = 0 To UBound(项拆分数组)
- SQL = "select * from [Excel 12.0;Database=" & 项拆分数组(i) & "].[Sheet1$]"
- Set 记录 = 连接.Execute(SQL)
- If j = 0 Then
- For 列 = 1 To 记录.Fields.Count
- .Cells(1, 列) = 记录.Fields(列 - 1).Name
- Next
- .[a2].CopyFromRecordset 记录
- Else
- .Range("a" & .Rows.Count).End(xlUp).Offset(1).CopyFromRecordset 连接.Execute(SQL)
- End If
- Next
- 新生薄.SaveAs ThisWorkbook.Path & "\需要得到的结果" & 关键字(i)
- End With
- Next
- 新生薄.Close
- Set Folder = Nothing
- Set Fso = Nothing
- 记录.Close: Set 记录 = Nothing
- 连接.Close: Set 连接 = Nothing
- Application.ScreenUpdating = True
- MsgBox "ok"
- End Sub
- Sub 子程序(ByVal Folder As Object, 字典 As Object)
- Dim SubFolder As Object
- Dim 外薄 As Object
- For Each 外薄 In Folder.Files
- If 外薄.Name Like "*.xlsx" Then
- If Not 字典.Exists(外薄.Name) Then
- 字典(外薄.Name) = 外薄
- Else
- 字典(外薄.Name) = 字典(外薄.Name) & "," & 外薄
- End If
- End If
- Next
- For Each SubFolder In Folder.SubFolders
- Call 子程序(SubFolder, 字典)
- Next
- End Sub
- Sub 同类工作薄_分别合并成一薄一表_ADO加字典法() '''已入代码宝库
- Dim 字典 As Object
- Application.ScreenUpdating = False: Application.DisplayAlerts = False
- Set 字典 = CreateObject("scripting.dictionary")
- Set 连接 = CreateObject("adodb.connection")
- Set Fso = CreateObject("Scripting.FileSystemObject")
- Set Folder = Fso.GetFolder(ThisWorkbook.Path & "\数据")
- Call 内程序(Folder, 字典)
- Set 新生薄 = Workbooks.Add(xlWBATWorksheet)
- 连接.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=excel 12.0;Data Source=" & ThisWorkbook.FullName
- 关键字 = 字典.keys
- 项 = 字典.items
- For i = 0 To 字典.Count - 1
- 项拆分数组 = Split(项(i), ",")
- With 新生薄.Sheets(1)
- .Cells.ClearContents
- For j = 0 To UBound(项拆分数组)
- SQL = "select * from [Excel 12.0;Database=" & ThisWorkbook.Path & "\数据" & 项拆分数组(j) & "].[Sheet1$]"
- Set 记录 = 连接.Execute(SQL)
- If j = 0 Then
- For 列 = 1 To 记录.Fields.Count
- .Cells(1, 列) = 记录.Fields(列 - 1).Name
- Next
- .[a2].CopyFromRecordset 记录
- Else
- .Range("a" & .Rows.Count).End(xlUp).Offset(1).CopyFromRecordset 连接.Execute(SQL)
- End If
- Next
- 新生薄.SaveAs ThisWorkbook.Path & "" & 关键字(i)
- End With
- Next
- 新生薄.Close
- Set Folder = Nothing
- Set Fso = Nothing
- 记录.Close: Set 记录 = Nothing
- 连接.Close: Set 连接 = Nothing
- Application.ScreenUpdating = True
- MsgBox "ok"
- End Sub
- Sub 内程序(ByVal Folder As Object, 字典 As Object)
- Dim SubFolder As Object
- Dim 外薄 As Object
- For Each 外薄 In Folder.Files
- If 外薄.Name Like "*.xlsx" Then
- If Not 字典.Exists(Split(外薄.Name, " ")(0)) Then
- 字典(Split(外薄.Name, " ")(0)) = 外薄.Name
- Else
- 字典(Split(外薄.Name, " ")(0)) = 字典(Split(外薄.Name, " ")(0)) & "," & 外薄.Name
- End If
- End If
- Next
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|