|
序列号修正了下,原来引用的分表里的。- Sub 数据整理()
- Dim strPath As String, strFile As String
- Dim arr(1 To 10000, 1 To 5), i As Long, j As Long
- strPath = ThisWorkbook.Path & Application.PathSeparator & Application.PathSeparator
- strFile = Dir(strPath & "*.xls")
- With Application
- .ScreenUpdating = False
- .DisplayAlerts = False
- .Calculation = xlCalculationManual
- End With
- Do While Len(strFile)
- If strFile <> ThisWorkbook.Name Then
- Call getDate(strPath & strFile, arr, i)
- End If
- strFile = Dir
- Loop
-
- Cells(Rows.Count, "b").End(xlUp).Offset(1).Resize(i, UBound(arr, 2)).Value = arr
- With Range("a2")
- .Value = 1
- .AutoFill .Resize(Cells(Rows.Count, "b").End(xlUp).Row - 1), xlFillSeries
- End With
-
- With Application
- .ScreenUpdating = True
- .DisplayAlerts = True
- .Calculation = xlCalculationAutomatic
- End With
- MsgBox "数据整理完成", vbInformation
- End Sub
- Sub getDate(ByRef strFullName As String, ByRef arr As Variant, ByRef arrSize As Long)
- Dim objWbk As Workbook
- Dim arrTemp
- Dim strCode$, strCompany$
- Dim lLastrow As Long
- Dim i As Long
- Set objWbk = GetObject(strFullName)
- With objWbk
- With .Worksheets("sheet1")
- lLastrow = .Cells(Rows.Count, "b").End(xlUp).Row
- If lLastrow > 4 Then
- arrTemp = .Range("a5:e" & lLastrow).Value
- strCode = Split(.Range("a2").Value, ":")(1)
- strCompany = Split(.Range("a3").Value, ":")(1)
- For i = LBound(arrTemp) To UBound(arrTemp)
- arrSize = arrSize + 1
- arr(arrSize, 1) = "'" & strCode
- arr(arrSize, 2) = "'" & strCompany
- arr(arrSize, 3) = arrTemp(i, 3)
- arr(arrSize, 4) = "'" & arrTemp(i, 4)
- arr(arrSize, 5) = arrTemp(i, 5)
- Next
- End If
- End With
- .Close False
- End With
- End Sub
复制代码 |
|