|
- Option Explicit
- Dim arr
- Sub 生成数据()
- Dim filename As String ', , Rng As Range,
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Application.EnableEvents = False
- Range("a5:s500").ClearContents
- ListDirs ThisWorkbook.Path & "\数据库"
- Cells(5, 1).Resize(UBound(arr, 2), UBound(arr)) = Application.Transpose(arr)
- Application.EnableEvents = True
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
- Sub 删除数据()
- Range("A5:w65536").ClearContents
- End Sub
- Sub ListDirs(ByVal Path As String)
- '文件名
- Dim filename$
- '文件夹数组
- Dim arrPath()
- '当前搜索的文件夹
- Dim sPath$
- '计数变量
- Dim i&, j&, k&, s&
- Dim N1&, N2&, Y!
- Dim fs As Workbook
- Dim Rng As Range
- i = 1: j = 1
- s = 2
- ReDim arrPath(1 To 1)
-
- arrPath(i) = Path & Application.PathSeparator
- 'On Error Resume Next
- sPath = arrPath(j)
-
- Do While Len(sPath)
- 'Debug.Print "--------------------"
- 'Debug.Print sPath
-
- '搜索文件和文件夹(无属性设置的)
- filename = Dir(sPath & "*.*", vbDirectory + vbNormal)
- Do While Len(filename)
- '跳过. 和 .. 文件夹
- If Not (filename = "." Or filename = "..") Then
- '判断是否为文件夹
- If (GetAttr(sPath & "" & filename) And vbDirectory) = 16 Then
- '避免读取错误
- If Err.Number <> 0 Then Err.Clear: GoTo End1If
- i = i + 1
- '把搜索到的子文件夹放入数组中
- ReDim Preserve arrPath(1 To i)
- arrPath(i) = sPath & filename & Application.PathSeparator
- Else
- If filename <> ThisWorkbook.Name And (Not LCase(filename) Like "*.xls") Then
- '在在此处加入针对文件处理的代码
- If s = 2 Then
- ReDim arr(1 To 23, 1 To 1)
- Else
- ReDim Preserve arr(1 To 23, 1 To UBound(arr, 2) + 1)
- End If
- s = s + 1
- Set fs = GetObject(sPath & filename)
- arr(1, s - 2) = s - 2
- arr(2, s - 2) = Mid(filename, 4, IIf(InStr(filename, "区") > 0, InStr(filename, "区") - 3, InStr(filename, "村") - 3))
- arr(3, s - 2) = fs.Sheets(1).[P2]
- arr(4, s - 2) = "=SUM(RC[1]+RC[2])"
- arr(5, s - 2) = Application.WorksheetFunction.CountIf(fs.Sheets(1).Range("Q6:Q10000"), "正常发放")
- arr(6, s - 2) = Application.WorksheetFunction.CountIf(fs.Sheets(1).Range("Q6:Q10000"), "死亡停发")
- arr(7, s - 2) = "=SUM(RC[1]+RC[4])"
- arr(8, s - 2) = Application.WorksheetFunction.SumIf(fs.Sheets(1).Range("Q6:Q10000"), "正常发放", fs.Sheets(1).Range("G6:G10000"))
- arr(9, s - 2) = Application.WorksheetFunction.SumIf(fs.Sheets(1).Range("Q6:Q10000"), "正常发放", fs.Sheets(1).Range("H6:H10000"))
- arr(10, s - 2) = Application.WorksheetFunction.SumIf(fs.Sheets(1).Range("Q6:Q10000"), "正常发放", fs.Sheets(1).Range("I6:I10000"))
- arr(11, s - 2) = Application.WorksheetFunction.SumIf(fs.Sheets(1).Range("Q6:Q10000"), "死亡停发", fs.Sheets(1).Range("J6:J10000"))
- arr(12, s - 2) = Application.WorksheetFunction.SumIf(fs.Sheets(1).Range("Q6:Q10000"), "死亡停发", fs.Sheets(1).Range("K6:K10000"))
- arr(13, s - 2) = Application.WorksheetFunction.SumIf(fs.Sheets(1).Range("Q6:Q10000"), "死亡停发", fs.Sheets(1).Range("L6:L10000"))
- arr(16, s - 2) = ""
- arr(17, s - 2) = ""
- arr(18, s - 2) = ""
- arr(19, s - 2) = ""
- arr(20, s - 2) = ""
- arr(21, s - 2) = ""
- arr(22, s - 2) = ""
- arr(23, s - 2) = ""
- With fs.Sheets(1)
- N1 = 0
- N2 = 0
- For Each Rng In .Range("d6:d" & .Cells(.Rows.Count, 4).End(3).Row)
- If Rng <> "" And (.Cells(Rng.Row, "Q").Value = "正常发放" Or .Cells(Rng.Row, "Q").Value = "死亡停发") Then
- Y = Val(IIf(Len(Rng.Text) = 18, Mid(Trim(Rng.Text), 7, 4), "19" & Mid(Trim(Rng.Text), 7, 2)))
- Select Case Y
- Case Is <= 1949
- N1 = N1 + 1
- Case Is <= 1952
- N2 = N2 + 1
- Case Else
- End Select
- End If
- Next Rng
- End With
- arr(14, s - 2) = Val(arr(14, s - 2)) + N1
- arr(15, s - 2) = Val(arr(15, s - 2)) + N2
- fs.Close False
- End If
- End If
- End If
- End1If:
- filename = Dir
- Loop
- j = j + 1
- If j > i Then Exit Do
- sPath = arrPath(j)
- Loop
- End Sub
复制代码 |
|