Excel
メモ
VBA
初心者
新人プログラマ応援

エクセルを使ってファイルを突合チェックする

先日、成果物(ここではプログラムとします)と成果物一覧を見比べてファイル有無をチェックしてほしいと言われ、エクセル関数とマクロを使用し自動化したので備忘録として残しておきます。

したいこと

フォルダ内の成果物(.txt)と成果物一覧を突合チェックしたい。

方針

  • Excelが使える人対象。Excel関数は IF, COUNTIF, LEFTB, LENB関数程度
  • マクロは使えないけど興味ある
  • マクロはあくまで補助

エクセル側

1. 突合させる用のシート作成

フォルダ側のリスト

キャプチャ1.PNG

成果物一覧

キャプチャ2.PNG

2. 突合結果シート作成

  • B2, D2はそれぞれのシートからの値が入る(=シート名!セル)
  • C2はB2から.txt(4文字)を省いた値が入る(=LEFTB(セル, LENB(セル)-4))
  • E2はCOUNTIF(範囲,検索条件)を使用したチェック結果が入る。1つでもあれば1、なければ0になる。

キャプチャ3.PNG

関数を適用するとこうなる↓
キャプチャ4.PNG

これでは分かりづらいので、IFを使用し0と#VALUE!が表示されないようにする。あと一覧整形。
キャプチャ5.PNG

エクセル側は完成。フォルダ内のファイル一覧取得はコマンドでもいいが、ここではマクロで一覧取得してみる

マクロ

1. Alt + F11 でVisual Basic Editorを起動し、標準モジュールを作成する

キャプチャ6.PNG

2. VBAを書く、分からなければコピーでOK(サンプルはネットにころがっているので)

Sub test()

    '変数を定義
    Dim buf As String, ThisPath As String, cnt As Long

    '変数「ThisPath」にパスを格納
    ThisPath = ActiveWorkbook.Path

    '「Path」に存在するすべての「*.txt」をアクティブシートに書き出す。
    buf = Dir(Path & "*.txt")
    Do While buf <> ""
        cnt = cnt + 1
        Cells(cnt, 1).Value = buf
        buf = Dir()
    Loop

    '完了メッセージをポップアップで表示
    MsgBox "ファイル一覧を更新しました"

End Sub

キャプチャ7.PNG

3. 開発タブからボタンを挿入し、VBAが起動できるように紐付け

キャプチャ9.PNG

キャプチャ10.PNG

完成。成果物と同じフォルダへ入れて一覧更新をすれば簡単にチェックできます。

Excel VBAに興味を持った人へのおまけ