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

datasyusyueyecatch003

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

 

今回は、

先回作成した「抽出条件の入力表」に入力された内容で、資料となるエクセルファイルからデータを取得するVBAコードを組み立てます。

 

設定シートに表示された「抽出条件の入力表」で

「シート名」の中で、データ抽出を行う「〇」マークが付いているシートで

抽出データで指定した項目セル番号に基づいて値を取り出してきます。

 

その前に

もう一度、一つ前の記事をチェックされるのは、こちら↓になります。

エクセルファイルからデータ収集。VBA取り出し条件の楽々設定方法

 

指定条件でデータ取得するVBAコードの組み立ての流れ

 

データを取得するためのVBAコードはこのような内容で組み立てて行きます。

資料となるエクセルファイルが保存されているフォルダーを選択指定します。

 

(「抽出シート」で)資料となるエクセルファイルのファイル名をリストとして一覧表示します。

 

(「設定」シートで)抽出マークを付けたシート名を配列を使って要素化します。項目名とセル番号を変数化しておきます。

 

それぞれのエクセルファイルごとに、マーク付けされたシートの指定されたセル位置のデータを順番に取得していきます。

順番に取得したデータは「抽出シート」に一覧表形式で表示されていきます。

 

データ取得のVBAコード

 

今回の作業は、「設定」シートの「抽出実行」ボタンで操作するプロシージャーを作成するということです。

detasyuu005aa

 

VBAコードは、先回作成したモジュール(Module1とか)の続きに記述します。

 

ファイル名をリスト化します

 

資料となるファイルの保存先フォルダーを指定して、その中にあるファイル名をリスト化します。

リストは、「抽出リスト」シートに作成します。

 

フォルダーを指定するVBAコード

 

まずは、資料となるエクセルファイルの置いてあるフォルダーを選択指定するVBAです。

プロシージャー名を「フォルダー選定()」としています。

Sub フォルダー選定()

 

  With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = True Then
      Path = .SelectedItems(1)

    Else
      Exit Sub

    End If
  End With

 

End Sub

 

MEMO

フォルダをダイアログ指定する方法はこちらの記事を参考にしてください。

Excel VBA 保存先フォルダをダイアログ指定で変数化

 

指定したフォルダーからファイル名を抽出

 

ファイル名を取り出すコードの作成を行います。

このコードの中で、「フォルダー選択」プロシージャーを呼び出します。

プロシージャー名を「データ抽出()」としています。

Sub データ抽出()

 

Dim Fn As String
Dim mb As Workbook
Set mb = ThisWorkbook

 

フォルダー選定

 

i = 3
Fn = Dir(Path & “\*.xls”)
  Do While Fn <> “”
    i = i + 1
    mb.Activate

    Worksheets(“抽出リスト”).Select
    Cells(i, 1) = Fn
    Fn = Dir()
  Loop

MsgBox “全部で” & i – 3 & “個ファイルがありました”

End Sub

 

MEMO

Dir関数についてはこちらを参考にしてください。

エクセルVBA 要らないファイルを自動削除するコードの作り方

 

揃った抽出条件によるデータ取出しVBAコード

 

シート名を変数として扱えるようにする

 

「設定」シートのシートリストでチェック欄に「〇」を付けたシートを変数として扱えるようにコード化します。

プロシージャー名を「シート選択()」としています。

Sub シート選択()

 

Dim n As Long

 

  Workbooks(“データ収集.xlsm”).Activate
  Worksheets(“設定”).Select
  NO = 0
  Arow = Cells(Rows.Count, 1).End(xlUp).Row

  For n = 4 To Arow
    If Range(“B” & n) = “〇” Then
      Sna(NO) = Range(“A” & n)
      NO = NO + 1
    End If
  Next n

 

  CndN = Range(“D2”)
  Cnd = Range(“D3”)
  CneN = Range(“E2”)
  Cne = Range(“E3”)
  CnfN = Range(“F2”)
  Cnf = Range(“F3”)
  CngN = Range(“G2”)
  Cng = Range(“G3”)
  CnhN = Range(“H2”)
  Cnh = Range(“H3”)

End Sub

 

MEMO

データ入力済セルの最終行番号取得についてはこちらを参考にしてください。

データ入力済セルの最終行番号を取得するVBAコード

 

変数化した抽出条件でデータを取り出すVBAコード

 

このVBAコードの中で、「フォルダー選定()」と「データ抽出()」プロシージャーを呼び出します。

プロシージャー名を「各数値取出し()」としています。

 

MEMO

For~Nextの使い方については、この記事も参考にしてください。

エクセルVBA!For~Nextの入れ子ループを最速に理解する

If条件文については、この記事も参考にしてください。

「If条件文」のVBAコードの組み方。条件の絞り方を最速に理解。

For Eachの使い方については、この記事を参考にしてください。

VBA 回数不定のループ処理はDo LoopとFor Each

Offsetの使い方については、この記事を参考にしてください。

Offsetプロパティの使い方。セルや選択範囲を移動するVBAコード

ChDirの使い方についてはこちらの記事を参考にできます。

カレントフォルダを簡単に変更できるChDirステートメントの使い方

Callステートメントの使い方についてはこちらの記事を参考にできます。

Callステートメントはプロシージャーの部品化に必須項目 エクセルVBA最速理解

 

Sub 各数値取出し()

 

Dim ws As Worksheet
Dim flg As Boolean
Dim s As Long, Fna As String
Dim Vd As Variant, Ve As Variant, Vf As Variant, Vg As Variant, Vh As Variant
Dim Flag As Boolean

 

データ抽出
シート選択

 

On Error Resume Next

 

  Workbooks(“データ収集.xlsm”).Activate

  Worksheets(“抽出リスト”).Select
  Range(“B1”) = “シート名”
  Range(“A3”) = “ファイル名”
  Range(“B3”) = “チェック欄”

  For s = 4 To i
    Workbooks(“データ収集.xlsm”).Activate
    Worksheets(“抽出リスト”).Select

    Fna = Range(“A” & s)

    ChDir Path
    Workbooks.Open Fna

    k = 0
    Flag = False

    For j = 0 To NO – 1

      For Each wsh In Workbooks(Fna).Worksheets
        If wsh.Name = Sna(j) Then
          Flag = True
          Exit For
        End If
        Flag = faluse
      Next wsh

      If Flag = True Then
        Worksheets(Sna(j)).Select
        Vd = Range(Cnd)
        Ve = Range(Cne)
        Vf = Range(Cnf)
        Vg = Range(Cng)
        Vh = Range(Cnh)

        Workbooks(“データ収集.xlsm”).Activate
        Worksheets(“抽出リスト”).Select
          Range(“C1”).Offset(, 6 * k) = Sna(j)
          Range(“C2”).Offset(, 6 * k) = “項目名”
          Range(“C3”).Offset(, 6 * k) = “セル番号”
          Range(“D2”).Offset(, 6 * k) = CndN
          Range(“E2”).Offset(, 6 * k) = CneN
          Range(“F2”).Offset(, 6 * k) = CnfN
          Range(“G2”).Offset(, 6 * k) = CngN
          Range(“H2”).Offset(, 6 * k) = CnhN
          Range(“D3”).Offset(, 6 * k) = Cnd
          Range(“E3”).Offset(, 6 * k) = Cne
          Range(“F3”).Offset(, 6 * k) = Cnf
          Range(“G3”).Offset(, 6 * k) = Cng
          Range(“H3”).Offset(, 6 * k) = Cnh

          Range(“D” & s).Offset(, 6 * k) = Vd
          Range(“E” & s).Offset(, 6 * k) = Ve
          Range(“F” & s).Offset(, 6 * k) = Vf
          Range(“G” & s).Offset(, 6 * k) = Vg
          Range(“H” & s).Offset(, 6 * k) = Vh
          k = k + 1
      Else
        k = k + 1
      End If

  Workbooks(Fna).Activate
    Next j

  Workbooks(Fna).Close False

  Next s

  Columns.AutoFit

End Sub

 

「設定」シートで5つ設定できる「抽出データ」項目で、5つすべて指定しなかった(一部空欄のままにした)場合エラーが発生します。

MEMO
一部のセル番号が指定されていない時に、Range(・・)のコード実行でエラーが発生しVBAがストップします。
そのエラーストップを回避するために「On Error Resume Next」を記述しています。

抽出マークを付けたシートが資料となるすべてのエクセルファイルに存在するとは限らないと思います。

もしそのシート名がない場合は、抽出データなしとして通出データ一覧表には「空欄」として表示されます。

 

変数の宣言方法

 

各VBAの動作を、それぞれのプロシージャーに分けて記述しました。

そのプロシージャーの中で使用した変数を、他のプロシージャーの中でも使用できるようにします。

変数の宣言方法を以下のようにします。

同一モジュール内でプロシージャーを超えて変数が有効になります。

モジュールの最初に記述します。

 

MEMO

宣言位置で変数の適用範囲を変化させるのは、この記事を参考にしてください。

変数の宣言方法で適用範囲をコントロールするVBA記述

 

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 String
Private Arow As Long, NO As Long
Private Flag As Boolean

 

モジュールの記述はこのようになります

 

detasyuu006aa

プロシージャー起動ボタンを設置

 

「設定」シートに「抽出実行」ボタンを設置します。

このボタンに紐づけるプロシージャーは「各数値取出し()」になります。

ご不明な場合、

設置方法については、前回の記事を参考にして下さい。

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

 

ここまでのまとめ

 

ここまでで、データの抽出取得は完了です。

一度に大量に処理を行うと処理作業に少々時間がかかることがあるかもしれません。

データ抽出には、各資料ファイルを開いて、データを取得して、ファイルを閉じてとなります。

パソコンスペックに対して処理数を調節しながら使用されることをお勧めします。

 

抽出条件を限定すれば、データ取得にファイルを開かずに処理するコードもありますが、実用面でシンプル条件になり過ぎる弊害がありましたので今回採用はしませんでした。

 

次の記事は、データ取得・一覧表作成後の、次の使用のためのリセット(データクリア)についてのVBAコードです。

 

短期間でエクセルVBAの独学習得を目指したいなら

 

エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。

けれども、

出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。

独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。

先人が行った勉強方法をあなたがそのまま利用すればよいということです。

vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

独習のための大切な7つのポイントは、上記記事にて解説しています。

重要ワード

独習によるVBA習得のキーワードは、

出来るだけ多くの実例に触れること!

です。

正直、VBAの学習について自分の周りの仕事(業務)からだけ実例を得るのでは効率良い習熟は無理です。

ハッキリ言って、

本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習も取り入れて行うことが、

手っ取り早く短期間習得できるというのは間違いないでしょう。

 

VBAコード参考記事

エクセルVBA 独学の始め方 学習のポイントを情報公開

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

エクセルVBAを自習するのは結構大変ですが、ポイントとコツさえ押さえれば独学で習得も可能です。 独学のみの店長でもこのような記事が書けるようになりました。それは独習の仕方を実践的に変えたからです。

エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておきたい内容を纏めています。

「VBA最速理解」の記事一覧を開く

 

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

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