· 5 years ago · Feb 13, 2020, 06:30 AM
1Create monthly Reports
2
3Private Sub 集計_Click()
4
5 If 日付1 = "" And 日付2 = "" Then
6 MsgBox "出力の期間を選択してください。", vbOKOnly, "エラー"
7
8 Else
9
10 'change 工数 from 2.30 (2時間30分) into 2.5
11 DoCmd.RunSQL "SELECT 工数台帳1.日付, 工数台帳1.個人コード, 工数台帳1.作業, CDbl(Format(CDbl(Right(Format([工数],'Fixed'),2))/60+CDbl(Left(Format([工数],'Fixed'),1)),'Fixed')) as 総工数 into 工数台帳修正 FROM 工数台帳1;"
12 DoCmd.SetWarnings False
13
14 '-------------------create 工数データ according to 作業名------------------
15 '-------------------------------------------------------------------------------------
16
17 'LASSA
18 DoCmd.RunSQL "INSERT INTO データ元 ( 日付, 事業所番号, 件数 ) SELECT X.受付日, X.事業所№, Count(X.社員番号) AS 社員番号のカウント FROM 取得台帳取込シート AS X WHERE ((X.受付日 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY X.受付日, X.事業所№;"
19 DoCmd.SetWarnings False
20
21 DoCmd.RunSQL "INSERT INTO データ元 ( 日付, 事業所番号, 件数 ) SELECT X.受付日, X.事業所№, Count(X.社員番号) AS 社員番号のカウント FROM 喪失台帳取込シート AS X WHERE ((X.受付日 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY X.受付日, X.事業所№;"
22 DoCmd.SetWarnings False
23
24 DoCmd.RunSQL "update データ元 set 作業名 = '5.Lassa' where 作業名 is null"
25 DoCmd.SetWarnings False
26
27 '住所変更
28 Dim searchDate As Date, searchDate1 As Date
29 Dim strFilePath As String, strFilePath1 As String
30 Dim strFile As String, strFile1 As String
31 Dim a As Long
32
33
34 searchDate = 日付2
35 searchDate1 = DateSerial(Year(searchDate), Month(searchDate) - 1, 1)
36
37 strFilePath = "\\192.168.131.222\東川f\Excel取込_住所変更\住所変更管理\" & Year(searchDate) & "\住所変更管理ファイル _" & Month(searchDate) & "月.xlsx"
38 strFilePath1 = "\\192.168.131.222\東川f\Excel取込_住所変更\住所変更管理\" & Year(searchDate1) & "\住所変更管理ファイル _" & Month(searchDate1) & "月.xlsx"
39 Debug.Print "Looking for: " & strFilePath 'Check if file exists
40 Debug.Print "Looking for: " & strFilePath1
41
42 strFile = Dir(strFilePath)
43 strFile1 = Dir(strFilePath1) 'Note that Dir() only returns the file name, so use strFilePath
44
45 If strFile <> "" And strFile1 <> "" Then
46
47 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "住所変更1", strFilePath, True, "Sheet1!A:L"
48 DoCmd.SetWarnings False
49 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "住所変更2", strFilePath1, True, "Sheet1!A:L"
50 DoCmd.SetWarnings False
51
52 DoCmd.RunSQL "INSERT INTO データ元 ( 日付, 事業所番号, 件数 ) SELECT X.CD作成済み日, CDbl(Left([事業],InStr([事業],':')-1)), Sum(X.件数) FROM 住所変更1 AS X WHERE ((X.CD作成済み日 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY CDbl(Left([事業],InStr([事業],':')-1)), X.CD作成済み日, X.進度 HAVING (((X.進度)='完了'));"
53 DoCmd.SetWarnings False
54 DoCmd.RunSQL "INSERT INTO データ元 ( 日付, 事業所番号, 件数 ) SELECT X.CD作成済み日, CDbl(Left([事業],InStr([事業],':')-1)), Sum(X.件数) FROM 住所変更2 AS X WHERE ((X.CD作成済み日 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY CDbl(Left([事業],InStr([事業],':')-1)), X.CD作成済み日, X.進度 HAVING (((X.進度)='完了'));"
55 DoCmd.SetWarnings False
56 DoCmd.RunSQL "update データ元 set 作業名 = '7.住所変更' where 作業名 is null"
57 DoCmd.SetWarnings False
58 End If
59
60 '整合チェック
61 DoCmd.RunSQL "INSERT INTO データ元 ( 日付, 事業所番号, 件数 ) SELECT dateserial(left(X.ZESADLDATE,4),mid(X.ZESADLDATE,5,2),right(X.ZESADLDATE,2)), X.BKBJINO, Count(X.PersonalCode) FROM _被保険者 as X WHERE ((dateserial(left(X.ZESADLDATE,4),mid(X.ZESADLDATE,5,2),right(X.ZESADLDATE,2)) Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY dateserial(left(X.ZESADLDATE,4),mid(X.ZESADLDATE,5,2),right(X.ZESADLDATE,2)), X.BKBJINO;"
62 DoCmd.SetWarnings False
63 DoCmd.RunSQL "update データ元 set 作業名 = '2.整合チェック' where 作業名 is null"
64 DoCmd.SetWarnings False
65
66 '発送業務
67 DoCmd.RunSQL "INSERT INTO データ元 ( 日付, 事業所番号, 件数 ) SELECT ZipFileList.StorageDate, ZipFileList.BKBJINO, Count(ZipFileList.BKBSYONO) FROM ZipFileList WHERE ((ZipFileList.StorageDate Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY ZipFileList.StorageDate, ZipFileList.BKBJINO;"
68 DoCmd.SetWarnings False
69 DoCmd.RunSQL "update データ元 set 作業名 = '6.発送業務' where 作業名 is null"
70 DoCmd.SetWarnings False
71
72 'エントリ 西友
73 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "エントリ", "\\172.18.0.16\札幌Factory\10_管理資料\チームミーティング\Team西友\東川ファクトリー業務移管用\テスト用PDFファイル格納\西友エントリー件数new.xlsx", True, "Sheet1!B:H"
74 DoCmd.SetWarnings False
75
76 DoCmd.RunSQL "INSERT INTO データ元 ( 日付, 件数 ) SELECT エントリ種別, 合計西友 FROM エントリ as X WHERE ((エントリ種別 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY エントリ種別, 合計西友 HAVING (((X.合計西友) Is Not Null));"
77 DoCmd.SetWarnings False
78 DoCmd.RunSQL "update データ元 set 作業名 = '4.エントリ 西友', 事業所番号 = '449' where 作業名 is null and 事業所番号 = 0;"
79 DoCmd.SetWarnings False
80
81 'エントリ ブックオフ
82 DoCmd.RunSQL "INSERT INTO データ元 ( 日付, 件数 ) SELECT エントリ種別, 合計BOOKOFF FROM エントリ as X WHERE ((エントリ種別 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY エントリ種別, 合計BOOKOFF HAVING (((X.合計BOOKOFF) Is Not Null));"
83 DoCmd.SetWarnings False
84 DoCmd.RunSQL "update データ元 set 作業名 = '3.エントリ ブックオフ', 事業所番号 = '982' where 作業名 is null and 事業所番号 = 0;"
85 DoCmd.SetWarnings False
86
87
88 '健保チェック
89 Dim StorageDate As String, strFromDirectory As String
90 Set FSO = CreateObject("Scripting.FileSystemObject")
91 Set objShell = CreateObject("Shell.Application")
92
93 strFromDirectory = "\\192.168.140.33\東川ファクトリーssr\健康保険決定通知チェック20171220～\"
94 Call ImportFileSub(strFromDirectory)
95
96 DoCmd.RunSQL "INSERT INTO データ元 ( 日付, 事業所番号, 件数 ) SELECT X.日付, X.事業所番号, Count(X.個人コード) FROM 健保チェック AS X GROUP BY X.日付, X.事業所番号;"
97 DoCmd.SetWarnings False
98 DoCmd.RunSQL "update データ元 set 作業名 = '9.健康保険決定通知チェック' where 作業名 is null;"
99 DoCmd.SetWarnings False
100
101
102 '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
103 '-----------------------------------------------------------------------------
104 '-------------------------------------------------------------------------------------
105 Dim worksheetData As DBArray
106 Set worksheetData = New DBArray
107 Call worksheetData.LoadFromQuery("SELECT * FROM 工数台帳修正")
108
109 Dim result As DBArray
110 Dim totalHoursInJob As String
111 Set result = New DBArray
112 Call result.Constructor
113 Call result.AddHeader("日付")
114 Call result.AddHeader("個人コード")
115 Call result.AddHeader("事業所番号")
116 Call result.AddHeader("工数")
117
118 Dim i As Integer
119 For i = 0 To worksheetData.rows - 1
120 Dim wDate As Date
121 wDate = worksheetData.At(i, 0)
122
123 Dim wEmployee As String
124 wEmployee = worksheetData.At(i, 1)
125
126 Dim wJob As String
127 wJob = worksheetData.At(i, 2)
128
129 Dim wTime As String
130 wTime = worksheetData.At(i, 3)
131
132
133 Dim jobData As DBArray
134 Set jobData = New DBArray
135 If wJob <> "1.Programing" And wJob <> "10.36協定" And wJob <> "8.マクドナルド（外国人人事）" And wJob <> "11.個別発送 西友" And wJob <> "12.個別発送 日本マクドナルド" And wJob <> "13.個別発送 AXA生命保険" And wJob <> "14.個別発送 ロイヤルホスト" Then
136 Call jobData.LoadFromQuery("SELECT SUM(件数) FROM データ元 WHERE 日付=#" & wDate & "# AND 作業名='" & wJob & "'")
137 totalHoursInJob = jobData.At(0, 0)
138
139 Dim companyData As DBArray
140 Set companyData = New DBArray
141 Call companyData.LoadFromQuery("SELECT 件数, 事業所番号 FROM データ元 WHERE 日付=#" & wDate & "# AND 作業名='" & wJob & "'")
142
143
144 Dim j As Integer
145 For j = 0 To companyData.rows - 1
146
147 Dim companyTime As Double
148 companyTime = companyData.At(j, 0)
149
150 Dim companyName As String
151 companyName = companyData.At(j, 1)
152
153 Dim finalTime As Double
154 finalTime = Round(wTime * companyTime / totalHoursInJob, 2)
155
156 Dim row As Object
157 Set row = CreateArrayList()
158 row.Add (wDate)
159 row.Add (wEmployee)
160 row.Add (companyName)
161 row.Add (finalTime)
162 Call result.AddRow(row)
163 Next j
164 End If
165 Next i
166 result.CreateTable ("Result")
167
168 DoCmd.RunSQL "SELECT Result.個人コード, Result.事業所番号, Sum(Result.工数) AS 総工数 into 集計 FROM result GROUP BY Result.個人コード, Result.事業所番号"
169 DoCmd.SetWarnings False
170
171
172 'programming
173 DoCmd.RunSQL "SELECT 工数台帳修正.個人コード, Sum(工数台帳修正.総工数) AS 総工数 into ProgramingTotal FROM 工数台帳修正 WHERE ((工数台帳修正.日付 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY 工数台帳修正.個人コード, 工数台帳修正.作業 HAVING (((工数台帳修正.作業)='1.Programing'));"
174 DoCmd.SetWarnings False
175 DoCmd.RunSQL "alter table ProgramingTotal add column 事業所 text(15);"
176
177 Dim db As DAO.Database
178 Dim tbl As DAO.TableDef
179 Dim fld As DAO.Field
180
181 Set db = CurrentDb
182 Set tbl = db.TableDefs("ProgramingTotal")
183
184
185 For Each fld In tbl.Fields
186 Debug.Print fld.Name, fld.OrdinalPosition
187 Next
188
189 tbl.Fields("事業所").OrdinalPosition = 0
190 tbl.Fields("個人コード").OrdinalPosition = 1
191 tbl.Fields("総工数").OrdinalPosition = 2
192 tbl.Fields.Refresh
193 db.Close
194 Set db = Nothing
195 DoCmd.RunSQL "update ProgramingTotal set 事業所 = '111111' where 事業所 is null"
196 DoCmd.SetWarnings False
197 DoCmd.RunSQL "INSERT INTO 集計 (個人コード, 事業所番号, 総工数 ) SELECT 個人コード, 事業所, 総工数 FROM ProgramingTotal"
198 DoCmd.Close acTable, "ProgramingTotal"
199 DoCmd.DeleteObject acTable, "ProgramingTotal"
200
201
202 '36協定
203 DoCmd.RunSQL "SELECT 工数台帳修正.個人コード, Sum(工数台帳修正.総工数) AS 総工数 into 36協定Total FROM 工数台帳修正 WHERE ((工数台帳修正.日付 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY 工数台帳修正.個人コード, 工数台帳修正.作業 HAVING (((工数台帳修正.作業)='10.36協定'));"
204 DoCmd.SetWarnings False
205 DoCmd.RunSQL "alter table 36協定Total add column 事業所 text(30);"
206
207
208 Set db = CurrentDb
209 Set tbl = db.TableDefs("36協定Total")
210
211
212 For Each fld In tbl.Fields
213 Debug.Print fld.Name, fld.OrdinalPosition
214 Next
215
216 tbl.Fields("事業所").OrdinalPosition = 0
217 tbl.Fields("個人コード").OrdinalPosition = 1
218 tbl.Fields("総工数").OrdinalPosition = 2
219 tbl.Fields.Refresh
220 db.Close
221 Set db = Nothing
222 DoCmd.RunSQL "update 36協定Total set 事業所 = '222222' where 事業所 is null"
223 DoCmd.SetWarnings False
224
225 DoCmd.RunSQL "INSERT INTO 集計 (個人コード, 事業所番号, 総工数 ) SELECT 個人コード, 事業所, 総工数 FROM 36協定Total"
226 DoCmd.Close acTable, "36協定Total"
227 DoCmd.DeleteObject acTable, "36協定Total"
228
229 '外国人人事
230 DoCmd.RunSQL "SELECT 工数台帳修正.個人コード, Sum(工数台帳修正.総工数) AS 総工数 into 外国人人事Total FROM 工数台帳修正 WHERE ((工数台帳修正.日付 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY 工数台帳修正.個人コード, 工数台帳修正.作業 HAVING (((工数台帳修正.作業)='8.マクドナルド（外国人人事)'));"
231 DoCmd.SetWarnings False
232 DoCmd.RunSQL "alter table 外国人人事Total add column 事業所 text(15);"
233 DoCmd.RunSQL "update 外国人人事Total set 事業所 = '50001' where 事業所 is null"
234 DoCmd.SetWarnings False
235 DoCmd.RunSQL "INSERT INTO 集計 (個人コード, 事業所番号, 総工数 ) SELECT 個人コード, 事業所, 総工数 FROM 外国人人事Total"
236 DoCmd.Close acTable, "外国人人事Total"
237 DoCmd.DeleteObject acTable, "外国人人事Total"
238
239 '個別発送
240 '個別発送 西友
241 DoCmd.RunSQL "SELECT 工数台帳修正.個人コード, Sum(工数台帳修正.総工数) AS 総工数 into 個別発送1 FROM 工数台帳修正 WHERE ((工数台帳修正.日付 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY 工数台帳修正.個人コード, 工数台帳修正.作業 HAVING (((工数台帳修正.作業)='11.個別発送 西友'));"
242 DoCmd.SetWarnings False
243 DoCmd.RunSQL "alter table 個別発送1 add column 事業所 text(15);"
244 DoCmd.RunSQL "update 個別発送1 set 事業所 = '449' where 事業所 is null"
245 DoCmd.SetWarnings False
246 DoCmd.RunSQL "INSERT INTO 集計 (個人コード, 事業所番号, 総工数 ) SELECT 個人コード, 事業所, 総工数 FROM 個別発送1"
247 DoCmd.Close acTable, "個別発送1"
248 DoCmd.DeleteObject acTable, "個別発送1"
249
250 '個別発送 日本マクドナルド
251 DoCmd.RunSQL "SELECT 工数台帳修正.個人コード, Sum(工数台帳修正.総工数) AS 総工数 into 個別発送1 FROM 工数台帳修正 WHERE ((工数台帳修正.日付 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY 工数台帳修正.個人コード, 工数台帳修正.作業 HAVING (((工数台帳修正.作業)='12.個別発送 日本マクドナルド'));"
252 DoCmd.SetWarnings False
253 DoCmd.RunSQL "alter table 個別発送1 add column 事業所 text(15);"
254 DoCmd.RunSQL "update 個別発送1 set 事業所 = '50001' where 事業所 is null"
255 DoCmd.SetWarnings False
256 DoCmd.RunSQL "INSERT INTO 集計 (個人コード, 事業所番号, 総工数 ) SELECT 個人コード, 事業所, 総工数 FROM 個別発送1"
257 DoCmd.Close acTable, "個別発送1"
258 DoCmd.DeleteObject acTable, "個別発送1"
259
260 '個別発送 AXA生命保険
261 DoCmd.RunSQL "SELECT 工数台帳修正.個人コード, Sum(工数台帳修正.総工数) AS 総工数 into 個別発送1 FROM 工数台帳修正 WHERE ((工数台帳修正.日付 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY 工数台帳修正.個人コード, 工数台帳修正.作業 HAVING (((工数台帳修正.作業)='13.個別発送 AXA生命保険'));"
262 DoCmd.SetWarnings False
263 DoCmd.RunSQL "alter table 個別発送1 add column 事業所 text(15);"
264 DoCmd.RunSQL "update 個別発送1 set 事業所 = '260' where 事業所 is null"
265 DoCmd.SetWarnings False
266 DoCmd.RunSQL "INSERT INTO 集計 (個人コード, 事業所番号, 総工数 ) SELECT 個人コード, 事業所, 総工数 FROM 個別発送1"
267 DoCmd.Close acTable, "個別発送1"
268 DoCmd.DeleteObject acTable, "個別発送1"
269
270 '個別発送 ロイヤルホスト
271 DoCmd.RunSQL "SELECT 工数台帳修正.個人コード, Sum(工数台帳修正.総工数) AS 総工数 into 個別発送1 FROM 工数台帳修正 WHERE ((工数台帳修正.日付 Between #" & 日付1 & "# And #" & 日付2 & "#)) GROUP BY 工数台帳修正.個人コード, 工数台帳修正.作業 HAVING (((工数台帳修正.作業)='14.個別発送 ロイヤルホスト'));"
272 DoCmd.SetWarnings False
273 DoCmd.RunSQL "alter table 個別発送1 add column 事業所 text(15);"
274 DoCmd.RunSQL "update 個別発送1 set 事業所 = '349' where 事業所 is null"
275 DoCmd.SetWarnings False
276 DoCmd.RunSQL "INSERT INTO 集計 (個人コード, 事業所番号, 総工数 ) SELECT 個人コード, 事業所, 総工数 FROM 個別発送1"
277 DoCmd.Close acTable, "個別発送1"
278 DoCmd.DeleteObject acTable, "個別発送1"
279
280 DoCmd.RunSQL "SELECT 集計.個人コード, 集計.事業所番号, Sum(集計.総工数) AS 総工数 into 集計1 FROM 集計 GROUP BY 集計.個人コード, 集計.事業所番号"
281 DoCmd.SetWarnings False
282
283
284 'create privottable
285 Dim qdf As DAO.QueryDef
286 Dim strQuery As String
287 Dim strReportName As String
288 Dim rs As DAO.recordSet 'shortens record set call
289 Dim strRecordSetSQL As String 'record set string to pass to SQL
290 Dim strPath As String
291 Dim objFolder1 As Object
292
293 Set db = CurrentDb 'sets db[DAO.Dataset] to current data base
294
295 strQuery = "TRANSFORM Sum(集計1.総工数) SELECT 集計1.事業所番号 FROM 集計1 GROUP BY 集計1.事業所番号 ORDER BY 集計1.個人コード PIVOT 集計1.個人コード;" 'create Qry String
296
297 CurrentDb.QueryDefs("crosstable").SQL = strQuery 'Write String to Query
298 DoCmd.RunSQL "select * into Privottable from crosstable"
299 DoCmd.SetWarnings False
300 db.Close
301 Set db = Nothing
302
303 'change 事業所番号 into internationalcode
304 DoCmd.RunSQL "SELECT Privottable.事業所番号, 換算.InsName, 換算.InsNoInternal INTO converttable FROM Privottable INNER JOIN 換算 ON cdbl(Privottable.事業所番号) = 換算.InsNo;"
305 DoCmd.SetWarnings False
306
307 DoCmd.RunSQL "SELECT converttable.InsNoInternal, converttable.InsName, Privottable.* INTO 換算済みの集計 FROM Privottable INNER JOIN converttable ON Privottable.事業所番号 = converttable.事業所番号;"
308 DoCmd.SetWarnings False
309
310
311 'export to final report table
312 DoCmd.Close acTable, "最終集計"
313 DoCmd.DeleteObject acTable, "最終集計"
314 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "最終集計", "\\192.168.131.222\東川f\作業工数管理\KOT勤退\集計\集計のフォーマット.xlsx", True, "Sheet1!A:AV" 'フィールドの範囲を再確認
315 DoCmd.SetWarnings False
316
317
318 DoCmd.RunSQL "UPDATE 最終集計 AS X LEFT JOIN 換算済みの集計 AS Y ON (X.InsName = Y.InsName) SET X.2020049 = Y.[2020049], X.2020050 = Y.[2020050], X.2020052 = Y.[2020052], X.2020053 = Y.[2020053], X.2020056 = Y.[2020056], X.2020057 = Y.[2020057], X.2020061 = Y.[2020061], X.2020063 = Y.[2020063], X.2020065 = Y.[2020065], X.2020066 = Y.[2020066], X.2020068 = Y.[2020068], X.2020071 = Y.[2020071], X.2020072 = Y.[2020072], X.2020074 = Y.[2020074], X.2020078 = Y.[2020078], X.2020087 = Y.[2020087], X.2020088 = Y.[2020088], X.2020089 = Y.[2020089], X.2020090 = Y.[2020090], X.2020097 = Y.[2020097], X.2020098 = Y.[2020098], X.2020099 = Y.[2020099];"
319 'DoCmd.RunSQL "UPDATE 最終集計 AS X LEFT JOIN 換算済みの集計 AS Y ON (X.InsName = Y.InsName) AND (X.InsNoInternal = Y.InsNoInternal) SET X.2020049 = Y.[2020049], X.2020050 = Y.[2020050], X.2020052 = Y.[2020052], X.2020053 = Y.[2020053], X.2020056 = Y.[2020056], X.2020057 = Y.[2020057], X.2020061 = Y.[2020061], X.2020063 = Y.[2020063], X.2020065 = Y.[2020065], X.2020066 = Y.[2020066], X.2020068 = Y.[2020068], X.2020071 = Y.[2020071], X.2020072 = Y.[2020072], X.2020074 = Y.[2020074], X.2020078 = Y.[2020078], X.2020087 = Y.[2020087], X.2020088 = Y.[2020088], X.2020089 = Y.[2020089], X.2020090 = Y.[2020090], X.2020097 = Y.[2020097], X.2020098 = Y.[2020098], X.2020099 = Y.[2020099];"
320 ''''''''''this code needs revising'''''''''''''
321 DoCmd.SetWarnings False
322
323
324 Dim fileName As String
325 fileName = "\\192.168.131.222\東川f\作業工数管理\KOT勤退\集計\" & "集計データ" & Month(日付1) & "月.xlsx"
326 DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="最終集計", fileName:=fileName, HasFieldNames:=True
327 Report_Creation (fileName)
328
329
330
331 DoCmd.Close acTable, "集計"
332 DoCmd.DeleteObject acTable, "集計"
333 DoCmd.Close acTable, "集計1"
334 DoCmd.DeleteObject acTable, "集計1"
335 DoCmd.Close acTable, "Result"
336 DoCmd.DeleteObject acTable, "Result"
337 DoCmd.Close acTable, "工数台帳修正"
338 DoCmd.DeleteObject acTable, "工数台帳修正"
339 DoCmd.Close acTable, "住所変更1"
340 DoCmd.DeleteObject acTable, "住所変更1"
341 DoCmd.Close acTable, "住所変更2"
342 DoCmd.DeleteObject acTable, "住所変更2"
343 DoCmd.Close acTable, "エントリ"
344 DoCmd.DeleteObject acTable, "エントリ"
345 DoCmd.Close acTable, "データ元"
346 DoCmd.RunSQL "delete * from データ元;"
347 DoCmd.SetWarnings False
348 DoCmd.SetWarnings False
349 DoCmd.SetWarnings False
350 DoCmd.Close acTable, "健保チェック"
351 DoCmd.RunSQL "delete * from 健保チェック;"
352 DoCmd.SetWarnings False
353 DoCmd.SetWarnings False
354 DoCmd.SetWarnings False
355 DoCmd.Close acTable, "換算済みの集計"
356 DoCmd.DeleteObject acTable, "換算済みの集計"
357 DoCmd.Close acTable, "converttable"
358 DoCmd.DeleteObject acTable, "converttable"
359 DoCmd.Close acTable, "Privottable"
360 DoCmd.DeleteObject acTable, "Privottable"
361
362
363 MsgBox "集計が正常に完了しました。", vbOKOnly, "完成"
364 End If
365
366
367End Sub
368
369
370'個別発送
371 'Dim filelink As String
372 'filelink = "\\192.168.131.222\東川発送業務\札幌\" & "個別発送集計" & Year(日付1) & "年.xlsx"
373 'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "個別発送", filelink, True, "Sheet1!A:O"
374 'DoCmd.SetWarnings False
375
376 'Dim 個人発送 As DBArray
377 'Dim col As String
378 'Dim k As Integer
379 'Set 個人発送 = New DBArray
380 'Call 個人発送.LoadFromQuery("SELECT * from 個別発送;")
381
382 'Dim result As DBArray
383 'Set result = New DBArray
384 'Call result.Constructor
385 'Call result.AddHeader("作業名")
386 'Call result.AddHeader("事業所番号")
387 'Call result.AddHeader("件数")
388
389 'For k = 0 To 個人発送.rows - 1
390 'Dim 作業名 As String
391 '作業名 = 個人発送.At(k, 2)
392
393 'Dim 事業所番号 As Long
394 '事業所番号 = 個人発送.At(k, 0)
395
396 'Dim 件数 As Integer
397 'Dim l As Integer
398 'l = Month(日付1)
399 '件数 = 個人発送.At(k, l + 2)
400
401
402 'Dim row1 As Object
403 'Set row1 = CreateArrayList()
404 'row1.Add (作業名)
405 'row1.Add (事業所番号)
406 'row1.Add (件数)
407 'Call result.AddRow(row1)
408
409 'Next
410 'result.CreateTable ("個別発送修正")
411 'DoCmd.RunSQL "select distinct * into 1234 from 個別発送修正"
412
413 'DoCmd.RunSQL "INSERT INTO データ元 (作業名, 事業所番号, 件数 ) SELECT 作業名, 事業所番号, 件数 FROM 1234;"
414 'DoCmd.SetWarnings False
415
416 'DoCmd.Close acTable, "個別発送修正"
417 'DoCmd.DeleteObject acTable, "個別発送修正"
418 'DoCmd.Close acTable, "1234"
419 'DoCmd.DeleteObject acTable, "1234"
420
421
422 '外国人人事
423 'Dim SourceTableName1, SourceTableName2 As String
424 'Dim DestinationTableName1, DestinationTableName2 As String
425 'SourceTableName1 = Format(日付1, "yyyymm") & "採用"
426 'SourceTableName2 = Format(日付1, "yyyymm") & "離職"
427 'DestinationTableName1 = "外国人人事_採用"
428 'DestinationTableName2 = "外国人人事_離職"
429
430 'DoCmd.TransferDatabase acImport, "Microsoft Access", "\\192.168.131.222\東川f\マクドナルド\外国人人事マスタ届出.accdb", acTable, SourceTableName1, DestinationTableName1, False
431 'DoCmd.TransferDatabase acImport, "Microsoft Access", "\\192.168.131.222\東川f\マクドナルド\外国人人事マスタ届出.accdb", acTable, SourceTableName2, DestinationTableName2, False
432
433 'Dim 件数1 As Integer
434 '件数1 = DCount("*", "外国人人事_採用") + DCount("*", "外国人人事_離職")
435 'DoCmd.RunSQL "insert into データ元(日付, 作業名, 事業所番号, 件数) values ('','8.マクドナルド（外国人人事)',50001," & 件数1 & ");"