EXCEL VBA エクセルブックを開かないでデータを取得(ExecuteExcel4Macro)・別ブックからデータ取得・別ブックからのデータコピー

 

 

EXCEL VBA エクセルブックを開かないでデータを取得(ExecuteExcel4Macro)・別ブックからデータ取得・連続データ取得・別ブックからのデータコピー

 

 

 ●はじめに

今回説明するのは、エクセルブックを開かずに、EXCELデータを取得する方法を説明いたします。EXCELブックを開かずにデータを取得するには、ExecuteExcel4Macroメソッドを利用してエクセルブック内のデータを取得する事ができます。ExecuteExcel4Macroメソッドは、旧式のExcel 4.0マクロですが、現在のEXCELでも利用する事ができます。ExecuteExcel4Macroメソッドを利用する際は、いくつかの制限等あります。R1C1形式で取得するセルを指定したり、空白セルを取得した際は、0として値を返します。ただ、ブックを開く事無くデータを取得する事ができるので、重いEXCELファイルや大量のEXCELファイルからデータを取得する時に利用できるので、とても便利な機能です。それでは、サンプルプログラムを交えて順番に説明いたします。

 

●【EXCEL VBA 別のブックからデータをコピーする(ブック間のシートコピー)、下記の参照して下さい】

EXCEL VBA 別のブックからデータをコピーする(ブック間のシートコピー)(テクニック)

 

 

 

 ●書式の説明 EXCELブックを開かずにセルデータを取得・別ブックからのデータコピー(ExecuteExcel4Macroメソッド) 

● ExecuteExcel4Macroメソッドを利用するには、下記の通りに設定を行います。

 

【使用例】
※下記のプログラムは、ブックを開かずに参照先ブック「Masterbook.xlsx」シート「Sheet1」セルA1からデータを取得するサンプルプログラムです。

 

【処理内容】

※上記プログラムの処理内容は、下図の通りになります。
①参照元の「ThisBook」から参照先のブック「Masterbook.xlsx」・シート「Sheet1」・セルA1よりデータを取得します。
②取得したデータを参照元の「ThisBook」のシート「Sheet1」・セルA1へデータが転記されます。
③参照先のブック「ThisBook」のシート「Sheet1」・セルA1に「ABCED」が表示「格納」されます。

 

【注意点】
※ExecuteExcel4Macroメソッドの利用については、2点ほど注意事項が有ります。
① 参照先のセルを指定する時に、R1C1形式でセル位置を指定します。
② 参照先のセルが空白の場合、数字の「0」を返します。

 

 

 

 

EXCEL VBA ブックを開かずに指定したブックからデータを所得する。(別ブックからコピー)

 

 

 ●プログラム説明 (ExecuteExcel4Macroメソッド) (サンプル①)

下記のサンプルプログラムは、ExecuteExcel4Macroメソッドを利用して、ブックを開かずに指定したブックからデータを取得するサンプルプログラムです。取得するデータについては、シート名「Sheet1」のセルA1を起点に縦10行×横7列分のデータを取得します。

 

【プログラム実行条件】
① 参照先のブックを作成:ブック名「MasterBook01.xlsx」ワークシート「Sheet1」にA1を起点に縦10×横7のデータを作成します。(上記参照)
② 参照先ブックのフルパス(ファイル位置)を実際に保存しているパスをプログラムに記入。
※注意:プログラム内に記述している参照先フルパスを書き換える↓この部分 
(“‘C:\Users\akira\OneDrive\デスクトップ\[MasterBook01.xlsx]sheet1’)

③ 参照元のブックを作成:ブック名は、何でもよい。転記先のワークシート「Sheet1」を作成します。(空白でもよい)(上記参照)
※プログラムは、参照元「ThisBook」に記述します。

 

 

 

 

●実行前~実行後 ※プログラム実行後、参照先のブック「Masterbook01」・シート「Sheet1」・セルA1起点に縦10行×横7列に記載しているデータを取得して参照元に転記されました。
(画面クリックして拡大)

 

 

 

 

EXCEL VBA ブックを開かずに指定したブックからデータを所得する。(別ブックからコピー)※データの取得範囲が分からない場合

 

 

 ●プログラム説明  (ExecuteExcel4Macroメソッド) (サンプル②)

下記のサンプルプログラムは、ExecuteExcel4Macroメソッドを利用して、ブックを開かずに指定したブックからデータを取得するサンプルプログラムです。今回は、前回のサンプル①の応用になります。前回のサンプル①では、取得するブックの取得範囲はセルA1を起点に縦10行×横7列分のデータを取得しますが、今回のサンプル②では、取得するブックの取得範囲が分からなくても、A1を起点に自動的に範囲を把握してデータを取得するサンプルプログラムです。

【プログラム実行条件】
① 参照先のブックを作成:ブック名「MasterBook.xlsx」ワークシート「Sheet1」にA1を起点にのマスターデータを作成します。(上記参照)
② 参照先ブックのフルパス(ファイル位置)を実際に保存しているパスをプログラムに記入。
※注意:プログラム内に記述している参照先フルパスを書き換える↓この部分 
(“‘C:\Users\akira\OneDrive\デスクトップ\[MasterBook.xlsx]sheet1’)

③ 参照先「Masterbook」・ワークシート「Sheet1」のセル範囲を取得します。※(空白=”0”でデータ範囲の最終行・最終列を判定します。)
④ 参照元のブックを作成:ブック名は、「ThisBook」参照元のワークシート名「Sheet1」を作成します。(中身は空白でもよい)(上記参照)
※下記プログラムは、参照元「ThisBook」に記述します。

【注意事項】
参照先「Masterbook」のセルのデータに「0」が登録されていると、空白を同じ認識されてしまうため、数字は、参照先「Masterbook」には、「0」は登録しないで下さい。

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、参照先のブック「Masterbook」・シート「Sheet1」・セルA1起点にデータ範囲を把握して、参照元の「ThisBook」のシート「Sheet1」・セルA1起点に取得したデータを転記されました。今回のプログラムは、セルA1を起点に範囲を自動取得しますので、行列のデータが増えても範囲を取得して転記されます。
(画面クリックして拡大)

 

 

 

EXCEL VBA ブックを開かずに指定したブックからデータを所得する。(別ブックからコピー)※複数シートからデータを取得する。

 

 

 

 ●プログラム説明 (ExecuteExcel4Macroメソッド)(サンプル③)

下記のサンプルプログラムは、ExecuteExcel4Macroメソッドを利用して、ブックを開かずに指定したブックからデータを取得するサンプルプログラムです。今回は、前回のサンプル②の応用になります。前回のサンプル②では、取得するブックの取得範囲が分からなくても、A1を起点に自動的に範囲を把握してデータを取得するサンプルプログラムですが、今回は、更に拡張性を持たせて、複数指定したシートのデータも転記先(Masterbook)のブックから取得します。

 

 

 

【プログラム実行条件】
① 参照先のブックを作成:ブック名「MasterBook.xlsx」ワークシート「マスターデータ」・「人事データ」にA1を起点にのマスターデータと人事データを作成します。(上記参照)
② 参照先ブックのフルパス(ファイル位置)を実際に保存しているパスをプログラムに記入。
※注意:プログラム内に記述している参照先フルパスを書き換える
③ 参照先「Masterbook」・ワークシート「マスターデータ」・「人事データ」のセル範囲を取得します。※(空白=”0”でデータ範囲の最終行・最終列を判定します。)
④ 参照元のブックを作成:ブック名は、「ThisBook」参照元にのワークシート名「マスターデータ」と「人事データ」を作成します。(中身は空白でもよい)(上記参照)
※下記プログラムは、参照元「ThisBook」に記述します。

【注意事項】
参照先「Masterbook」のセルのデータに「0」が登録されていると、空白を同じ認識されてしまうため、数字は、参照先「Masterbook」には、「0」は登録しないで下さい。

 

 

 

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、参照元の「ThisBook」ブックのシート「シート名一覧」に記述しているシート名「マスターデータ」・「人事データ」のデータを別ブック「Masterbook」から一致するシート名「マスターデータ」・「人事データ」のデータを取得され、参照元のブックへデータ転記されました。
(画面クリックして拡大)

 

 

EXCEL VBA ブックを開かずに選択したファイル(ブック)からデータを所得する。(別ブックからコピー)※毎回選択したフォルダーからファイルを取得

 

 

 ●プログラム説明 (サンプル④)

下記のサンプルプログラムは、ExecuteExcel4Macroメソッドを利用して、ブックを開かずにダイヤログボックスから選択したブックのデータを取得するサンプルプログラムです。今回のサンプルプログラムの場合は、取得するファイルを選択する事ができるので、取得するファイルの位置やフォルダーの階層が変わってもダイアログボックスから選択する事で該当データを取得する事ができます。

 

【プログラム実行条件】
① 参照先のブックを作成:ブック名「MasterBook.xlsx」ワークシート「顧客データM」A1を起点にマスターデータを作成します。(③:上記参照)
② 参照先「Masterbook」・ワークシート「顧客データM」のセル範囲を取得します。※(空白=”0”でデータ範囲の最終行・最終列を判定します。)
③ 各ブックのワークシート名については、「ThisBook」は、「顧客データ」・「MasterBook」は、「顧客データM」に固定しています。(変更できません。※プログラムの変更が必要)

※参照先の「MasterBook」については、どのフォルダーに置いても大丈夫です。(ダイアログボックスで選択する事が範囲ならOK)
※ファイル名については、参照元:「ThisBook」及び参照先:「MasterBook」共にファイル名を変更しても大丈夫です。ワークシート名は変更不可

【注意事項】
参照先「Masterbook」のセルのデータに「0」が登録されていると、空白を同じ認識されてしまうため、数字は、参照先「Masterbook」には、「0」は登録しないで下さい。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● ExecuteExcel4Macro2(サンプルプログラム) 

 

 

 ●実行前~実行後 ※プログラム実行後、ダイアログボックス表示が表示されて、参照先のファイルを選択します。選択されたファイルのデータが参照元に転記されました。ダイアログボックスで参照先を選択することで、参照先が変更されてもデータを転記する事が出来ます。
(画面クリックして拡大)

 

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。