【Excel VBA】別ブックからデータを抜き出す処理(その1)

「ファイルを開いてデータを取得する処理」について、前回・前々回と2回に分けて紹介しましたが、「データを取得」する部分をより具体的に書いていきたいと思います
請求書や報告書などのような、決まった様式のシートからデータをピンポイントで抜き出す作業はVBAを使えば劇的に効率化(時短)できます
では今回も、「汎用でだれでも使えて活用できるように考えてvbaを使う」というポリシーで書いていこうと思います

表になっていない定型様式のデータを集計シートに表形式で抜き出す方法

・設定したターゲットのセル番地をFindメソッドで取得する
・サンプル様式(ターゲットシート)への設定例
・汎用で使えるように考えた結果こんな感じに

設定したターゲットのセル番地をFindメソッドで取得する

動かすVBAはこれです。次の設定が済んだら実際に動かしてみてください

'ターゲットのセル番地を取得する処理
'同じ符号を複数付けた場合最初に見つかった1個だけになります
Sub GetTargetCells()
    Dim strTarget As String
    Dim strMark As String
    Dim i As Long
    Dim m As Long
    Dim FCell As Range
    Dim strFind As String
    
    With WorkSheets("Main")
        .Range("C:D").Clear     'Targetセル番地保存エリアをクリア
        strMark = .Range("B2")  'Targetの設定用符号をセット
        WorkSheets("取得セル設定").Activate '取得セル設定シートをアクティブに
        m = Application.CountIf(Cells, strMark & "*")   'Targetの数を調べる
        For i = 1 To m
            strTarget = strMark & i '符号+番号
            Set FCell = Cells.Find(What:=strTarget) 'Findメソッド
            If FCell Is Nothing Then
                strFind = "見つかりません"
            Else
                strFind = FCell.Address 'Targetのアドレス代入
            End If
            'Mainシートに書き込む
            .Cells(i, 3) = strTarget
            .Cells(i, 4).Formula = strFind
        Next
        .Activate
    End With
End Sub

サンプル様式(ターゲットシート)への設定例

事前準備(設定)

・シートを3つ用意します「Main」「取得セル設定」「取得データ」
・「Main」シートのA列は取得対象のフォルダとファイル名が入る
・「Main」シートの「B1」セルに「記号」を入力(#,$,&,!など任意)
・「取得セル設定」シートにターゲットの様式を貼り付けます
・「取得セル設定」に貼った様式のデータを取得したい部分に
 「記号」+数字(例:#1)をセットします(抜き出す順番を番号で指定)

「取得セル設定」シートの設定例(見本)

こんな感じでデータを抜き出したいセルに「記号」+「番号」を順番に入れます

設定が終わったらVBAを動かしてみましょう
どうですか? 一瞬で終わったと思います
C列に「記号」+番号、D列にセル番地を書き出しているはずです

汎用で使えるように考えた結果こんな感じに

【汎用で使えるように設計するには】
・VBAコード内に特定の「セル番地」はなるべく書かないようにする
・設定シートを用意して(マクロの動作をコントロール)
・「セル番地」などのターゲットは変更できるようにする
・VBAコード内をあとで書き換えなくても良いように考える
・流用して使えるように考える

まだまだあると思いますが。。。(^^)

シート名などもマクロで自動取得するように変更

・上はMainシートの画像です。F列に使うシート名を入力ておいて読み込む設定に
・変更したコードはこちら

'ターゲットのセル番地を取得する処理
'同じ符号を複数付けた場合最初に見つかった1個だけになります
Sub GetTargetCells()
    Dim strTarget As String
    Dim strMark As String
    Dim i As Long, m As Long
    Dim FCell As Range
    Dim strFind As String
    Dim shSeting As String

    With WorkSheets("Main")
        shSeting = .Range("F2") 'Target設定用シート名
        .Range("C:D").Clear     'Targetセル番地保存エリアをクリア
        strMark = .Range("B2")  'Targetの設定用符号をセット
        Sheets(shSeting).Activate 'Target設定用シートをアクティブに
        m = Application.CountIf(Cells, strMark & "*")   'Targetの数を調べる
        For i = 1 To m
            strTarget = strMark & i '符号+番号
            Set FCell = Cells.Find(What:=strTarget) 'Findメソッド
            If FCell Is Nothing Then
                strFind = "見つかりません"
            Else
                strFind = FCell.Address 'Targetのアドレス代入
            End If
            'Mainシートに書き込む
            .Cells(i, 3) = strTarget
            .Cells(i, 4).Formula = strFind
        Next
        .Activate
    End With
End Sub

・こうしておけばシート名はコードを修正せずに自由に変更できます

ここまでの「まとめ」

今までの記事内容を整理します
・別ファイルを保存しているフォルダを指定
・フォルダ内のファイル名を取得
・ターゲットのデータを取り出すための設定
次回は、今までの設定で取得できた情報を使って、実際の別ファイル(ブック)からデータを取り出していく作業部分を解説していきたいと思います
是非ご期待ください!

サンプルファイルをダウンロードできます(下記リンク先へ)

記事で使用したサンプルファイルがダウンロードできるページを設置しました
こちらからご利用ください