VBAでCSVに出力する。 ～ログの残し方～

今回はCSVデータとして一か所にそれぞれのユーザーID名で保存するようにしたいと思います。
重複することは無いでしょうし、一か所のフォルダにまとめて置けば
人が複数人いてもまとめてデータの取得ができるのではないかということで。

■やりたいこと
「CSVに出力する」

■対策
データを扱う上で過去困った経験として、
日付:yyyy/mm/dd
時間:hh:mm:ss
で扱っていきたい。
並べ替えをしたときに1月10月11月12月2月3月…とか
1:00 10:00 11:00 12:00 ~ 19:00 2:00 20:00 …とか
並べ替えを設定すればよいのでしょうけどもBIツールの各チャートは都度設定しないとだめだったり、同じ設定を使いまわしたくても一つ変えると他に影響したり。
話がそれてしまいました。
あと、テキスト形式で " と , を含むもの。
今回自分で更新ログをいっぱい取ったときに関数の内容も見れるようにしていたら
Format(Date, "yyyy/mm/dd")
などの情報がカンマで分割されてしまったり、回避で""""で対処したら
Format(Date, yyyy/mm/dd")"
とで、CSVにして保存するときにダブルコーテーションが存在する数によって
Replaceで""""を変換したりと、シンプルに作ろうとしておりましたが
思っていたより長くなってしまいました。


さて、まずは一か所のフォルダにまとめるために
Function　で使いまわしたいと思います。
そうすると、格納する階層を決め打ちする必要があります。運用は計画的に。
今回は　
With ThisWorkbook
 **= Left(.Path, InStrRev(.Path, "\") - 1)
End With
としてさらに
** & "格納するフォルダ名" & "ユーザーID" & ".csv"
格納するフォルダはいずれ用途によって変更できるように対策しました。
ColumnごとにFormatを指定して、Printで書き込む感じです。

■課題
日付はダブルクォーテーションで加工必要があるのか。
CSVを利用する側のエクセル以外の人たちの要望を私は知らない…。
結局私はVBAで取り込んでこの後も作業するのでどこで影響がでるのでしょうかね。

ユニークリストを作成しよう

CSVの取り込みができたところで次はそれぞれのユニークリストを作成したい。
合わせて並べ替えをしたいので次のようにします。

=SORT(UNIQUE(テーブル[**],,FALSE),,1)

Columnが増えることを想定して[**]はCSVから取得するようにしました。
VBAでセルに対して上記内容を入れた時に困ったことが発生。
本来であればスピルを利用したいのですが、勝手に「＠」がついて

=＠SORT(UNIQUE(テーブル[**],,FALSE),,1)

となってしまう。
回避策の検討が必要ですね。

セルを指定するとき、
Cells(1, i).Formula2
が正解で理想通りになりました。基礎知識が足りないとこのあたりで苦労するのでしょう。
一応マクロの記録でも見比べていたのですが、この2の意味が分からず消していたのが原因でした。

■課題
個々のユニークリストは作成できたとして、
きっとすべてまとめたユニークリストが必要になると思う。
この時、値化すれば再現はできるが読み直しへの対応が難しい。


■寄り道
試行錯誤しているとき、UNIQUEの配列に当たる部分に「＆とか+」を入れてみたら影響した。
なのでデータを組み合わせてオリジナルのユニークリストを作ることも可能ということが分かりました。
_________________________________________________________
data1
日時：yyyy/mm/dd hh:mm:ss
日付：yyyy/mm/dd
時間：hh:mm:ss

=SORT(UNIQUE(data1[日時],,FALSE),,1)
=SORT(UNIQUE(TEXT(data1[日付],"yyyy/mm/dd")&" "&TEXT(data1[時間],"hh:mm:ss"),,FALSE),,1)
_________________________________________________________
※何に使うかはあなた次第！
　1列に格納するなら表示形式を指定すればよいのですが、
　合体させるなら事前にTEXTで指定しないと数字がいっぱい入ります。

始業と終業、UniqueとSortとXlookup

まずはログを取るということで、必要な情報は絞らずに設定しました。
足りない情報は都度追加する方針です。

次に得たログで何を見たいかを考えていきます。
今回はテレワーク視点で一日の始業と終業が見れるようにします。
日中のみで働く人、日をまたぐ人があると思いますので、
ある程度のケースに準じて対応することにします。
正確な勤怠管理はここでは検討していませんが、
単純に出勤退勤ボタンを用意することで解決できると思いますので、
今あるログの中でどこまで再現可能かやってみます。

まずはPCのシステムログを取る方法。
これはPCがプライベートだったり仕事以外の操作を行うことがあった場合、
起動とシャットダウンのログでは勤怠管理は困難ですので、
単純に以下のようにしました。
　エクセルを開いた　＝　仕事始めた　　⇒ログイン
　エクセルを閉じた　＝　仕事終わった　⇒ログアウト
なのでエクセルを開いた瞬間にまずはログを取るようにします。
Workbook_Open()　と　Workbook_BeforeClose(Cancel As Boolean)
これでなんとか対応が可能です。
色々なエクセルに同じVBAを仕込むことになりますので、
用途に応じて開くエクセルの数だけログインとログアウトという動作が追加されます。
よって始業と終業は以下のようにしてみました。
　始業　＝　一番最初のログイン
　終業　＝　一番最後のログアウト
日をまたぐ場合はこれでは成り立ちませんので別の設定を設けました。

一番最初のログイン取得方法
　対象の日付をUnique関数を使う。Sort関数で並べ替える。
　隣の列にXlookup関数を使ってスピル。
　引数で上からにするのがポイントでした。
一番最後のログイン取得方法
　上記の中で引数を下からにすれば可能でした。


■寄り道
いずれは勤怠管理もできそうだったので検討開始。
寄り道に時間がかかり過ぎましたが、ログの取得方法に一部変更がはいったり修正ができたのでこれはこれで良しとしています。

■課題
一人のログなら一か所にまとめて取れますが、複数人を管理しなければなりませんので
OneDriveに格納した状態で再現するように組む必要あり。
ローカルで環境が作れるように自動でどこまでやれるかの確認。
更新ログを残すシートが存在しない時の自動追加。
CSVでログを残すことを今回は検討しており、CSV取り込み方法の検討。
一人だけの集計ではなく複数のCSVに対応した集計。

VBAでエクセルの変更ログを取る

とある目的に向けてエクセルVBAを用いて達成しようと考えました。
その途中で思いついたことを寄り道しながら再現していきます。
元SVがエクセルVBAを勉強する。実際に私が作れるかどうかは別問題として。

■やりたいこと

「ログを取る」

■背景
Q:テレワークを導入したいです。家で仕事してもらったとして、今使っているシステムで誰がどれだけやっているか確認することはできますか？
A:現状では確認できません。

こういったことができるシステムは当然あると思いますが、今使っているものでできないのであれば何か代替え案を考えなければいけません。
さて、前置きはこれくらいにして。

まずは見える化のためにエクセルで作業したときのログを取ってみます。

トリガー：ワークシートで何かを変更したとき
WorkSheet change で target をトリガーにすれば取得できることはわかりました。
今回のケースではデータは何が必要か。
　「日時」　　　　　（日付・時刻）
　「PC名」　　　　  （参照設定＞ライブラリの追加）
　「ユーザー名」　　（参照設定＞ライブラリの追加）
　「ワークブック名」
　「ワークシート名」
　「セル」
　「動作」
　「内容」　　　　　（要エラー回避）
まずはこれくらいとして、今後指標が増えるたびに項目を増やしたいと思います。

■課題
・targetを使う場合、シートモジュールに記入。
　⇒シートを増やした時、VBAが反映されるようにしたい。
・VBAをほかのブックでも使いまわし管理する場合、トリガーはブックモジュールに記入。読み込むVBAは標準モジュールに記入。
　⇒メンテナンスを容易にしたい。
※どこに何を記入したら理想通り動くのか試行錯誤したいと思います。
・RPAも視野に入れる。