Note: Fill in the storage path of the monthly report in column B of the path table (complete path name, such as C: \ Daily Report \ 201201); Enter the data 1 or 0(0 is optional) in column A, and 1 indicates that the corresponding path is selected. At run time, all reports in the selected path will be summarized. If column A is greater than 1, all the selected paths will be counted (it is convenient to count daily reports for multiple months at one time).
-the processing process will summarize everyone's data, and personnel changes will not be affected.
—— The name column of the daily report cannot be blank, otherwise the columns below the blank line will not be counted.
Sub summary personnel data ()' gets daily files from the monthly path and merges them into the current file summary table.
Set s = CreateObject ("script. File system object ")
gjz = "*。 xls "
Set hm = ThisWorkbook。 Worksheet ("Summary Table"). Range ("A2")
Set ahm = hm. Range (cell (1, 1), cell (10000, 1))
x = 0
For each c in this workbook. Worksheet ("Path Table"). Range ("a2:a20")
If Len(c.Value) > then 0
If C. value = 1, then
lj = c.Offset(0， 1)。 value
If it is correct (lj,1) < & gt "So lj = lj & amp"\"
Set d = s.GetFolder(lj)
Set f = d.Files
For each k in f', file after file.
Bnm = K. name
fl = lj + bnm
Apply. StatusBar = "Open file:" +bnm
Go to error handling when an error occurs.
Workbooks. Open fl
Go to 0 when an error occurs.
Apply. StatusBar = False
v = 0
For each sht(bnm) in the workbook. worksheet
Set om = sht. Range ("b2")
If om. Value = "name "and then
For i = 1 to 10000.
If Len(om. Offset (i+ 1, 0). Value) = 0, and then exit.
aa = om。 Offset (i+ 1, 0). value
Go to et when an error occurs.
T = worksheet function. Match (aa, ahm, 0)
Go to 0 when an error occurs.
If v = 1
v = 0
Hmm. Offset (x, 0). Value = aa
Hmm. Offset (x, 1). Value = om. Offset (i+ 1, 6). value
Hmm. Offset (x, 2). Value = om. Offset (i+ 1, 7). value
Hmm. Offset (x, 3). Value = 1
x = x + 1
other
v = 0
Hmm. Offset (t-1, 4). Value = aa
Hmm. Offset (t-1, 1). Value = hm. Offset (t-1, 1). Value+om. Offset (i+ 1, 6). value
Hmm. Offset (t-1, 2). Value = hm. Offset (t-1, 2). Value+om. Offset (i+ 1, 7). value
Hmm. Offset (t-1, 3). Value = hm. Offset (t-1, 3). Value+1
If ... it will be over.
Next, I
If ... it will be over.
next time
ActiveWorkbook。 Closed leave
Next k
If ... it will be over.
If ... it will be over.
Next C.
Set om = none
Set f = none.
Set d = none.
Set ahm = none.
Set hm = none.
Set s = none.
outlet connection
Error handling:
Workbook (bnm). Make active
Continue next.
outlet connection
et:
v = 1
Continue next.
End joint