エクセルファイルからのデータ収集。取り出し条件設定シート作成

datasyusyueyecatch002

複数ファイルからのデータ収集の取り出し条件設定方法についてです。
VBAでその都度、その時々に合った取り出し条件設定シートを設置できるようにします。

こんにちは、じゅんぱ店長(@junpa33)です。

今回から、データ収集のためのエクセルVBAの解説をしていきます。

まずは、「取り出し条件の設定」についてプログラム作成を行っていきます。

データを取り出したいエクセルブックの中の代表的なエクセルブックを選びます。

そのエクセルブックにあるシート名をリストアップします。

そのシートリストの中で、どのシートを対象にするか、

そのシートの中のデータを最大5つ抽出できるようにします。

先回の記事を読み返すにはこちら↓です。

datasyusyueyecatch 複数ファイルから特定データを収集するエクセルVBAの概要

取り出し条件を設定する方法

detasyuusetteip005

手作業で大量のエクセルファイルを一つづつ開いて、必要データをコピーして別の集計ファイルにペーストする作業を考えると、多少の手作業が残っても多くのことが自動化されるならそれでもOKでしょう。

たとえば、収集するデータの「項目名」とそのデータが入っている「セル番号」は手打ちするしかありません。

逆によく起こる問題として、ファイルの中の「どのシート」を参照するかを手打ちで指定すると、そのシート名を間違って指定しまう事があります。

大文字・小文字・半角スペースの違いでも条件を認識できなくなってしまいます。そして案外ここで手間取ってしまう事が多々あります。

ここでのテーマでは、この条件入力ミスを防ぐ工夫も紹介したいと思います。

つまり、ボタンクリックすると自動で、このように選択可能なシートのリストが表示されるようにします。

detasyuu004aa

条件を設定するシート作成のVBA

detasyuusetteip006

今回はこのボタン「抽出条件作成」で操作するプロシージャーを作成します。

detasyuu002a

最初にエクセルBOOKのセットアップします

新しくエクセルBOOKを開きます。

Sheet1を「設定」

Sheet2を「抽出リスト」とします。

一旦、BOOK名を「データ収集.xlsm」として保存してください。

条件設定シートのVBAコード

取り出すデータのあるシート名を指定します

シート名はそれぞれ入力して指定していくことになります。

複数のシート名からデータをピックアップする時、少数のシート数であればまだいいですが、

多量のシート数がある場合は、手打ち入力指定だけでも大変です。

そこで、

指定方法として、データを取り出すエクセルファイルの何れかを「代表的なファイル」として選択し、

その「代表的なファイルに含まれる全シート名」をリスト化します。

そのリストの中で、条件指定として必要なシートをチェックすることにします。

(指定したいデータが含まれていないファイルを選択すると、必要なシート名をリスト化できません。)

参考記事

モジュールの挿入方法が不明な方は、こちら↓でご確認ください。

「VBE(ビジュアルベーシックエディター)を起動する」

こちらの記事でも参考になります。

vbavbekidoeyecatch VBA初めての起動。VBEの立ち上げ、保存と終了

VBAコードとしては、次のようになります。

このコードは、標準モジュールを新規挿入後、そちらに記入してください。

ここではModule1にコード記述を行います。

モジュールの宣言エリア

プロシージャーの作成に移る前に、モジュールの宣言エリアに、このモジュール(Module1)で共通して有効にする変数を記述しておきます。

VBA
Option Explicit

Private Path As String, i As Long
Private CndN As String, Cnd As Variant
Private CneN As String, Cne As Variant
Private CnfN As String, Cnf As Variant
Private CngN As String, Cng As Variant
Private CnhN As String, Cnh As Variant
Private Sna(1000) As Variant
Private Arow, NO, Result As Long
Private Flag As Boolean

プロシージャー名「シート名取出し()」

「ファイルを開く」ダイアログを表示して、ファイルを選択できるようにします。

カレントフォルダにあるファイルが表示されます。デフォルト設定は、このVBAソフト本体のあるエクセルファイルのフォルダとしています。

vbachdireyecatch ChDirステートメントでカレントフォルダを簡単に変更する vbanameeyecatch 新規作成ブックとシート。アクティブ状態でやっておくこと vbadoloopeyecatch VBA 回数不定のループ処理はDo LoopとFor Each vbawithstateeyecatch With~End Withの使い方。VBAコードを簡潔に記述する vbacellsbordereyecatch 「罫線」のVBAを最速理解
VBA
Sub シート名取出し()
    Dim St As Worksheet
    Dim R As Long
    Dim flg As Boolean
    Dim An As String
    ChDir ThisWorkbook.Path
        On Error Resume Next
        flg = Application.FindFile
        On Error GoTo 0
        If flg = False Then Exit Sub
        An = ActiveWorkbook.Name
        R = 4
        For Each St In Worksheets
            Workbooks("データ収集.xlsm").Activate
            Worksheets("設定").Cells(R, 1).Value = St.Name
            With Range(Cells(R, 1), Cells(R, 2))
                .Interior.ColorIndex = 6
                .Borders.LineStyle = xlContinuous
            End With
            R = R + 1
            Workbooks(An).Activate
        Next
        Workbooks(An).Close False
End Sub

プロシージャー名「変数設定()」

この「シート名取出し()」でリスト化したシート名と、取り出すデータのセル番号を指定する入力表を作成します。

プロシージャー名を「変数設定()」とします。その中で「シート名取出し()」プロシージャーを呼び出します。

VBAコードとしては、次のようになります。

vbacellsfonteyecatch 「フォント」の操作を最速理解する エクセルVBA vbacellspaintbackeyecatch 「塗りつぶし」背景色をVBAで記述する
VBA
Sub 変数設定()

        Call Module1.シート名取出し
        Workbooks("データ収集.xlsm").Activate
        With Worksheets("設定")
            .Range("A3") = "シート名"
            .Range("B2") = "シートチェック"
            .Range("B3") = "抽出に〇"
            .Range("C2") = "項目名"
            .Range("C3") = "セル番号"
            .Range("D1") = "抽出データ1"
            .Range("E1") = "抽出データ2"
            .Range("F1") = "抽出データ3"
            .Range("G1") = "抽出データ4"
            .Range("H1") = "抽出データ5"
            .Range("D2", "H3").Interior.ColorIndex = 6
            With .Range("A1", "H1")
                .Font.Size = 15
                .Font.ColorIndex = 5
                .Font.Bold = True
            End With
            With .Range("A2", "C3")
                .Font.Size = 15
                .Font.ColorIndex = 5
                .Font.Bold = True
            End With
                .Range("D1", "H3").Borders.LineStyle = xlContinuous
                .Range("A2", "C3").Borders.LineStyle = xlContinuous
                .Columns.AutoFit
        End With
        MsgBox "取り出し条件設定シートの作成が完了しました", _
                    vbInformation, "複数エクセルファイルデータ収集"
End Sub

「変数設定()」プロシージャー名を実行すると、

ファイル選択画面が表示されます。代表的なファイルを選択します。

detasyuu003aa
矢印下001

ファイルを選択して「開く」ボタンをクリックすると、「設定」シートは、このように表示されます。

detasyuu004aa

入力表は少し見やすくするために、入力指示項目のタイトルの文字スタイルを変更しています。

入力部分は、セルに色を付けています。

この部分は、お好みでカスタマイズしていただければと思います。

プロシージャー実行のボタンを設置する

あとは、「設定」シートに実行ボタンを設置します。

設置位置はJ・K列に設置しています。(ボタン位置は適宜でOKです。)

設置方法は、「開発タブ」→「挿入ボタン(プルダウン)」→「フォームコントロール」→「ボタン」

位置決め後、「ボタン」右クリック→「マクロの登録」→「変数設定プロシージャー」を指定です。

ボタン名称の変更は「テキストの編集」から行います。

参考記事

設置方法はこちら↓も参考になります。

「データ抽出ボタンを設置する」

こちらの記事も参考になります。

vbabuttoneyecatch コマンドボタンをシートに設置する2つの方法

これでボタン設置完了です。

ここまでのまとめ

detasyuusetteip007

サンプルの画像を参考にすると、

抽出するデータ数はこの場合、5個/シート ✖ 72シート で合計360個のデータを取り出すことが出来ます。

もちろん、ファイルにあるシート数が多ければもっと取り出せるデータ量は多くなります。

が、その分パソコンに負担が多くかかりますので、一度の作業でなく多ければ何回かに分けて行うことをお勧めします。

次回は、入力した条件に従ってデータを取り出すためのVBAコードを解説していきたいと思います。

次の記事をご覧になるには、こちら↓になります。

datasyusyueyecatch003 指定条件で複数ファイルからデータ取得。エクセルVBAコード

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう!

エクセルVBA業務ツールで日常の業務改善を行いましょう。

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。