度々申し訳ございません。
VBSを使用して、
「あるセルを参照し、そのセル内の値が既定の値であったら、別セルに固定の値を入力する」
という処理を実施しようとしているのですが、
処理を実行すると、
エラー:型が一致しません。:DateValue
とエラーが表示されます
文字列型と日付型の違いのためということはわかるのですが、
対処方法がよくわかりませんでした。
⇒csvの日付がyyyy/mm/dd形式のため、
(前段の処理でyyyy/mm/ddにしております)
これを再度日付型として認識させる方法を実現すればいいと思っているのですが、
その実現方法がわかりません。
教えてください
お分かりになられる方、
申し訳ございませんがご教授をお願いいたします
あるセルを参照:[offtime]列
既定の値:「23:59:59」
別セル:翌日の[ontime]列
固定の値:[0:00:00]
入力ファイル:csv_addlog_tmp3.csv
日付1,グループ,U_name,ontime,offtime,act時間
"2019/05/10","Aグループ","AAA","8:29:00","8:30:00","0:01:00"
"2019/05/10","Aグループ","BBB","8:49:00","8:51:00","0:01:00"
"2019/05/10","Aグループ","CCC","8:38:00","11:26:00","0:01:02"
"2019/05/09","Aグループ","AAA","8:28:00","17:04:00","0:01:00"
"2019/05/09","Aグループ","BBB","8:49:00","17:58:00","3:11:52"
"2019/05/09","Aグループ","CCC","8:36:00","19:21:00","0:01:00"
"2019/05/08","Aグループ","AAA","8:31:00","17:04:00","3:36:47"
"2019/05/08","Aグループ","BBB","8:48:00","23:59:59","0:01:00"
"2019/05/08","Aグループ","CCC","8:30:00","22:34:00","0:01:00"
"2019/05/05","Aグループ","BBB","8:51:00","19:10:00","0:01:00"
"2019/05/05","Aグループ","CCC","8:52:00","23:28:00","0:01:00"
"2019/05/04","Aグループ","AAA","8:25:00","17:12:00","1:07:52"
"2019/05/04","Aグループ","BBB","8:48:00","18:46:00","0:01:00"
"2019/05/04","Aグループ","CCC","8:48:00","22:49:00","0:01:00"
"2019/05/03","Aグループ","AAA","8:32:00","23:59:59","0:01:00"
"2019/05/03","Aグループ","BBB","9:01:00","18:13:00","0:01:00"
"2019/05/02","Aグループ","AAA","8:29:00","17:05:00","0:01:00"
"2019/05/02","Aグループ","BBB","9:09:00","18:34:00","0:01:00"
"2019/05/01","Aグループ","AAA","8:28:00","17:07:00","0:01:00"
"2019/05/01","Aグループ","BBB","8:53:00","19:07:00","0:01:00"
"2019/05/01","Aグループ","CCC","8:51:00","21:01:00","0:01:00"
出力ファイル(結果として出したい):csv_addlog_tmp.csv
⇒5/4のAAAと5/9のBBBのontimeの値が「0:00:00」になっている
日付1,グループ,U_name,ontime,offtime,act時間
"2019/05/10","Aグループ","AAA","8:29:00","8:30:00","0:01:00"
"2019/05/10","Aグループ","BBB","8:49:00","8:51:00","0:01:00"
"2019/05/10","Aグループ","CCC","8:38:00","11:26:00","0:01:02"
"2019/05/09","Aグループ","AAA","8:28:00","17:04:00","0:01:00"
"2019/05/09","Aグループ","BBB","0:00:00","17:58:00","3:11:52"
"2019/05/09","Aグループ","CCC","8:36:00","19:21:00","0:01:00"
"2019/05/08","Aグループ","AAA","8:31:00","17:04:00","3:36:47"
"2019/05/08","Aグループ","BBB","8:48:00","18:40:00","0:01:00"
"2019/05/08","Aグループ","CCC","8:30:00","22:34:00","0:01:00"
"2019/05/05","Aグループ","BBB","8:51:00","19:10:00","0:01:00"
"2019/05/05","Aグループ","CCC","8:52:00","23:28:00","0:01:00"
"2019/05/04","Aグループ","AAA","0:00:00","17:12:00","1:07:52"
"2019/05/04","Aグループ","BBB","8:48:00","18:46:00","0:01:00"
"2019/05/04","Aグループ","CCC","8:48:00","22:49:00","0:01:00"
"2019/05/03","Aグループ","AAA","8:32:00","17:00:00","0:01:00"
"2019/05/03","Aグループ","BBB","9:01:00","18:13:00","0:01:00"
"2019/05/02","Aグループ","AAA","8:29:00","17:05:00","0:01:00"
"2019/05/02","Aグループ","BBB","9:09:00","18:34:00","0:01:00"
"2019/05/01","Aグループ","AAA","8:28:00","17:07:00","0:01:00"
"2019/05/01","Aグループ","BBB","8:53:00","19:07:00","0:01:00"
"2019/05/01","Aグループ","CCC","8:51:00","21:01:00","0:01:00"
記載したコード
VBScript
1sub logon_check() 2 3Dim a, c, co, cr, d(), f, m, n, i, j, so, x, z(5) 4Set so = CreateObject("Scripting.FileSystemObject") 5f = so.GetParentFolderName(WScript.ScriptFullName) 6Set co = so.OpenTextFile(f & "\csv_addlog_tmp3.csv", 1) 7Set cr = so.OpenTextFile(f & "\csv_addlog_tmp4.csv", 2, True) 8 9'タイトル業種出力 10x = co.ReadLine 11cr.WriteLine x 12 13 14c = - 1 15 16'レコード数分実施 17Do Until co.AtEndOfStream 18 a = Split(co.ReadLine, ",") 19 c = c + 1 20 ReDim Preserve d(5, c) 21 For i = 0 to 5 22 d(i, c) = a(i) 23 Next 24 Loop 25co.Close 26 27Set co = Nothing 28 29 '[offtime]が「23:59:59」の行を選択 30For i = 0 to c 31 If d(4, i) = "23:59:59" Then 32 m = i 33 n = DateAdd("d", 1, d(0, i)) 34 End If 35 36 '翌日の[ontime]に「00:00:00」を投入 37 '表示形式が[h:mm:ss]なので、時刻は[0:00:00]で設定される 38For j = m + 1 to c 39 If n = DateValue(d(0, j)) and d(4, j) = "23:59:59" and d(2, j) = d(2, i) Then 40' If n = DateValue(d(0, j)) and DateValue(d(4, j)) = "23:59:59" and d(2, j) = d(2, i) Then 41' If n = d(0, j) and d(4, j) = "23:59:59" and d(2, j) = d(2, i) Then 42 d(3, j) = "0:00:00" 43 Exit For 44 End If 45 Next 46Next 47 For i = 0 to c 48 For j = 0 to 5 49 z(j) = d(j, i) 50 Next 51 cr.WriteLine Join(z, ",") 52 Next 53cr.Close 54Set cr = Nothing 55Set so = Nothing 56 57'バッファを Flush してファイルを閉じる 58Set dic = Nothing 59 60end sub
入力ファイル(csv_addlog_tmp3.csv)は、
objADO.Execute _
"SELECT Format([日付],'yyyy/mm/dd') AS 日付1, Format([offtime],'h:nn:ss') as [OFF1] , エージェント , 操作種別 , 端末機No , 部署名 , Format([ontime],'h:nn:ss') as [ON1] " & _
" INTO ~~~
で、作成しています
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2019/05/10 09:31