考勤机文本记录写入EXCEL
考勤机记录格式:
000971201202271838
000974201202271839
000982201202281849
000953201202281849
格式说明:
1~5 工号
6 (1=上班上班, 2=上午下班, 3=下午上班, 4=下午下班, 5=晚上上班, 6=晚上下班)
7~10 年
11~12 月
13~14 日
15~16 时
17~18 分
EXCEL 格式:
| 日期 | 员工编号 | 姓名 | 上午上班 | 上午下班 | 下午上班 | 下午下班 | 晚上上班 | 晚上下班 |
1 Private Sub CommandButton1_Click()
2 Dim TxtName As String
3 Dim fNum As Integer
4 Dim i, t, j, k, ap As Integer
5 Dim ep, st As String
6 Dim ad As Date
7
8 ap = 0
9 t = ActiveSheet.Range("A65535").End(xlUp).Row
10
11 fNum = FreeFile
12 TxtName = Application.GetOpenFilename("TEXT Files (*.TXT), *.TXT", 0, "Please select a Text File", , False)
13 Open TxtName For Input As #fNum
14
15 i = t + 1
16
17 Do While Not EOF(1)
18 Line Input #1, kqLine
19 ep = CStr(Mid(kqLine, 1, 5)) \'employee code
20 ad = CDate(Mid(kqLine, 7, 4) & "-" & Mid(kqLine, 11, 2) & "-" & Mid(kqLine, 13, 2)) \'date
21 st = Mid(kqLine, 15, 2) & ":" & Mid(kqLine, 17, 2) \'time
22
23 If i = 2 Then
24 Range("A" + Trim(Str(i))) = ad
25 Range("B" + Trim(Str(i))) = ep
26
27 If Mid(kqLine, 6, 1) = 1 Then
28 Range("D" + Trim(Str(i))) = st
29 End If
30
31 If Mid(kqLine, 6, 1) = 2 Then
32 Range("E" + Trim(Str(i))) = st
33 End If
34
35 If Mid(kqLine, 6, 1) = 3 Then
36 Range("F" + Trim(Str(i))) = st
37 End If
38
39 If Mid(kqLine, 6, 1) = 4 Then
40 Range("G" + Trim(Str(i))) = st
41 End If
42
43 If Mid(kqLine, 6, 1) = 5 Then
44 Range("H" + Trim(Str(i))) = st
45 End If
46
47 If Mid(kqLine, 6, 1) = 6 Then
48 Range("I" + Trim(Str(i))) = st
49 End If
50 i = i + 1
51 t = t + 1
52 Else
53 j = 2
54 Do While j <= t
55
56 If Range("A" + Trim(Str(j))) = ad And Range("B" + Trim(Str(j))) = ep Then
57 k = Range("A" + Trim(Str(j))).Row
58
59 If Mid(kqLine, 6, 1) = 1 Then
60 Range("D" + Trim(Str(k))) = st
61 End If
62
63 If Mid(kqLine, 6, 1) = 2 Then
64 Range("E" + Trim(Str(k))) = st
65 End If
66
67 If Mid(kqLine, 6, 1) = 3 Then
68 Range("F" + Trim(Str(k))) = st
69 End If
70
71 If Mid(kqLine, 6, 1) = 4 Then
72 Range("G" + Trim(Str(k))) = st
73 End If
74
75 If Mid(kqLine, 6, 1) = 5 Then
76 Range("H" + Trim(Str(k))) = st
77 End If
78
79 If Mid(kqLine, 6, 1) = 6 Then
80 Range("I" + Trim(Str(k))) = st
81 End If
82 ap = 1
83 Exit Do
84 Else
85 j = j + 1
86 ap = 0
87 End If
88 Loop
89
90 If ap = 0 Then
91
92 Range("A" + Trim(Str(i))) = ad
93 Range("B" + Trim(Str(i))) = ep
94
95 If Mid(kqLine, 6, 1) = 1 Then
96 Range("D" + Trim(Str(i))) = st
97 End If
98
99 If Mid(kqLine, 6, 1) = 2 Then
100 Range("E" + Trim(Str(i))) = st
101 End If
102
103 If Mid(kqLine, 6, 1) = 3 Then
104 Range("F" + Trim(Str(i))) = st
105 End If
106
107 If Mid(kqLine, 6, 1) = 4 Then
108 Range("G" + Trim(Str(i))) = st
109 End If
110
111 If Mid(kqLine, 6, 1) = 5 Then
112 Range("H" + Trim(Str(i))) = st
113 End If
114
115 If Mid(kqLine, 6, 1) = 6 Then
116 Range("I" + Trim(Str(i))) = st
117 End If
118 i = i + 1
119 t = t + 1
120 End If
121
122
123 End If
124
125 Loop
126
127 Close #fNum
128
129 End Sub
版权声明：本文为linmf原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。