あのブック、どこにあったっけ・・・
仕事でよくあるのが、「この前見た資料、どこに置いてあったっけ・・・」とか「どこに保存したっけ？」という状況です。
「最近使ったアイテム」に残っておらず、ファイルサーバーだったか、自分のPCだったか、ダウンロードした奴だったか、というのも覚えておらず、作った人も分からず、手帳にメモも書いておらず、当時聞いた人に「前に言いましたよね？」なんて言葉も聞きたくない。
でも探さないといけない。時間がない。探す時間が無駄すぎる。つらい。うーん、分かります。その状況はイヤです。
VBAで解決しましょう。
Excelのブックを開いた履歴は50個まで
Excelでブックを開く場合に、「最近使ったアイテム」の一覧から、過去に開いたブックを探して開くことが出来ます。便利な機能ですが、初期設定では50個までしか履歴管理されません。数を増やすにしても無限ではありません。
また、「最近使ったアイテム」の一覧に表示されるのは「開いたブック」が対象となりますが、あとから自分が保存したブックを特定したいときには使えません。
そこで、以下では、開いたブックの履歴と、保存したブックの履歴をテキストファイルに出力する方法を紹介します。
コードを書く場所と処理の説明
以下のコードはPERSONAL.XLSBのThisWorkbookに書いてください。ThisWorkbookに何も書いてないのであれば、まるごとコピペでOKです。このマクロはExcel起動時に存在していないと動作しないため、ThisWorkbookに書いて保存したら一度Excelのブックを全て閉じてください。これは最初の１度だけです。それ以降は次にブックを開いたときからPERSONAL.XLSBに書いたコードが動作します。
ブックを開いたり、保存したりすると、「Excelのオプション→保存→既定のローカルファイルの保存場所」で設定されているフォルダ（通常は「ドキュメント」フォルダ）に「Excel-History.txt」というファイルを作成し、日時、開いた（Open）もしくは保存（Save）、ブックパス、を書き込みます。
Excel-History.txtにはこんな感じで書き込まれます。
|
1 2 3 4 |
2021/06/03 02:18:45 [Open] V:\aaa\vba1.xlsm 2021/06/03 02:23:46 [Open] V:\test\bbb\test\手順書.xlsx 2021/06/03 02:28:03 [Save] V:\aaa\vba1.xlsm 2021/06/03 02:37:26 [Open] \\nwstrage\main\doc\資料\資料の書き方.xlsx |
日時（yyyy/mm/dd hh:mm:ss）と、開いたor保存の[Open]または[Save]、そして、そのブックのフルパスが書き込まれます。ブックがファイルサーバなど\\で始まるパスにある場合でも問題ありません。
なお、Openステートメントを使っているためShift-JISで書き込まれており、テキストエディタでUTF-8で開いたりすると文字化けを起こします。
Excel-History.txtには単純に追加書き込みをしているだけのため、ブックを開いたり保存したりする度に書き込まれていきます。ただ、それだとファイルサイズが増え続けるため、100,000バイト（約100KB）を超えたらExcel-History.txtのファイル名に日時を付与してバックアップ（ファイル名変更）するようにしています。
100KBは、１日にブックを開いたり保存したりを100回行ったとすると、大体ですが半月ぐらいで達するぐらいの分量です。１年掛かってもバックアップファイル含めて2MB行かないぐらいですので、完全放置でも容量不足の問題はまず起きないでしょう。
コード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
Option Explicit '// Excelのイベント検知 Dim WithEvents x As Application '// PERSONAL.XLSBが開いたとき Private Sub Workbook_Open() '// Excelアプリケーションのイベントを検知する Set x = Application End Sub '// ブックが開いた場合 Private Sub x_WorkbookOpen(ByVal Wb As Workbook) Call OutputHistory(Wb, "Open") End Sub '// ブックを保存後 Private Sub x_WorkbookAfterSave(ByVal Wb As Workbook, ByVal Success As Boolean) '// 保存失敗時 If Success = False Then Exit Sub End If Call OutputHistory(Wb, "Save") End Sub '// 履歴書き込み Private Sub OutputHistory(ByVal Wb As Workbook, a_sType As String) Dim sNow As String '// タイムスタンプ Dim sPath As String '// ファイルパス Dim n As Integer '// ファイル番号 sPath = Application.DefaultFilePath & "\Excel-History.txt" '// 履歴ファイルが存在する場合 If Dir(sPath) <> "" Then '// 履歴ファイル名が指定サイズを超過している場合 If FileLen(sPath) > 100000 Then '// 履歴ファイル名をバックアップ名に変更 Name sPath As sPath & Format(Now, "yyyymmdd-hhmmss") End If End If '// 現在日時を取得 sNow = Format(Now, "yyyy/mm/dd hh:mm:ss") '// ファイル番号取得 n = FreeFile '// 履歴ファイルを追加書き込みモードで開く Open sPath For Append As #n '// ブックのパスを書き込み Print #n, sNow & " [" & a_sType & "] " & Wb.FullName '// 履歴ファイルを閉じる Close #n End Sub |
コード説明
このコードでは少し特殊なことを行っています。それはWithEvents修飾子です。WithEvents修飾子を使って変数を定義すると、型として指定したオブジェクトのイベントを検知できるようにしています。ここでは「Application」を指定しているためExcel自体を指すことになります。
通常、VBAでは他のブックのイベントを検知することは出来ません。例えばa.xlsxでセルの値が変わったことを検知するChangeイベントはa.xlsxで取得するのが普通です。
Excelは起動時に「C:\Users\[ユーザ名]\AppData\Roaming\Microsoft\Excel\XLSTART」」フォルダにあるブックを開きます。PERSONAL.XLSBもこのフォルダに配置されるため、個人用マクロブックとしてExcel起動時に開かれます。
XLSTARTフォルダに他のブックを意図的に置かない限り、PERSONAL.XLSBは唯一のExcel起動時に開かれるブックのため、PERSONAL.XLSBにWithEvents修飾子を使ってExcelのイベントを検知できるようにすることで他のブックの挙動を検知できる仕組みを用意しています。