大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、別ブックを参照する方法について、ご紹介します。簡単な方法として、「別ブックを開いて参照する方法」があります。高速に別ブックを参照したい場合は、「数式を使って参照する方法」が使えます。具体的なVBAコードを使って、解説してきます。

はじめに

この記事では、別ブックを参照する方法について、ご紹介します。

簡単に別ブックを参照したい場合は、「別ブックを開いて参照する方法」があります。

少し見づらくてもいいので、高速に別ブックを参照したい場合は、「数式を使って別ブックを参照する方法」が使えます。

では、別ブックを参照する方法について、解説していきます。

この記事で紹介すること

  • 別ブックを参照する方法

目次から見たい項目へ移動すると便利ですよ。

目次

VBAでWorkbooksを使って別ブックを参照

VBAで、別ブックを参照する場合は、「Workbooks」を使います。

Workbooks.Openで開いて別ブックを参照

別ブックを参照する手順としては、

  • Workbooks.Openで別ブックを開く
  • Workbooksを使って別ブックを参照

という流れです。

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

Sub TEST1()
    
    '別ブックを開く
    Workbooks.Open Filename:="C:\TEST\TEST.xlsx"
    
    Set Wb1 = ThisWorkbook 'このブック
    Set Wb2 = Workbooks("TEST.xlsx") '別ブック
    
    '別ブックの値を取得
    Wb2.Worksheets("Sheet1").Range("A1:C1").Copy Wb1.Worksheets("Sheet1").Cells(1, 1)
    Wb2.Close '別ブックを閉じる
    
End Sub

同じフォルダに、「TEST.xlsx」というブックを用意しました。

フォルダ構成

フォルダ構成

「TEST.xlsx」には、「Sheet1」に次にように値を入力しておきました。

別ブックの値

別ブックに入力した値

では、VBAコードを実行して、別ブックを参照してみます。

別ブックを参照

別ブックを参照して、セルの値を取得した結果

別ブックの値を、参照することができました。

この方法がVBAとしては、シンプルな方法になります。

ただ、ブックを開いて参照するので、少し時間がかかります。

次で、時間を計測してみます。

100個のブックを参照する時間を計測

別ブックを開いて参照する方法で、100個のブックを参照するのにかかる時間を、計測してみます。

VBAコードは、こんな感じになります。

Sub TEST2()
    
    t = Timer
    
    For i = 1 To 100
        '別ブックを開く
        Workbooks.Open Filename:="C:\TEST\TEST" & WorksheetFunction.Text(i, "000") & ".xlsx"
        
        Set Wb1 = ThisWorkbook 'このブック
        Set Wb2 = Workbooks("TEST" & WorksheetFunction.Text(i, "000") & ".xlsx") '別ブック
        
        '別ブックの値を取得
        Wb2.Worksheets("Sheet1").Range("A1:C1").Copy Wb1.Worksheets("Sheet1").Cells(i, 1)
        Wb2.Close 'ブックを閉じる
    Next
    
    Debug.Print Timer - t & " 秒"
    
End Sub

100個のブックを用意しました。

100個の別ブック

100個のブックを用意

それぞれファイルには、「Sheet1」にデータを入力しています。

100個の別ブックの値

TEST001.xlsx
TEST002.xlsx
TEST100.xlsx

では、VBAコードを実行して、100個の別ブックを開いて、参照してみます。

100個の別ブックを開いて参照

100個の別ブックを開いて、参照した結果

100個の別ブックを参照できました。

問題のかかった時間です。

かかった時間

「87.70 秒」となりました。

結構、時間かかりますね。

大量の別ブックを参照したい場合は、次に紹介する数式を使って参照する方法を使うと、高速化できます。

VBAで数式を使って別ブックを参照

別ブックを参照する方法として、数式を使って参照する方法があります。

セルで、「=数式」と入力して参照するやつです。

これが、結構、高速です。数式を使って別ブックを参照してみます。

数式を使って別ブックを参照

数式を使って、別ブックを参照する方法です。

まず、元のブックと、参照先のブックを開いて、参照したいセルを「=」で持ってきます。

数式を使って別ブックを参照

数式を使って別ブックを参照する

すると、参照先の別ブックの値を取得することができます。

参照先の別ブックを閉じると、セルに入力した数式が、フルパス表示になります。

別ブックを閉じる

別ブックを閉じると数式がフルパス表示になる

フルパス表示になりました。

別ブックを参照した数式を取得

='C:\TEST\[TEST.xlsx]Sheet1'!$A$1

このフルパスの数式を使えば、別ブックを参照することができます。

ちなみに、ネットワークドライブの別ブックも、参照することができます。

別ブックが閉じていても、別ブックを参照できる点が、ポイントです。

VBAで数式を使って別ブックを参照

では、VBAで数式を使って、別ブックを参照してみます。

別ブックのセル「A1」を参照

先ほどの数式で入力した値を使って、別ブックのセル「A1」を参照するVBAコードです。

Sub TEST3()
    
    With ActiveSheet
        '数式を使って、別ブックを参照
        .Cells(1, 1) = "='C:\TEST\[TEST.xlsx]Sheet1'!$A$1"
        '数式を値に変換
        .Cells(1, 1).Value = .Cells(1, 1).Value
    End With
    
End Sub

最後に、数式を値に変換しています。

フォルダ構成は、次のようにしています。

フォルダ構成

元ブックが「00_別ブックを参照.xlsm」で、参照先のブックが「TEST.xlsx」となります。

参照先のブック「TEST.xlsx」には、次のように、値が入力されています。

参照先のブックのデータ

では、VBAコードを実行してみます。

別ブックのセルA1を参照した結果

別ブックのセルA1を参照できました。

数式に変数を入れる

ただし、このままの数式「='C:\TEST\[TEST.xlsx]Sheet1'!$A$1"」では、応用ができませんので、ブック名やシート名、セル範囲を変数にしてみます。

Sub TEST4()
    
    With ActiveSheet
        a = "TEST.xlsx" '別ブック名
        b = "Sheet1" 'シート名
        c = .Cells(1, 1).Address 'セル範囲
        '数式を使って、別ブックを参照
        .Cells(1, 1) = "='C:\TEST\[" & a & "]" & b & " '!" & c
        '数式を値に変換
        .Cells(1, 1).Value = .Cells(1, 1).Value
    End With
    
End Sub

こんな感じで、ブック名やシート名、セル範囲を変数にすると応用がききます。

では、実行してみます。

別ブックを参照した結果

同じように別ブックのセルA1を参照することができました。

別ブックのセル「A1~C1」を参照

あとは、別ブックのセル「A1~C1」の範囲のデータを参照したいので、Forでループして、データを参照してみます。

Sub TEST5()
    
    With ActiveSheet
        a = "TEST.xlsx" '別ブック名
        b = "Sheet1" 'シート名
        For i = 1 To 3
            'セル範囲
            c = .Cells(1, i).Address
            '数式を使って、別ブックを参照
            .Cells(1, i) = "='C:\TEST\[" & a & "]" & b & " '!" & c
            '数式を値に変換
            .Cells(1, i).Value = .Cells(1, i).Value
        Next
    End With
    
End Sub

では、別ブックのセル「A1~C1」を参照してみます。

別ブックを参照した結果

別ブックのセル「A1~C1」を参照することができました。

VBAコードが少し見づらいではありますけども、かなり高速です。

次で、検証してみます。

100個のブックを参照する時間を計測

100個のブックを、数式を使って参照するという方法で、時間を計測してみます。

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

Sub TEST6()
    
    t = Timer
    
    For i = 1 To 100
        For j = 1 To 3
            With ActiveSheet
                a = "TEST" & WorksheetFunction.Text(i, "000") & ".xlsx" '別ブック名
                b = "Sheet1" 'シート名
                c = Cells(1, j).Address 'セル範囲
                '数式を使って、別ブックを参照
                .Cells(i, j) = "='C:\TEST\[" & a & "]" & b & "'!" & c
                '数式を値に変換
                .Cells(i, j).Value = .Cells(i, j).Value
            End With
        Next
    Next
    
    Debug.Print Timer - t & " 秒"
    
End Sub

別ブックを100個、用意しました。

100個の別ブック

別ブックを100個用意した

それぞれのブックには、セル「A1~C1」の範囲にデータが入力されています。

100個の別ブックのデータ

ブックのデータ1個目
ブックのデータ2個目
ブックのデータ100個目

では、100個の別ブックを、数式を使って参照してみます。

数式を使って100個の別ブックを参照

100個の別ブックを、数式を使って参照した結果

100個の別ブックを、数式を使って参照できました。

問題の時間です。

かかった時間

「1.41 秒」となりました。

かなり速いですよね。別ブックを毎回開いて参照した場合では、「87.70 秒」でした。

ブックを閉じたまま参照できるので、速いです。

ちなみに、配列を使うともうちょっと速くなります。

次で検証してみます。

配列を使って高速化

数式を使って別ブックを参照する方法で、さらに配列を使って、高速化してみます。

VBAコードは、こんな感じになります。

Sub TEST7()
    
    t = Timer
    
    '配列を作成
    Dim Arr
    ReDim Arr(1 To 100, 1 To 3)
    
    For i = 1 To 100
        For j = 1 To 3
            With ActiveSheet
                a = "TEST" & WorksheetFunction.Text(i, "000") & ".xlsx" '別ブック名
                b = "Sheet1" 'シート名
                c = Cells(1, j).Address 'セル範囲
                '数式を配列に格納
                Arr(i, j) = "='C:\TEST\[" & a & "]" & b & "'!" & c
            End With
        Next
    Next
    
    With ActiveSheet
        '配列をセルに入力
        .Range("A1").Resize(UBound(Arr, 1), UBound(Arr, 2)) = Arr
        '数式を値に変換
        .Range("A1").CurrentRegion.Value = .Range("A1").CurrentRegion.Value
    End With
    
    Debug.Print Timer - t & " 秒"
    
End Sub

先ほどと同じ100個の別ブックを使います。

100個の別ブック

別ブックを100個用意した

それぞれのブックには、セル「A1~C1」の範囲にデータが入力されています。

100個の別ブックのデータ

ブックのデータ1個目
ブックのデータ2個目
ブックのデータ100個目

では、実行します。

数式と配列を使って100個の別ブックを参照

数式を使って別ブックを参照する方法で、さらに配列を使って高速化した結果

まずは、同じように100個の別ブックを参照できました。

では、問題の時間です。

かかった時間

「0.37 秒」となりました。

配列を使わないで、セルに入力した場合は、「1.41 秒」でしたので、少しだけ速くなっています。

配列を使って、一括でセルに入力したので、その分速くなったという感じです。

別ブックを、高速に参照したい場合は、数式を使って参照する方法を、検討してみる価値ありです。

おわりに

この記事では、別ブックを参照する方法について、ご紹介しました。

簡単に別ブックを参照したい場合は、別ブックを開いて参照する方法があります。

少し見づらくてもいいので、高速に別ブックを参照したい場合は、数式を使って別ブックを参照する方法が使えます。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す