【VBA】別ブックのシートから情報を取得する方法【サンプルコード掲載】

 

別のブックから情報を取得したい！

 

こんな要望にお答えします。

 

この記事では、仕事でよく利用される別ブックから情報を取得する方法を詳しく解説しています。

 

別ブックの情報を取得するマクロはいろんなエクセル業務に応用することができるので、使い方をマスターをすればエクセル業務の効率を格段にアップさせることが可能です。

単に「別ブックから情報を取得する」といっても、情報を取得するにはいろんな方法があります。

 

代表的な方法は下記の3つです。

 

  • 別ブックの全シートの情報を取得する方法
  • 別ブックの最初のシートの情報を取得する方法
  • 別ブックの特定のシート情報を取得する方法

3つの方法を使い分けることができれば、たいていの課題を解決することができます。

 

この記事ではではサンプル事例を使って、別シートの情報を取得する3つの方法について詳しく解説していきます。

エクセル業務の効率をアップさせたい！複数のエクセルを開いて仕事をすることが多い！と言う方は是非チェックしてみてください。

 

広告

別ブックを操作する方法

別ブックのシートの情報を取得する説明の前に、別ブックを操作する方法をご紹介します。

 

別ブックを操作するには、

    • ブックを開く
    • 開いたブックを指定する
    • 開いたブックのシートを指定する

という、3つの設定をする必要があります。

 

この設定ができなければ別ブックのシートの情報を取得することはできません。

 

ブックを開く

ブックを開くにはOpenメソッドを使用します。

 

Openメソッドの基本構造がこちら

 

Workbooks.Open Filename:=〇〇

 

 

アイコンをダブルクリックしなくとも別のブックを開くことが可能です。

 

Openメソッドで指定するブックを開くには「ブックの保存場所」「 ブックの名前」が必要になります。

 

 

例えば、デスクトップにある「テスト.xlsx」というエクセルを開くには、

Workbooks.Open Filename:=C:\Users\鈴木\Desktop\テスト.xlsm

というコードになります。

 

Openメソッドは簡単に設定可能ですが、コードに直接「ブックの保存場所」「ブックの名前」を入力してしまうと、

開きたいブックが変更したいときいちいちVBAを開いてブックの情報を編集しなければならないのでかなり使いにくいマクロになってしまいます。

 

なので、セルに入力したブック情報から「ファイルを開く」というマクロを設定します。

 

B3セルに入力された「ブックの保存場所」「ブックの名前」の情報からブックを開くというサンプル事例をご紹介します。

 

サンプルコードがこちら

Dim exfileAs String
If Ash.Cells(3, 2) = "" Then
Exit Sub
Else
exfile = Ash.Cells(3, 2)
End If
Workbooks.Open Filename:=exfile

 

B3セルの情報を変更するだけで開くブックを変更することができます。

VBAを使ったことがない方でも感覚的に設定を変更できるので、いろんな方が扱うエクセルファイルへの導入がおすすめです。

 

開いたブックを指定する

「操作していたブック」と「開いたブック」を区別しなければ、「開いたブック」から「操作していたブック」へ情報を転記することができません。

 

Openメソッドでブックを開いた直後にブックの指定を行います。

 

サンプルコードがこちら

Workbooks.Open Filename:=exfile
Dim Bbook As Workbook
Set Bbook = ActiveWorkbook

 

サンプルコードでは、

「操作していたブック」‥Workbook

「Openメソッドで開いたブック」‥Bbook

といい区別をしています。

 

この「Bbook」は好きな変数に変更可能ですので、自分なりに使いやすい変数を決めて宣言してみてください。

 

別ブックの全シートの情報を取得する方法

「ブックを開く」「ブックを指定する」コードの作成の次は、開いたブックのシートを指定するコードを入力していきます。

 

この「開いたブックのシートを指定するコード」の内容によって、

  • 全シートの情報を取得するのか
  • 最初のシート情報を取得するのか
  • 特定のシート情報を取得するのか

が変わってきます。

 

まずは、全シートの情報を取得する方法からご紹介していきます。

 

サンプル事例として、

開いたブックのA1セルに入力されているテキストを設定シートの4列目に転記する

という処理を行ってみます。

 

 

サンプルコードがこちら

Sub テスト()
Dim Abook As Workbook
Set Abook = ActiveWorkbook
Dim Ash As Worksheet
Set Ash = Abook.Worksheets("設定")
Dim exfile, exname As String
If Ash.Cells(3, 2) = "" Then
Exit Sub
Else
exfile = Ash.Cells(3, 2)
End If
Workbooks.Open Filename:=exfile
Dim Bbook As Workbook
Set Bbook = ActiveWorkbook
For i = 1 To Bbook.Worksheets.Count
Ash.Cells(i, 4) = Bbook.Worksheets(i).Cells(1, 1)
Next i
Bbook.Close
End Sub

 

15行目から17行目のコードでは、

繰り返し処理（For〜Next）を使ってBbookの1つ目のシートから最後のシートまでを選択

しています。

16行目は、

「設定」シートに選択したシートの情報を転記する

という内容です。

 

16行目のコードを編集すすれば、開いたブックの全シートの情報を〇〇するというマクロを作成することができます。

 

このコードは、

開いたブックにどれだけのシートが設定されているかわからない、けれど全シートの情報を取得したい

というときに使われます。

 

別ブックの最初のシートの情報を取得する方法

 

サンプルコードがこちら

Sub テスト2()
Dim Abook As Workbook
Set Abook = ActiveWorkbook
Dim Ash As Worksheet
Set Ash = Abook.Worksheets("設定")
Dim exfile, exname As String
If Ash.Cells(3, 2) = "" Then
Exit Sub
Else
exfile = Ash.Cells(3, 2)
End If
Workbooks.Open Filename:=exfile
Dim Bbook As Workbook
Set Bbook = ActiveWorkbook
Ash.Cells(1, 4) = Bbook.Worksheets(1).Cells(1, 1)
Bbook.Close
End Sub

上記で紹介した「全シート情報を取得するコード」とほとんど変わっていません。

変わっているコードは15行目の「Bbook.Worksheets(1).Cells(1, 1)」です。

 

(1)とは、左から１つ目のシートということ

(1)を(2)とすれば左から２つ目、(3)とすれば左から３つ目のシートを選択できます。

 

このコードは、

開いたブックの何個目のシート情報を取得するのかが決まっている

というときに使われます。

 

別ブックの特定のシート情報を取得する方法

別ブックの特定のシート情報を取得するには、関数Ifを使って〇〇のとき〇〇シートを選択するといったコードにしていきます。

 

サンプル事例として、

開いたブックに「Aさん」というシート名がある時に情報を取得する

という処理をするコードをご紹介します。

 

サンプルコードがこちら

Sub テスト3()
Dim Abook As Workbook
Set Abook = ActiveWorkbook
Dim Ash As Worksheet
Set Ash = Abook.Worksheets("設定")
Dim exfile, exname As String
If Ash.Cells(3, 2) = "" Then
Exit Sub
Else
exfile = Ash.Cells(3, 2)
End If
Workbooks.Open Filename:=exfile
Dim Bbook As Workbook
Set Bbook = ActiveWorkbook
For i = 1 To Bbook.Worksheets.Count
If Bbook.Worksheets(i).Name = "Aさん" Then
Ash.Cells(i, 4) = Bbook.Worksheets(i).Cells(1, 1)
Else
End If
Next i
Bbook.Close
End Sub

15行目の繰り返し処理（For〜Next）で全シートを選択

 

16行目と17行目は、

開いたブックの中にシート名が「Aさん」があった場合、情報を転記する

といった内容になっています。

 

繰り返し処理（For〜Next）と関数IFを組み合わせて使用することで特定のシートだけを選択することが可能です。

 

別ブックのシート情報を取得するマクロでできることは？

別ブックを開いて情報を自由に取得することができるようになると、以下のようなエクセル業務の効率を格段にアップさせることが可能です。

 

  • 報告書の作成
  • データ分析
  • スケジュール管理

 

 

私が勤めていた会社では、

各々がエクセルファイルを作成してデータ管理するといったことが多くデータをめとめなさい！となったときにめちゃくちゃ苦労していました。

 

例えば、勤怠表をエクセルで管理していて

「Aさん.xlsx」「Bさん.xlsx」「Cさん.xlsx」「Dさん.xlsx」というブックが職員分保存されている

という環境でした。

 

管理者は月末になると、「ブックを一つ一つ開いて内容を確認、情報をまとめる」という作業を手作業で開始。

 

  • 残業は規定時間以内に収まっているか
  • ちゃんと残業を入力しているか
  • 休みを入力しているか

 

これらの作業を手作業で行っていたら時間がいくらあっても足りません。

そこで導入されたのが、別ブックから情報を取得しまとめるマクロです。

 

今まで丸２日かけて行っていた確認作業がものの1時間で完了するように。。

別ブックから情報を取得するマクロは、使い方によっていろんな業務に応用することができる最強のマクロだと思っています。

是非使い方をマスターして仕事の効率をアップさせてください。

 

マクロが導入できない！お困りの方はマメBlogへご相談

 

別ブックから情報を取得するマクロを導入したいけどコードが複雑で設定できない！

 

このようにマクロの設定でお悩みの方は是非マメBlogへご相談ください。

マクロを使って「こんな処理を自動化したい！」「このようなエクセル作業の効率を良くしたい！」というお悩みを解決します。

 

実績としては、月に10件程度のマクロ開発を受注。

1件1件余裕をもって作業できるボリュームで請け負わせていただいています。

本業で忙しい中、VBAの勉強をして業務内容に合ったマクロを開発する作業はとても難しいかと思います。

 

マメBlogのマクロ開発サービスを利用してエクセル業務の効率化を計ってみてはいかがでしょうか。

 

>>>マクロの開発依頼の詳細はこちら

エクセル

Posted by t.a.k.ubonse926@gmail.com