VBA【最速】開かずに別ブックから【値を取得】セルの転記【自由度MAX】
VBA【最速】開かずに別ブックから【値を取得】セルの転記【自由度MAX】

VBA【最速】開かずに別ブックから【値を取得】セルの転記【自由度MAX】

投稿者 JoVBA投稿日:2022年12月29日2024年5月8日

最速かつ自由に柔軟にVBAで他のエクセルブックからセルの値を取得し指定範囲のデータを抽出する方法を紹介します。

結論を申しますと別ブックから転記など、値を読み出す処理をする場合は今回、紹介する方法が一番、処理速度が速く、汎用性があるので色々な効率が良いです。

別ブックのセルの値を取得するのは、覚えてしまえば簡単です。

別のブックを裏で開くことにより、開かずに取得しているような感じで別ブックの値を取得できます。

表示処理をしていないので、処理速度もかなり向上しています。ですので、別のブックからの値を複数取得したい時にも、高速で使用可能です。

また、別のブックから値を参照し色んな値をループして取得したい時にも問題なく使えます。

なので、別ブックの値を開かずに値を取得したい方には、オススメです。

値を取得するだけではなく、開かずに値を書き込む事も可能です。

もちろん単純な「Workbooks.Open」という初級レベルではありません。

単純な「Workbooks.Open」だと、処理速度が遅いですよね。

VBAの処理は少しでも速い方が良いのでVBAが遅くなる原因になりかねない単純な「Workbooks.Open」は、使用を控えましょう。

あと有名なのは「ExecuteExcel4Macro」もあります。

別のブックを開かずに値を取得できます。ですが、他のブックからセルの値を取得するのに制約が多く、柔軟性に欠けます。

エクセルで色々作っていると他のファイルからデータを抽出したいですよね。

「ExecuteExcel4Macro」は、昔のマクロのコードなので使用するのは避けましょう。

Excelの関数で別ブックの値を取得しようとすると少しファイル名が変わると、うまくいかないし、「INDIRECT」関数だと限界がありますよね。

では、VBAで別のブックのセルから値を取得する、最速な方法を説明します。

別のブックからデータを抽出する方法がいくつかありますが今回、紹介する方法を覚えておくと、なにかと便利ですよ。

注意事項もあるので、必ず目を通してください。

VBA高速化に興味ある方は、こちらをご覧ください。

【VBA高速化】爆速処理にする簡単な方法【マクロが遅いと感じるあなたへ】

VBA【連想配列】の使い方を解説【”爆速”配列内検索】を簡単に習得

< ---目次--- >

  • ◆最速で他のブックを開く方法
    • サンプルコード
    • 解説
      • ・「Dim ExcelApp As New Application」
      • ・変数:「ReadFolderFullPath」
      • ・「ExcelApp.Visible = False」
      • ・「Set Wb」
      • ・他のブックの値を取得
      • ・「ExcelApp.Quit」
      • ・「Set ExcelApp = Nothing」
  • ◆注意事項
    • ・開いたら閉じる
    • ・不可視にしている事を理解する
  • ◆応用編
    • ・別のブックから転記する方法
      • サンプルコード
      • 解説
    • ・別ブックの最終行を取得する方法
      • サンプルコード
      • 解説
  • ◆よくあるVBAで他のブックのセルの値を取得する方法
    • ・単純な「Workbooks.Open」
    • ・「ExecuteExcel4Macro」
  • ◆この記事をご覧になった方へのおすすめリンク
    • マクロを高速化したいあなたへ~VBA高速化の常識~
    • マクロでできることをおさらい
  • ◆まとめ

◆最速で他のブックを開く方法

サンプルコード Sub 最速で別ブックを開く() Dim ExcelApp As New Application Dim Wb As Workbook Dim ReadFolderFullPath As String '開くExcelファイルを指定 ReadFolderFullPath = ThisWorkbook.Path & "\" & "Book1.xlsx" 'エクセルを不可視で開く ExcelApp.Visible = False 'エクセル可視/不可視設定 ExcelApp.DisplayAlerts = False '警告メッセージをオフ Set Wb = ExcelApp.Workbooks.Open(ReadFolderFullPath, , True) '読取り専用で開く '処理例 '別ブックのセルの値を取得 Debug.Print Wb.Worksheets("Sheet1").[A1].Value ExcelApp.DisplayAlerts = True '警告メッセージをオン ExcelApp.Quit 'Excel終了 Set ExcelApp = Nothing '参照を解放 End Sub 解説

あれほど、「Workbooks.Open」をバカにしましたが、使っています(笑)

ですが、単純ではありません。

マクロを実行しているエクセルとは、違う新しいアプリケーションでエクセル（変数:ExcelApp）を開くいています。

そうすると、不可視設定ができますので、表示する処理時間を省く事によりVBA高速化に繋がります。

処理的にはブックを開いていますが見た目上は非表示なので、別ブックのファイルを開かずにセルの値を取得しているような感じになります。

VBAでファイルを開かずに値を取得する方法として一番オススメです。

無駄な表示処理をなくす事により、速い処理で他のブックの値を読みだす事ができます。

柔軟で高速に別ブックのセルの値を取得するのは、この方法です。

VBAコードの中身を順に説明します。

理解した後は、下部の注意事項を必ず目を通してください。

・「Dim ExcelApp As New Application」

別のアプリケーションのエクセル用のオブジェクト変数です。

この変数を使用するには、「ツール」→「参照設定」から「Microsoft Scripting Runtime」を探し出し、チェックを入れてください。

※↑何か勘違いしていたみたいで、チェックはなくても問題なく動きそうでした。

・変数:「ReadFolderFullPath」

「ReadFolderFullPath」という変数には、開くエクセルのパスとファイル名（拡張子まで）を代入してください。

・「ExcelApp.Visible = False」

「ExcelApp.Visible = False」でブックを開く前に不可視の設定にしています。

「True」にすれば、可視できるようになるのでデバッグ時は、「True」の方が便利かもしれませんね。

・「Set Wb」

「Set Wb = ExcelApp.Workbooks.Open(ReadFolderFullPath, , True) 」の文についてです。

他のブックを開くと同時に、変数「Wb」に開いた他のエクセルブックがオブジェクトとして格納されます。

「True」の所を「False」にすると、読取り専用ではなく、通常に開きます。

データの書き換えをする場合は、「False」にしてくださいね。

別ブックは不可視なので色々、気を付けてください。

・他のブックの値を取得 Debug.Print Wb.Worksheets("Sheet1").[A1].Value

上記のサンプルコードで別ブックのセルの値を取得する事ができます。

セルの値を取得する方法は、別のエクセルブックオブジェクトを指定して、後は、通常通りですね。

VBAって「[A1]」でセルの指定できるの？って疑問に思いますよね。

気になりますよね。

こちらの記事で紹介していますのでご覧ください。

【VBA】Cells? Range? [A1](Evaluate)?どっちが正しい？【色々なセル指定方法】

・「ExcelApp.Quit」

「ExcelApp」を終了します。

必ず、「ExcelApp.Quit」は実行してください。

要は、エクセルを閉じる事と同じで、右上の「×」ボタンと同じです。

デバッグ時に「ExcelApp.Quit」を実行せずに、途中終了すると「ExcelApp」が不可視で開かれた状態で残ります。

要は、裏で開きっぱなしです。

タスクマネージャーから終了する、又は、PCを再起動しないと閉じることはできません。

「ExcelApp」の変数を理解していない方に言いますが「ExcelApp.Quit」では、VBAを実行しているブックは閉じませんので安心してください。

不安なら「ThisWorkbook.Save」でも入れといてください。

当然、保存する処理時間は遅くなりますので、非推奨ですが。

・「Set ExcelApp = Nothing」

いわゆる、おまじないですね。

「ExcelApp」によって占有しているPCメモリ（わずか）を解放します。

「ExcelApp」はPublic変数ではないので、プロシージャ（1つのマクロ）が終了すれば解放される変数なので個人的にはいらないと思いつつも、おまじないとして、入れています。

◆注意事項

・開いたら閉じる

物を出したら片づけますよね。

アプリも開いて、使い終わったら閉じますよね。

それと同じで、見えないように別のブックを開いたからと言って、閉じないのはやめましょう。

デバッグ時やエラー時にVBAを強制途中終了する前に、必ず、「ExcelApp.Quit」を実行してください。

・不可視にしている事を理解する

「ExcelApp.Visible = False」によって、不可視にしているので

ファイル名を間違えたり不可視のエクセルファイルを書き込みしようとして、セルアドレスを間違えたりしないようにしてください。

ただでさえ、別のブックをバックグラウンドで開いているので、気づかない可能性もありますのでお気を付けください。

最初のうちは、バックアップを取ることをオススメします。

◆応用編

・別のブックから転記する方法

別のブックから一定の範囲を高速に転記する方法を紹介します。

特にこれと言って難しくないんですが、サンプルコードを記載します。

サンプルコード Sub 最速で別ブックから転記() '「Microsoft Scripting Runtime」にチェック（「ツール」→「参照設定」(Alt→T→R)） Dim ExcelApp As New Application Dim Wb As Workbook Dim ReadFolderFullPath As String '開くExcelファイルを指定 ReadFolderFullPath = ThisWorkbook.Path & "\" & "Book1.xlsm" 'エクセルを不可視で開く ExcelApp.Visible = False 'エクセル可視/不可視設定 ExcelApp.DisplayAlerts = False '警告メッセージをオフ Set Wb = ExcelApp.Workbooks.Open(ReadFolderFullPath, , True) '読取り専用で開く '------------------------------------------ '↑ここまでは上記サンプルコードと同一↑ '転記処理 With Wb.Worksheets("Sheet1") Worksheets("Sheet1").[A1:B10].Value = .[A1:B10].Value End With '↓ここから下は上記サンプルコードと同一↓ '------------------------------------------ ExcelApp.DisplayAlerts = True '警告メッセージをオン ExcelApp.Quit 'Excel終了 Set ExcelApp = Nothing '参照を解放 End Sub 解説

VBAで別のブックのセルから値を取得する高速な方法は、配列を使う事です。

「Worksheets(“Sheet1”).[A1:B10].Value = .[A1:B10].Value」のセル範囲を変えれば色んな範囲を転記する事が出来ます。

リンク：[A1:B10]でセルを楽に指定する方法

セルの範囲指定をしていますが、セルも配列の一種です。

配列変数を使用すれば、もっと様々な範囲を高速に転記することができると思います。

配列変数について詳しく記載した記事はこちらになります。

VBA【爆速】【配列】８割無意識に使っている！？エクセルVBAでは配列を意識して使おう【マクロ高速化】 ・別ブックの最終行を取得する方法

別のブックのA列最終行を取得する方法を紹介します。

こちらも難しくないのですが、サンプルコードを記載します。

サンプルコード Sub 別ブックの最終行を取得() '「Microsoft Scripting Runtime」にチェック（「ツール」→「参照設定」(Alt→T→R)） Dim ExcelApp As New Application Dim Wb As Workbook Dim ReadFolderFullPath As String '開くExcelファイルを指定 ReadFolderFullPath = ThisWorkbook.Path & "\" & "Book1.xlsm" 'エクセルを不可視で開く ExcelApp.Visible = False 'エクセル可視/不可視設定 ExcelApp.DisplayAlerts = False '警告メッセージをオフ Set Wb = ExcelApp.Workbooks.Open(ReadFolderFullPath, , True) '読取り専用で開く '------------------------------------------ '↑ここまでは上記サンプルコードと同一↑ '◆最終行取得処理◆ Dim StartRow As Long, LastRow_1 As Long _ , LastRow_2 As Long With Wb.Worksheets("Sheet1") '###---処理Ａ---### '●確実に最終行を取得 For StartRow = 1 To 100000 If .Cells(StartRow, 1) = "" Then LastRow_1 = StartRow - 1 Exit For End If Next StartRow '###------------### '###---処理Ｂ---### '●簡易的な最終空白行を取得 '関数入力セル等があると正常に最終行取得できない LastRow_2 = Range("A1").End(xlDown).Row '入力がある最終行 End With '###------------### 'LastRow_1 :確実に最終行を取得する方法で、こちらが推奨です 'LastRow_2 :簡易的な最終空白行を取得する方法で ' :関数入力セル等があると正常に最終行取得できません '↓ここから下は上記サンプルコードと同一↓ '------------------------------------------ ExcelApp.DisplayAlerts = True '警告メッセージをオン ExcelApp.Quit 'Excel終了 Set ExcelApp = Nothing '参照を解放 End Sub

解説

サンプルコードの処理Ａか処理Ｂを削除して、ご使用ください。

処理Ａを残して、処理Ｂを削除するのがオススメです。

こちらを参考にしていますので、下記の記事をご覧ください。

VBA【最終行の取得】を”確実”にする方法【マクロ基本編】

◆よくあるVBAで他のブックのセルの値を取得する方法

・単純な「Workbooks.Open」

これはシンプルに遅いです。

別のブックを開くのが1個なら、気にならないかもしれませんがどうせなら、今回紹介した、VBAで「最速で他のブックを開く方法」を覚えてください。

・「ExecuteExcel4Macro」

セル１個など限定的な条件では今回紹介した方法より、速いかもしれません。

ですが、複数はできないなど、使える条件が厳しすぎます。

シート名が固定されていないとダメだったり、取得するセル数が決まっている等色々ななんじゃこりゃというような制約がありますので、オススメはできません。

少し違う事にしようとした時に、嫌な気持ちになると思います。

実際、私がそうでした（笑）。

「ExecuteExcel4Macro」でコード書くと後々、おすすめの別のブックから値を取得するサンプルコードにしておけばよかったと後悔すると思います。

◆この記事をご覧になった方へのおすすめリンク

マクロを高速化したいあなたへ~VBA高速化の常識~ 【VBA高速化】爆速処理にする簡単な方法【マクロが遅いと感じるあなたへ】 マクロでできることをおさらい Excelマクロとは？マクロできること事例11選【ほぼ全て自動化可能】

◆まとめ

【室内、浴室乾燥で話題】コードレスサーキュレータ

【家中楽々持ち運び】コードレス加湿器

【高機能 & おしゃれゴミ箱】#自動開閉式#ニオイが漏れにくい

他のブックのセルの値を取得するのに、単純な「Workbooks.Open」は通常の使い方をしないで新しいエクセルオブジェクトから他のブックを開いて、セルの値を取得しましょう。

そうすると、以前とは比べ物にならないくらい、別ブックのセルの値取得が速くなります。

📎📎📎📎📎📎📎📎📎📎
BOT