|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub Hebing()
Dim i As Long, F() As String, l As Long
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*.XLS"
.LookIn = ThisWorkbook.Path & "\"
.Execute
If .FoundFiles.Count > 0 Then
ReDim F(1 To .FoundFiles.Count - 1) As String
l = 1
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) <> ThisWorkbook.FullName Then
F(l) = .FoundFiles(i)
l = l + 1
End If
Next
End If
End With
Sheet17.Rows("2:65536").ClearContents
For i = 1 To UBound(F)
Dim cn As ADODB.Connection
Dim Sql As String
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & F(i) & ";Extended Properties=""Excel 8.0;HDR=No"""
.CursorLocation = adUseClient
.Open
End With
Sql = "Select * FROM [数据$A5:FU1000] "
Dim Rg As Range
Set Rg = Sheet1.Range("A65536").End(xlUp)
If Rg.Address = Sheet1.Range("A1").Address Then
Rg.Offset(1).CopyFromRecordset cn.Execute(Sql)
Else
Rg.Offset(1, 0).CopyFromRecordset cn.Execute(Sql)
End If
cn.Close
Next
End Sub
请把这个代码修改为合并不同工作簿的工作表适用范围较广的通用性代码 |
|