度々申し訳ございません。
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
sub logon_check() Dim a, c, co, cr, d(), f, m, n, i, j, so, x, z(5) Set so = CreateObject("Scripting.FileSystemObject") f = so.GetParentFolderName(WScript.ScriptFullName) Set co = so.OpenTextFile(f & "\csv_addlog_tmp3.csv", 1) Set cr = so.OpenTextFile(f & "\csv_addlog_tmp4.csv", 2, True) 'タイトル業種出力 x = co.ReadLine cr.WriteLine x c = - 1 'レコード数分実施 Do Until co.AtEndOfStream a = Split(co.ReadLine, ",") c = c + 1 ReDim Preserve d(5, c) For i = 0 to 5 d(i, c) = a(i) Next Loop co.Close Set co = Nothing '[offtime]が「23:59:59」の行を選択 For i = 0 to c If d(4, i) = "23:59:59" Then m = i n = DateAdd("d", 1, d(0, i)) End If '翌日の[ontime]に「00:00:00」を投入 '表示形式が[h:mm:ss]なので、時刻は[0:00:00]で設定される For j = m + 1 to c If n = DateValue(d(0, j)) and d(4, j) = "23:59:59" and d(2, j) = d(2, i) Then ' If n = DateValue(d(0, j)) and DateValue(d(4, j)) = "23:59:59" and d(2, j) = d(2, i) Then ' If n = d(0, j) and d(4, j) = "23:59:59" and d(2, j) = d(2, i) Then d(3, j) = "0:00:00" Exit For End If Next Next For i = 0 to c For j = 0 to 5 z(j) = d(j, i) Next cr.WriteLine Join(z, ",") Next cr.Close Set cr = Nothing Set so = Nothing 'バッファを Flush してファイルを閉じる Set dic = Nothing end 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 ~~~
で、作成しています
まだ回答がついていません
会員登録して回答してみよう
