|
楼主 |
发表于 2016-11-18 14:22
|
显示全部楼层
- Sub GetDatas()
-
- Dim strThisPath As String
- Dim strThisName As String
-
- Dim iMaxCount_0 As Long
- Dim Rows_0 As Long
- Dim Cns_0 As Long
- Dim KeyRows As Long
- Dim i As Long, ia As Long, ib As Long, ic As Long
-
- Dim cfg_0 As Variant
- Dim cfg_1 As Variant
- Dim cfgRange_0 As Variant
-
- Dim isFind As Boolean
-
- Dim objFile
- Dim objExcel
-
- strThisPath = ThisWorkbook.Path & ""
- strThisName = ThisWorkbook.Name
- objFile = Dir(strThisPath & "*.xlsx")
- Rows_0 = 2
- Cns_0 = 1
- iMaxCount_0 = 100 '汇总表最大标题数量,当需求超出可自行调整,影响运行效率,值越大,运行会越慢
- Set cfgRange_0 = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(65536, iMaxCount_0))
- cfgRange_0.ClearContents
- cfg_0 = cfgRange_0.Value
-
- Do While objFile <> ""
- If objFile <> strThisName Then
- Set objExcel = Workbooks.Open(strThisPath & objFile)
- cfg_1 = objExcel.Sheets(1).[a1].CurrentRegion
-
- For i = 1 To UBound(cfg_1)
- If cfg_1(i, 1) = "序号" Then
- KeyRows = i
- Exit For
- End If
- Next i
-
- For i = 1 To UBound(cfg_1, 2)
- isFind = False
- For ia = 1 To UBound(cfg_0, 2)
- If Len(cfg_0(1, ia)) = 0 Then Exit For
- If cfg_1(KeyRows, i) = cfg_0(1, ia) Then
- isFind = True
- Exit For
- End If
- Next ia
- If isFind = False Then
- cfg_0(1, Cns_0) = cfg_1(KeyRows, i)
- Cns_0 = Cns_0 + 1
- End If
- Next i
-
- For ia = KeyRows + 1 To UBound(cfg_1)
- For ib = 1 To iMaxCount_0
- For ic = 1 To UBound(cfg_1, 2)
- If cfg_0(1, ib) = cfg_1(KeyRows, ic) Then
- cfg_0(Rows_0, ib) = cfg_1(ia, ic)
- Exit For
- End If
- Next ic
- Next ib
- Rows_0 = Rows_0 + 1
- Next ia
- objExcel.Close True
- End If
- objFile = Dir
- Loop
-
- cfgRange_0.Value = cfg_0
- End Sub
复制代码
这个代码只能合并同一文件夹中的工作簿,不能扫描工作簿中的每一个工作表,代码要如何改写?谢谢!
|
|