作業用ブックを開く

ここでは、ユーザーに分からないようにブックを操作する方法をご紹介します。たとえば、Book1を表示している状態で、裏で開いたBook2を操作するような使い方です。

見えないように開く

ブックを開くときは、WorkbooksコレクションのOpenメソッドを使います。これは、難しくないですね。

Sub Sample1()
    Workbooks.Open "Book2.xlsx"
End Sub

もちろん、ブックを開くと、開いたブックがアクティブになります。ユーザーに分からないようにブックを開くには、開いたあとで、アクティブブックを切り替えなければなりません。

Sub Sample1()
    Workbooks.Open "Book2.xlsx"
    ''別のブックをアクティブにする
End Sub

もし、このマクロが書かれているブックをアクティブにするのなら、ThisWorkbookをアクティブにしてやればいいです。ApplicationオブジェクトのScreenUpdatingプロパティにFalseを設定すると画面の更新が抑止されますので、Book2を開いたことは気づかれません。

Sub Sample1()
    Application.ScreenUpdating = False
    Workbooks.Open "Book2.xlsx"
    ThisWorkbook.Activate
    Application.ScreenUpdating = True
End Sub

もし「マクロが書かれているブック」ではなく、任意のブックに戻りたいのでしたら、戻るブックを覚えておかなければなりません。これには2つの方法があります。

【戻るブックの名前を文字列として覚えておく方法】

Sub Sample1()
    Dim ReturnBook As String
    ReturnBook = ActiveWorkbook.Name
    Application.ScreenUpdating = False
    Workbooks.Open "Book2.xlsx"
    Workbooks(ReturnBook).Activate
    Application.ScreenUpdating = True
End Sub

【戻るブックをオブジェクト型変数に格納しておく方法】

Sub Sample1()
    Dim ReturnBook As Workbook
    Set ReturnBook = ActiveWorkbook
    Application.ScreenUpdating = False
    Workbooks.Open "Book2.xlsx"
    ReturnBook.Activate
    Application.ScreenUpdating = True
End Sub

開いたブックを覚えておく

開いたブックを操作するとき、ファイル名を指定してもいいのですが、変数に入れておいた方が何かと便利です。これも上と同じように、ブックの名前を文字列として覚えておく方法と、オブジェクト型変数に格納しておく方法があります。

【開いたブックの名前を文字列として覚えておく方法】

Sub Sample1()
    Dim ReturnBook As String, TargetBook As String
    ReturnBook = ActiveWorkbook.Name
    Application.ScreenUpdating = False
    Workbooks.Open "Book2.xlsx"
    TargetBook = ActiveWorkbook.Name
    Workbooks(ReturnBook).Activate
    Application.ScreenUpdating = True
    MsgBox TargetBook
End Sub

【開いたブックをオブジェクト型変数に格納しておく方法】

Sub Sample1()
    Dim ReturnBook As Workbook, TargetBook As Workbook
    Set ReturnBook = ActiveWorkbook
    Application.ScreenUpdating = False
    Set TargetBook = Workbooks.Open("Book2.xlsx")
    ReturnBook.Activate
    Application.ScreenUpdating = True
    MsgBox TargetBook.Name
End Sub

ブックを開くOpenメソッドは、開いたブック(Workbookオブジェクト)を返します。そこで、Openメソッドの返り値(開いたブック)をオブジェクト型変数に格納してやります。このとき、Openメソッドの引数を括弧で囲むのを忘れないでください。VBAで、引数を括弧でかこむときのルールについては、下記ページをご覧ください。

括弧はどんなときに使うの？

使い方のサンプル

例をお見せします。まず、アクティブブックに次のようなデータが入力されているとします。

【アクティブブック】

商品ごとの単価は、Book2.xlsxの[Sheet1]に入力されているとします。

【Book2.xlsxの[Sheet1]】

アクティブブックの「金額」を計算するには、Book2.xlsxを開いて、それぞれ該当する「単価」を調べなければなりません。たとえば、次のような感じですね。

Sub Sample2()
    Dim TargetBook As Workbook, i As Long, j As Long
    Set TargetBook = Workbooks.Open("Book2.xlsx")
    ThisWorkbook.Activate
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        For j = 2 To Cells(Rows.Count, 1).End(xlUp).Row
            If Cells(i, 1) = TargetBook.Sheets("Sheet1").Cells(j, 1) Then
                Cells(i, 3) = Cells(i, 2) * TargetBook.Sheets("Sheet1").Cells(j, 2)
                Exit For
            End If
        Next j
    Next i
    TargetBook.Close
End Sub

う～ん・・・ちょっと煩雑になりましたね。Withを使ってみましょうか。

Sub Sample2()
    Dim TargetBook As Workbook, i As Long, j As Long
    Set TargetBook = Workbooks.Open("Book2.xlsx")
    ThisWorkbook.Activate
    With TargetBook.Sheets("Sheet1")
        For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
            For j = 2 To Cells(Rows.Count, 1).End(xlUp).Row
                If Cells(i, 1) = .Cells(j, 1) Then
                    Cells(i, 3) = Cells(i, 2) * .Cells(j, 2)
                    Exit For
                End If
            Next j
        Next i
    End With
    TargetBook.Close
End Sub

オブジェクト型変数として、たとえばTargetSheetを宣言して、Book2.xlsxのSheet1を入れちゃう手もありますね。
もうひとつ、検索で探すやり方もご紹介します。

Sub Sample2()
    Dim TargetBook As Workbook, i As Long, FoundCell As Range
    Set TargetBook = Workbooks.Open("Book2.xlsx")
    ThisWorkbook.Activate
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Set FoundCell = TargetBook.Sheets("Sheet1").Range("A:A").Find(Cells(i, 1))
        If Not FoundCell Is Nothing Then
            Cells(i, 3) = Cells(i, 2) * FoundCell.Offset(0, 1)
        End If
    Next i
    TargetBook.Close
End Sub

発想しだいで、いろいろできますね。

← ブックの一覧を取得する | ファイルの存在を調べる →