【ExcelVBA】閉じているExcelファイルからデータを取り出してExcelのシートに出力する方法

この記事では、閉じているExcelファイルからデータを取り出してExcelのシートに出力する方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

Table of Contents

【動画】閉じているExcelファイルからデータを取り出してExcelのシートに出力する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

閉じている6つのExcelファイル「1月.xlsm」「2月.xlsm」「3月.xlsm」「4月.xlsm」「5月.xlsm」「6月.xlsm」の中からデータを取り出して「0164.xlsm」のシートに出力しています。

なお、シートは各ファイルごとに作成し、シート名をそのファイル名で付けています。
(「1月.xlsm」なら1月、「2月.xlsm」なら2月)

マクロ作成の流れ

STEP.1

Excelファイルのデータを出力するためのシートを生成する

Excelファイルのデータを出力するためのシートを生成します。
なお、今回はシートの有無チェックを行い、シートがなければ生成し、すでにシートが存在する場合はシートの生成は行わず、そのシートをクリアしています。

STEP.2

STEP.1で生成したデータを貼り付ける先のシートにデータを貼り付ける

STEP.1で生成したデータを貼り付ける先のシートにデータを貼り付けます。

STEP.3

貼り付けた値は計算式を使って取得先のExcelファイルを参照しているので、値そのものを再入力する

貼り付けた値は計算式を使って取得先のExcelファイルを参照しているので、値そのものを再入力します。
参照先のExcelファイルが削除されたり移動されたりした場合、計算式が入ったままだと値の参照ができなくなるので、値そのものを再入力させます。

STEP.4

データを取り込むExcelファイルの数だけSTEP.1からSTEP.3を繰り返す

データを取り込むExcelファイルの数だけSTEP.1からSTEP.3を繰り返します。

目次へ戻る

Excelファイルの例

今回用意したExcelファイルは次の通りです。

セルA2に閉じているExcelファイルの置き場を、セルA5からセルA2に閉じているExcelファイル名を記入しています。

この閉じているExcelファイルの置き場と閉じているExcelファイル名とマクロが読み込んでシートに出力します。

閉じたExcelファイルのデータ

閉じたExcelファイルのデータを取り込んだ結果

目次へ戻る

コードの例

Excelのマクロのコード(例)

Option Explicit

Private Sub btn_dataImport_Click()

    Dim cnt             As Integer      'カウンタ
    Dim filePath        As String       'データが入力されているExcelファイルの格納先
    Dim dataFile()      As Variant      'データが入力されているExcelファイル用配列
    Dim shtExistFlg     As Boolean      'シート存在確認フラグ
    Dim ws              As Worksheet    'Worksheet用変数
    Dim pstDdSheetNM    As String       'データを貼り付ける先のシート名
    
    'データが入力されているExcelファイルの格納先を取得する
    filePath = Worksheets("work").Range("filePath").Value
    
    'データが入力されているExcelファイル名全てを取得する
    dataFile = Worksheets("work").Range("A5:A10").Value
                     
    For cnt = 1 To UBound(dataFile)
    
        'データを貼り付ける先のシート名を取得する
        pstDdSheetNM = Split(dataFile(cnt, 1), ".")(0)
    
        'データを貼り付ける先のシート有無のチェック
        For Each ws In Worksheets
    
            If ws.Name = pstDdSheetNM Then
    
                'データを貼り付ける先のシート名が存在している場合
    
                '変数「shtExistFlg」にTrueを設定する
                shtExistFlg = True
    
            End If
    
        Next ws
    
        If shtExistFlg = False Then
    
            'データを貼り付ける先のシートが存在しない場合
    
            'データを貼り付ける先のシートを新規作成する
            Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
                           .Name = pstDdSheetNM
                               
        Else
    
            'データを貼り付ける先のシートが存在する場合
    
            'データを貼り付ける先のシートのセル全てをクリアする
            Worksheets(pstDdSheetNM).Cells.Clear
    
        End If
        
        With Worksheets(pstDdSheetNM)
        
            'データを貼り付ける先のシートにデータを貼り付ける
            .Range("A1:F9").Formula = "=if(ISBLANK('" & filePath & "\[" & dataFile(cnt, 1) & "]work'!A1), """",'" & filePath & "\[" & dataFile(cnt, 1) & "]work'!A1)"
                    
            '取得先のExcelファイルを参照している計算式がセルに入力されているので、値そのものを再入力する
            .Range("A1:F9").Value = .Range("A1:F9").Value
        
        End With
            
    Next cnt

End Sub

注目すべきコード①

最初に見て頂きたいのは13行目から16行目です。

    'データが入力されているExcelファイルの格納先を取得する
    filePath = Worksheets("work").Range("filePath").Value
    
    'データが入力されているExcelファイル名全てを取得する
    dataFile = Worksheets("work").Range("A5:A10").Value

以上のコードは、閉じているExcelファイルの置き場と閉じているExcelファイル名を取得するコードです。

閉じているExcelファイルの格納先

閉じているExcelファイル名

閉じているExcelファイルの格納先のフォルダとExcelファイル

注目すべきコード①

次に見て頂きたいのは21行目です。

        'データを貼り付ける先のシート名を取得する
        pstDdSheetNM = Split(dataFile(cnt, 1), ".")(0)

以上のコードは、生成するシートに付ける名前を取得するコードです。

今回、生成するシートには閉じているExcelファイルの名前を付けたいので、Excelファイルのファイル名から「.xlsm」を取り除いた文字列を取得して変数「pstDdSheetNM」に格納しています。

Split関数の第2引数に「.」を、さらに(0)を指定してSplit関数を実行すると、例えば「1月.xlsm」のExcelファイルの場合は文字列「1月」を返します。

ちなみに(0)ではなく、(1)を指定してSplit関数を実行すると文字列「xlsm」を返します。

注目すべきコード③

次に見て頂きたいのは24行目から52行目です。

        'データを貼り付ける先のシート有無のチェック
        For Each ws In Worksheets
    
            If ws.Name = pstDdSheetNM Then
    
                'データを貼り付ける先のシート名が存在している場合
    
                '変数「shtExistFlg」にTrueを設定する
                shtExistFlg = True
    
            End If
    
        Next ws
    
        If shtExistFlg = False Then
    
            'データを貼り付ける先のシートが存在しない場合
    
            'データを貼り付ける先のシートを新規作成する
            Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
                           .Name = pstDdSheetNM
                               
        Else
    
            'データを貼り付ける先のシートが存在する場合
    
            'データを貼り付ける先のシートのセル全てをクリアする
            Worksheets(pstDdSheetNM).Cells.Clear
    
        End If

以上のコードは、生成するシートの数だけ行うシート有無チェックと、シート生成処理を行うコードです。

24行目でExcelファイルの中のシートの数だけループを行うループ処理のfor文を記述し、26行目でシートの有無チェックを行います。

シートの有無チェックでは、マクロ側のExcelファイル内のシート名(ws.Name)と、閉じているExcelファイルの名前(pstDdSheetNM)が同じかどうかを判定しています。

もし同じの場合は、31行目でシートが存在しているか否かを知らせるフラグshtExistFlgにTrueを設定します。

同じではない場合はFalseのまま後続処理に進みます。

次に37行目で、先ほどのフラグshtExistFlgの値を判定し、もしフラグshtExistFlgがFalseの場合、つまりシートが存在しない場合はシートを生成します。

生成するシート名には閉じているExcelファイルの文字列を付与します。

一方、もしフラグshtExistFlgがTrueの場合(45行目)、つまりシートが存在する場合はシートを生成せず、そのシートをクリアします。(50行目)

注目すべきコード④

次に見て頂きたいのは54行目から62行目です。

        With Worksheets(pstDdSheetNM)
        
            'データを貼り付ける先のシートにデータを貼り付ける
            .Range("A1:F9").Formula = "=if(ISBLANK('" & filePath & "\[" & dataFile(cnt, 1) & "]work'!A1), """",'" & filePath & "\[" & dataFile(cnt, 1) & "]work'!A1)"
                    
            '取得先のExcelファイルを参照している計算式がセルに入力されているので、値そのものを再入力する
            .Range("A1:F9").Value = .Range("A1:F9").Value
        
        End With

以上のコードは、シートにデータを貼り付ける処理のコードです。

57行目で、閉じているExcelファイルからデータを取り出し、データを貼り付ける先のシートのA1からF9のセルに出力しています。

以上のコードだと変数が使われているので、変数の値をそのまま使ったコードを以下にお見せします。

            .Range("A1:F9").Formula = "=if(ISBLANK('C:\work\10_勉強\10_VBA関連\0164\data\[1月.xlsm]work'!A1), "",'C:\work\10_勉強\10_VBA関連\0164\data\[1月.xlsm]work'!A1)"