【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時間で完了するように。。

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

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

 

VBA初心者が効率化マクロを導入する方法

 

VBAなんて扱ったことないけどエクセル作業を自動化するマクロを導入したい！

このような問題を抱えながら日々のエクセル業務をこなしている方がおおくいらっしゃるのではないでしょうか。

 

こんな問題を解決する方法がこちら

  • マクロの開発を外注に依頼する
  • マクロが設定されたエクセルファイルをダウンロードする
  • 参考書やネットでVBAスキルを身に付ける

 

VBAの勉強を始める前は上記の方法で効率化マクロを導入していました。

 

 

 

完成されたマクロを使ってエクセル業務を処理する

↓

作業効率が２倍以上アップ！！

↓

便利すぎて自分でもマクロを設定したいと考え始める

↓

参考書やネットの情報でVBAスキルを身に付ける

↓

マクロ開発を代行できる程のスキルを習得

 

 

 

VBAは

他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい

という特徴があります。

 

毎日のルーティーン作業にうんざりしている、だれでもできる単純作業に時間を費やしている、という方はあなたに合った方法でVBAを導入しちゃいましょう。

 

マクロの開発を外注に依頼する

マクロの開発を外注に依頼する導入方法は

VBAを全く扱えない方でもエクセル作業を自動化できる最も簡単な方法

です。

 

「こんなエクセル作業を自動化したい」「作った資料を自動的にPDFで保存したい」「決まった様式に写真を自動で貼り付けたい」

こんな感じで実現したい内容を依頼するだけで業務内容に合ったマクロを開発してくれます。

 

Left Caption

ガッツポーズの人

完成まで本業に集中できるので効率的

 

 

デメリットは、費用がかかることだけ。

開発内容、依頼先によってかかる費用は大きく異なります。

私が会社員の頃利用していた業者さんは１マクロあたり2万円～3万円の費用がかかりました。

 

Left Caption

疑問がある人

ちょっと高くない。。。

 

ちなみに、マメBlogでもエクセルマクロ開発代行サービスを承っています。

開発内容の確認、VBAコードの設定、動作確認後の調整、など全ての工程を私（マメ父ちゃん）が行っているので、

費用が安い！スピード納期！！

でやらせてもらっています。

 

ほとんど独学で身に付けたVBAスキルなので、内容によっては開発できないこともあるのでご了承ください。

 

ご相談、見積もり依頼は完全無料ですので気になる方は下記のリンクよりお問合せ下さい。

 

マメBlogのエクセルマクロ開発代行サービスはこちら

 

マクロが設定されたエクセルファイルをダウンロードする

VBA初心者でも『マクロが設定されたエクセルファイルをダウンロード』ですぐにエクセル作業を自動化することができます。

 

たとえば、

A４用紙に写真を貼り付けてコメントを入力する作業を自動化したい！という場合、

『写真を貼り付けるマクロ』が設定されたエクセルファイルをダウンロードすれば問題解決です。

 

実際に、『写真を貼り付けるマクロ』をダウンロードして使っている様子がこちら

写真貼付けマクロの使い方を示したイラスト

 

 

Left Caption

ガッツポーズの人

写真貼付け作業の効率がめちゃくちゃアップ

 

 

『マクロが設定されたエクセルファイルをダウンロード』する導入方法は、

VBA初心者でもマクロを導入できる、費用が安い、

というメリットがあります。

 

しかし

VBAに関する情報が多く公開されているとはいえ、ネット上で想定するマクロは見つけることが難しい

というデメリットもあります。

 

Left Caption

ガッツポーズの人

お目当てのマクロが見つかればラッキー

 

個人ブログやファイルを販売できるサイト「note」や「Tips」ではいろんなマクロの情報が掲載されているので是非参考にしてみてください。

 

今までマメBlogで受注した効率化マクロを下記のnoteで掲載していますので併せてチェックしてみてください。

 

【note】エクセルマクロのダウンロードはこちら

 

 

参考書やネットでVBAスキルを身に付ける

先程も紹介しましたが、

VBAは他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい

プログラミングです。

 

言い換えると、

情報量が豊富なので自分で勉強できちゃう

ということです。

 

私のVBAスキルはほとんど独学で身に付けたもので、

考えた処理のほとんどをVBAで表現できるレベルにまで到達した！

と思っています。

 

私の作ったマクロをプロのプログラマーが見れば、

お前の書くコードはインチキだ！「変数の宣言」とか「引数」を正しく使えよ！！

と感じるはずです。

 

でも、

私からすればどうでもいい！！！想定した内容を正しく処理できればよくないですか！？

 

独学であっても『写真を貼り付けるマクロ』『全てのシートをPDF出力するマクロ』『連番を振るマクロ』など、

業務内容に合ったマクロを開発できるようになれます。

 

独学でVBAスキルを身に付けるコツは、

エラーが発生してもいいからひたすらコードを書く、なんでもいいからエクセル作業をマクロ化してみる

です。

 

とは言っても、全くの知識0の方は何をすればいいかわからないですよね。

 

VBA知識0の初心者の方は、参考書『たった1秒で仕事が片づくExcel自動化の教科書【増強完全版】』を読んでみてください。

 

VBAの知識0の方でもすいすい読み進められる内容になっています。

やさしい内容にも関わらず「え！！VBAを使えばこんなことできるの！？」と感動すると思います。

 

実際に私はこの参考書を読んでからVBAの勉強を始めました。

 

Left Caption

ひらめく人

VBA学習スタートのきっかけをくれた参考書


 

 

VBAの魅力を発見した後は、

ひたすら自動化したい処理を実現するコードを書くだけ

です。

 

【VBA 写真を貼る】、【VBA PDF出力】、のようにネット検索すれば知りたいコードをすぐにゲットできます。

 

実現したい処理に向かって、「調べる→書いてみる→エラーを改善する→調べる」を繰り返していると

自然といろんなマクロが作れるようになっているはずです。

 

VBAに興味が湧いてきたという方は今からVBA学習をスタートさせちゃいましょう。

エクセル, エクセルVBA(応用編)

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