Excelの「コメント」をマクロで抽出する(2021以降/2019以前、VBA/OfficeScript)

エクセルに大量のコメントがついていて、確認しきれない。
マクロで全部抽出して、検索なども使って確認したい

というご要望が寄せられまして、VBAでマクロを組んだのですが、思ったより奥の深い世界でしたので、記事にまとめてみたいと思います。

Office 2021 以降の「スレッド形式のコメント」の抽出

Excelのコメント機能が、Excel 2021 を境に変わりました。

「スレッド形式のコメント」になり、従来の「コメント」は「メモ」と名前を変えて残ることになりました。

ブック全体の「スレッド形式のコメント」をすべて抽出するマクロ

ブック内のすべてのコメントを、別シートに書き出すマクロの例を作ってみました。こんな感じです。

別シートに出力するようにしたので、とりあえず、標準モジュールにコピペすれば、動くと思います。

出力は次のようになります。

コレクション .CommentsThreaded が全コメントを含んでいる

「全コメント」というのは、VBAで扱う上では意外と簡単で、コレクション「.CommentsThreaded」にすべてのコメントが含まれています。

親をシートにすれば、シート全体のコメント、
親をブックにすれば、ブック全体のコメント。
そして、範囲にすれば、その範囲内のコメントの全体集合を、簡単に取得することができます。

それをFor Eachループにかけたのが上記のコードになります。

セルにコメントがあるかどうかの判定

Web検索で「Excel コメント 書き出し VBA」とかやると、全セルをスキャンしてコメントの有無を判定していく書き方をしたコードが結構出てきます。

本記事では、全コメントを書き出すのであれば、たぶん、CommentsThreadedコレクションをFor Eachで回した方がスマートだ、と考えまして、このようなサンプルにしてみました。

しかし、「特定のセルにコメントがあるかどうか判定する」ことが必要なケースはあると思います。その場合は次のように書きます。

 If Not (そのセル).CommentThreaded Is Nothing Then
     (コメントが「あった」場合の処理)
 End If

「『CommentThreadedオブジェクトが無い』ではない」 = ある　っていう判定方法ですね。存在しなくてもエラーにはならないので、このように書くことができます。

あるコメントに「返信があるかどうか」の判定

これに対して、「あるコメントに返信があるかどうか」の判定は、この方法では無理でした。

コメントがあることが分かっているセルに対して、次のような判定をすると、返信があってもなくてもFalseになります。

If Not (そのセル).CommentThreaded.Replies Is Nothing Then
     (返信が「あった」場合の処理)
 End If

ですので、違う方法で判定する必要があります。

If Not (そのセル).CommentThreaded.Replies.Count > 0  Then
     (返信が「あった」場合の処理)
 End If

CommentsThreaded コレクションには、返信は含まれない

あるシートやブックを親にした場合のCommentsThreaded コレクションには、返信は含まれず親コメントのみが含まれます。

コレクション内の個々のCommentThreaded オブジェクトに対して、Repliesプロパティを参照しないと、返信は取得できません。

CommentsThreaded.Repliesは、親コメントについた返信全部を含むCommentsThreadedコレクションになります。

親コメントと返信を、プログラム上で区別するのが結構むずかしい

親コメントと返信コメントは、このようにオブジェクトツリー上ではっきり親子関係でありながらも、個々のコメント/返信はすべて、CommentsThreadsコレクション/ CommentThreadオブジェクト、という同じ型で示されます。

ということは、
・CommentThreadedオブジェクトを見ただけでは、親なのか返信なのかは分からない
ということになります。

ということで、

あるCommentThreadedが、親コメントなのか、返信なのか。

どうやって判定するかいろいろ試した結果、結局エラートラップをするしかないという結論にたどりつきました。例えばこんな感じです。

On Error GoTo Replies
        dest.Cells(Line, 1) = ObjComment.Parent.Address

…
…
Replies: '返信であった場合の処理
    If Err.Number = 438 Then
        dest.Cells(Line, 1) = "返信"
    Else
        On Error GoTo 0
        Stop
    End If

.Parentプロパティを参照して、エラーになる場合は返信だと判断する、という方法しか、どうもなさそうです。なので、サンプルコードでは、この判定を回避して、親コメント、返信、それぞれに出力部をだらだらと書く形にしました。

ほんとにこれしかないのかな。ちょっとまだ疑っています。

「メモ」(Office 2019 以前の「コメント」)の抽出

Office 2019以前の「コメント」は、Office 2021以降では「メモ」と改名されて存続しています。

新バージョンのコメントと同じように、CommentsコレクションとCommentオブジェクトが存在します。

すべてのメモを抽出するコードはこんな感じになります。

新バージョンのコメントでは、作者名は「Author.Name」ですが、旧バージョンのコメントでは単に「.Author」です。(サンプルコード39行目)

また、旧バージョンのコメントには返信機能がありませんので、返信は気にする必要がありません。

OfficeScriptによる全コメントの抽出

こんどは、VBAではなく、新しい「OfficeScript」で同じことをやってみましょう。

サンプルコードはこのようになります。

OfficeScriptについて、当店別記事はこちらから参照ください。

Excelに「Officeスクリプト」がやってきた VBAは廃止される?

Excelで、いつものように、何気なく「マクロの記録」を使っていたら、こんな画面が出て驚きました。「マクロはOfficeスクリプトとしても使用できます」「スクリプトを保存」をクリックすると、従来のVBAマクロと、Office Scriptの...

curio-shiki.com

2024.03.10

VBA→OfficeScriptの変換はCopilotを使って下書き

これから、VBAからOfficeScriptへの移行作業が必要な方も多いと思います。

「VBAにできてOfficeScriptにはできないこと」を認識した上であれば、まずざっとCopilotまたはChatGPTで変換してみるのが速いと思います。

VBAにできてOfficeScriptにできないこと
・他のExcelブックの読み取り・書き込み・新規作成
・外部ブラウザの起動と読み取り
・外部アプリケーションの起動
・イベントによる起動

Microsoft Copilotの利用について
・Windowsに無料版Copilotが付属しています。タスクバーのCopilotアイコンから起動できます。
・当店では、一般法人向けMicrosoft Copilotのライセンスを契約して使用しています。
・個人向けMicrosoft 365では、最近の値上げと同時に、20回分の使用権が付帯され、全員が使えるようになりましたが、たった20回なのにずいぶん高くなった感じがします。当面の間Copilotなしのプランに切り替えることができます。方法はこちらの記事をご覧ください。

Copilotで作ったコードは一発では動かない

現状では、Copilotが書いてくれたコードは残念ながら一発では動きません。

「修正してください」的なオーダーも試しましたが、下記の誤りはCopilotの力では修正できませんでした。

TypeScript (Javascript)の記法についての基本的な知識に加えて、APIリファレンスを参照して修正する必要があります。

ExcelScript.Comment interface - Office Scripts

ブック内のコメントを表します。

learn.microsoft.com

ChatGPT(無料版)でも同じことをやってみましたが、似たようなものでした。

OfficeScriptは「メモ」(Office 2019以前のコメント)非対応の模様

OfficeScriptで、旧バージョンの「コメント」、新バージョンの「メモ」の抽出ができないか、確認しましたが、どうやら非対応のようです。

そもそもスクリプトの記録で「メモ」の新規作成を記録しようとすると、「この操作は記録できません」になってしまいます。