Copilot(ChatGTP)で複数CSVファイル取込用のExcelマクロを簡単作成！

2023年はAI元年とも言われており、Microsoft社が開発した対話型AIであるChat-GPTのGPT-4モデルが無料で使えるCopilotではBing Chat(GPT-4)で様々な業務効率化を実現することができます。今回はCopilotを使って複数のCSVファイルを1アクションでExcelの各シートに取り込むマクロ(VBA)を作成依頼してみることにしました。

結果としては非常に優秀な開発アシスタントとして機能してくれたので、具体的な流れと指示方法についても説明してきますので是非最後まで読んでみてください！

前提条件は以下の通り

• マクロの開発/実行環境は事前に構築済
  →Windows Copilot(ChatGPT)にExcelマクロ(VBAコード)の簡単自動化実例紹介！
• Copilotについて利用環境がある
  →Windows11でCopilot(AI機能搭載)をすぐ有効化する方法
• マクロ(VBA)に関する知識はほぼ不要！
  →マクロを利用する上で最低限必要なセキュリティ上の知識は必要
• マクロ作成する際の指示の内容を組み立てる力

Copilotで複数のCSVファイルをExcelの各シートに読み込ませるマクロを作成

業務でCSVを扱う際にExcelに取り込む際にタブ区切りであればコピペでシートへ反映することも容易ですが、カンマ区切りの場合やファイルが複数存在した際など、手動で行うのは手間ですよね。

とは言えマクロを作成するにもVBAプログラミング知識がある程度備わっている必要があったりと、難易度が高い。しかし、最大の難関でもあるVBA開発部分をCopilotに任せることで開発コストも大幅に削減することができるため、本来の業務や作業に集中することができるのは大きなメリットです。

とごちゃごちゃ言うのはここまでとして早速やっていきましょう！

と・・・言いたい所ですがマクロ実行する際の注意事項については事前に把握/理解しておく必要があるので、念のため事前に確認しておきましょう。

Excelマクロ実行する前の注意事項について(Copilotへ聞いてみた)

VBAを安全に使用するためには、以下の事項を理解し、遵守することが重要です：

1. マクロのソースを確認する：不明なソースからのマクロは実行しないでください。マクロのコードを開いて内容を確認し、理解できないコードや怪しい動作をするコードがないことを確認してください。
   →ソースコードが読めない場合は「コメント」は最低限把握しておく
2. 信頼できるソースからマクロを取得する：信頼できるソース、例えば公式のマイクロソフトのウェブサイトや信頼できる第三者のウェブサイトからマクロを取得するようにしてください。
   →Copilotでコード生成してもらう場合も責任についてはMicrosoft社は責任を負いません。
3. VBAのセキュリティ設定を理解する：ExcelにはVBAマクロのセキュリティ設定があります。これらの設定を理解し、適切なレベルに設定してください。
   →例えばWebから拾ったツールやウィルスによりマクロ実行された際に実行されてしまう可能性がある
4. ActiveXコントロールに注意する：ActiveXコントロールは強力な機能を持っていますが、悪意のあるコードを実行する可能性もあります。信頼できないソースからのActiveXコントロールは使用しないでください。
5. 個人情報や機密情報の取り扱いに注意する：VBAマクロはExcelのデータにアクセスできます。個人情報や機密情報を含むデータを取り扱う場合は、そのデータがマクロによってどのように使用されるかを理解してください。
6. 自動実行マクロに注意する：一部のマクロはExcelの開始時に自動的に実行されます。これらのマクロは特に注意が必要です。
7. マクロのエラーハンドリングを理解する：マクロがエラーを起こした場合の対処方法を理解しておくことも重要です。
   →エラー時にどこで何が起きたのか、デバッグ機能などを使って問題解析をするスキル(エラー時はCopilotに聞いてみるのも一つの方法)

以上の点を理解し、遵守することで、VBAのマクロを安全に使用することができます。また、VBAの知識を深めることで、より安全に、そして効果的にVBAを使用することができます。VBAの学習には、公式のマイクロソフトのドキュメンテーションや信頼できる教育リソースを活用することをお勧めします。また、不明な点や問題が発生した場合は、専門家の助けを求めることも重要です。VBAは強力なツールですが、その力を安全に利用するためには適切な知識と理解が必要です。安全にお使いください。

自動化は非常に便利ですが、何をしているのか把握していないことの危険性については自己責任となるので扱いには注意する必要があります。

Excelマクロで取り込むCSVの内容を事前に把握

今回は国や行政が公開しているe-GOVデータポータルの人口に関するオープンデータのCSVをテスト用ファイルとして利用することにしました。

データの中身をテキストエディタで確認すると以下の通り

“都道府県コード”,”都道府県名”,”元号”,”和暦（年）”,”西暦（年）”,”注”,”人口（総数）”,”人口（男）”,”人口（女）”
“00”,”全国”,”大正”,9,1920,””,55963053,28044185,27918868
“01”,”北海道”,”大正”,9,1920,””,2359183,1244322,1114861
“02”,”青森県”,”大正”,9,1920,””,756454,381293,375161
“03”,”岩手県”,”大正”,9,1920,””,845540,421069,424471
“04”,”宮城県”,”大正”,9,1920,””,961768,485309,476459
引用：データセット | e-Govデータポータル

特徴としては「ダブルクォーテーション囲み」「カンマ区切り」「1行目がヘッダー情報」だったりと、非常にシンプルな構成であることが分かります。この辺りの特徴をCopilotへの指示プロンプトへ反映することで、イメージに近くより精度の高いマクロを作ってくれる可能性が高まります。

ExcelファイルとCSV取込用のフォルダやCSVの配置は↓の様に構成しました。

Excelファイルはマクロ実行及び「csv」フォルダ内のCSVファイルを各シートに反映させるためご本尊ファイルと言って良いでしょう。
2枚目の「c0*.csv」の3ファイルがe-GOVからダウンロードしておいたファイルとなります。3ファイルとも特徴自体に変わりはありません。

この状態をマクロ作成における条件としてあとはCopilotさんに作成依頼をするのみ！

Copilot(Chat-GTP)へのマクロ作成依頼指示を作成

どこまで複雑な指示が可能かについては探りながらですが、とりあえずこんな感じで最低限の仕様は満たせるような条件にしました。

Copilot(Chat-GPT)へExcelマクロ作成指示後の回答

Sub ImportCSVs()
    Dim strPath As String
    Dim strFile As String
    Dim wbCSV As Workbook
    Dim wsTarget As Worksheet
    Dim strName As String
    
    ' Set the path to the CSV files
    strPath = Application.ActiveWorkbook.Path & "\csv\"
    
    ' Get the first CSV file
    strFile = Dir(strPath & "*.csv")
    
    ' Loop through each CSV file in the folder
    Do While Len(strFile) > 0
        ' Open the CSV file
        Set wbCSV = Workbooks.Open(strPath & strFile)
        
        ' Set the name for the new worksheet
        strName = Left(strFile, InStrRev(strFile, ".") - 1)
        
        ' Add a new worksheet with the name of the CSV file
        Set wsTarget = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsTarget.Name = strName
        
        ' Copy the contents of the CSV file
        wbCSV.Sheets(1).UsedRange.Copy wsTarget.Cells(1, 1)
        
        ' Close the CSV file without saving
        wbCSV.Close savechanges:=False
        
        ' Convert the range to a table and apply a filter
        wsTarget.ListObjects.Add(xlSrcRange, wsTarget.UsedRange, , xlYes).Name = strName
        wsTarget.ListObjects(strName).Range.AutoFilter
        
        ' Get the next CSV file
        strFile = Dir
    Loop
End Sub

数十秒程度で上記の回答が得られました。マクロのファンクション名も「ImportCSVs」と非常にシンプルで明確な名称でセンスを感じます(笑)

VBAの詳しい処理についてはここでは言及しませんが、処理内容に対するコメントが英語だったのが少し残念ですのでこの辺りは後で細かい点も含めて改善を試みることにします。

Copilot(Chat-GTP)で作成したExcelマクロを実装→実行

上記動画は各ファイルやフォルダ構成を事前準備した状態でCopilotでの回答で得たマクロ(VBA)をExcelに実装→実行→結果までの一連の流れをキャプチャした内容となっています。

これがなんと一発で成功！

以前に各シートをPDF化させるマクロ作成を依頼した際も一発成功でしたが、今回もバグが発生することも無く一度で条件を満たしているとは・・・驚きです！
上記動画だけですと流れが分からないので画像で一つずつの工程を説明していきます！

①Excelを開き「開発タブ > Visual Basic」を開く

②「上部メニュー > 挿入 > 標準モジュール > Module1」をクリック
※ThisWorkbookをクリックでもOK

③Copilotの回答内のVBAコードをコピー

④”②”で開いたModule1へ貼り付け

⑤「上部メニュー > 実行 > Sub/ユーザフォームの実行」をクリックしマクロを実行
※「▶」マークやファンクションキーのF5でも実行可能

⑥結果を確認

指示通りシート名=ファイル名(拡張子なし)となっておりテーブル名はシート名と重複してしまうため先頭に”_”(アンダースコア)が付与されていますが、カンマ区切りで1行目がヘッダーのテーブルが各シートに取り込まれていることが分かります。

実際にプロンプト(指示文)を考えるのに1分程度→回答に1分程度→VBAコード実装に数秒と考えると、本来コード生成するのに数時間かかる可能性も考えると一瞬で作成できてしまう凄さがお分かりいただけましたでしょうか。

Copilot(Chat-GTP)で生成されたExcelマクロの品質を高める作業

今回実施したのは複数あるCSVファイルをExcel内の各シートに取り込むマクロ(VBA)をCopilotに作成してもらいましたが、細かい点で不満があるためもう少し改良すべく以下の点を追加でお願いしてみることにしました。

Sub ImportCSVs()
Dim strPath As String
Dim strFile As String
Dim wsTarget As Worksheet
Dim strName As String
Dim response As VbMsgBoxResult
Dim conn As Object
Dim rs As Object
Dim i As Integer

' マクロを開始しますか？と尋ねる
response = MsgBox("マクロを開始しますか？", vbYesNo)

' ユーザーが「No」を選択した場合、マクロを終了する
If response = vbNo Then Exit Sub

' CSVファイルのパスを設定する
strPath = Application.ActiveWorkbook.Path & "\csv\"

' 最初のCSVファイルを取得する
strFile = Dir(strPath & "*.csv")

' フォルダ内の各CSVファイルをループする
Do While Len(strFile) > 0
' 新しいワークシートの名前を設定する
strName = Left(strFile, InStrRev(strFile, ".") - 1)

' CSVファイルの名前で新しいワークシートを追加する
Set wsTarget = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsTarget.Name = strName

' ADODB.Connectionオブジェクトを作成
Set conn = CreateObject("ADODB.Connection")

' テキストドライバーを使用して接続を開く（HDR=Yesはヘッダー行があることを示す）
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"""

' レコードセットを開く
Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM [" & strFile & "]", conn, 2, 1

' ヘッダー行を書き出す
For i = 1 To rs.Fields.Count
wsTarget.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i

' レコードセットのデータをワークシートにコピー
wsTarget.Cells(2, 1).CopyFromRecordset rs

' レコードセットと接続を閉じる
rs.Close
conn.Close

' 範囲をテーブルに変換し、フィルタを適用する
wsTarget.ListObjects.Add(xlSrcRange, wsTarget.UsedRange, , xlYes).Name = strName
wsTarget.ListObjects(strName).Range.AutoFilter

' 次のCSVファイルを取得する
strFile = Dir
Loop

' 完了メッセージを表示する
MsgBox "完了しました"
End Sub

結果として、開始時の確認メッセージ
完了時の完了メッセージ

“01”データの0落ちの防止

これで追加指示通りとなりましたが・・・

以下の様にヘッダー行が落ちてしまい更に2行目がテーブルのヘッダー扱いといった具合で思い通りとならない問題が発生しました。
修正はCopilotとやり取りをしながらトライ&エラーを繰り返すことで改善はされましたが、
正直中々苦戦しました。

要因としては”01″をExcel上で文字列として表示させるため最初のVBAに”ADODB.Connection”オブジェクトを追加させることで実現できるのですが、
このオブジェクトが曲者でCopilot(Chat-GTP)さんでもこちらの考慮が落ちてしまい、改善に向けて数回やり取りすることとなりました。

更に細かい点で言うと”C01″シートは文字列表示となりましたが、なぜか”C03″シートでは0落ちが残存してしまう事象については改善できず・・・と言うかテストなので強制終了しました(笑)

Copilot(Chat-GTP)に複数CSVファイルを取り込むマクロ(VBA)を作成依頼してみた感想

前回作成した各シートをPDF化させる比較的簡単なマクロを作成して見事一発で実現に対し、今回はもう少し複雑な条件を依頼してみた結果、
ある程度シンプルな条件であれば問題なく作成可能ですが少し高度な条件についてはエラーも発生しマクロのプログラミングやデバッグ(問題箇所の特定のための実行スキル)操作などの知識やスキルが必要だと感じました。

とは言え大枠のコード生成については間違いなく効率化されるので、ファンクション名に迷ったりと言った無駄な時間を費やす必要も無く、コアなポイントに絞って人的リソースを割けるのは非常にありがたいですね。

是非皆さんもCopilot(Chat-GTP)を使ってExcelマクロのツール作成を試してみてください！

ChatGPT4(Bing Chat)のプロンプト攻略テクニック6選(OpenAI公式)

Bing Chat(ChatGPT4)でプログラミング生成を試してみた