|
发表于 2015-4-3 16:58
|
显示全部楼层
本楼为最佳答案
- Sub 比较()
- '** 使用FileDialog对象来选择文件夹
- Dim fd As FileDialog
- Dim myPath As String
- Set fd = Application.FileDialog(msoFileDialogFolderPicker)
- fd.InitialFileName = ThisWorkbook.Path & ""
- If fd.Show = -1 Then myPath = fd.SelectedItems(1)
- Set fd = Nothing
- If myPath <> "" Then '如果选到
- Dim wb As Workbook
- Set fso = CreateObject("scripting.filesystemobject")
- Set ff = fso.getfolder(myPath)
- xrr = Sheet1.Range("a1:a" & [a65536].End(3).Row) '当前工作A列数据
- c = 1
- For Each f In ff.Files '打开所有文件
- Set wb = Workbooks.Open(f)
- xname = Split(wb.Name, ".")(0) '工作表名
- Set d = CreateObject("scripting.dictionary")
- arr = wb.Worksheets(1).[a1].CurrentRegion
- For i = 1 To UBound(arr) '打开工作表A列和B列相关联
- d(arr(i, 1)) = arr(i, 2)
- Next
- wb.Close False
- ReDim yrr(1 To UBound(xrr), 1 To 1): yrr(1, 1) = xname
- For i = 2 To UBound(xrr)
- yrr(i, 1) = d(xrr(i, 1))
- Next
- c = c + 1
- Sheet1.Cells(1, c).Resize(UBound(yrr), 1) = yrr
- Next
- End If
- End Sub
复制代码 |
|