EXCEL VBA エクセルブックを開かないでデータを取得(ExecuteExcel4Macro)・別ブックからデータ取得・別ブックからのデータコピー
EXCEL VBA エクセルブックを開かないでデータを取得(ExecuteExcel4Macro)・別ブックからデータ取得・連続データ取得・別ブックからのデータコピー
今回説明するのは、エクセルブックを開かずに、EXCELデータを取得する方法を説明いたします。EXCELブックを開かずにデータを取得するには、ExecuteExcel4Macroメソッドを利用してエクセルブック内のデータを取得する事ができます。ExecuteExcel4Macroメソッドは、旧式のExcel 4.0マクロですが、現在のEXCELでも利用する事ができます。ExecuteExcel4Macroメソッドを利用する際は、いくつかの制限等あります。R1C1形式で取得するセルを指定したり、空白セルを取得した際は、０として値を返します。ただ、ブックを開く事無くデータを取得する事ができるので、重いEXCELファイルや大量のEXCELファイルからデータを取得する時に利用できるので、とても便利な機能です。それでは、サンプルプログラムを交えて順番に説明いたします。
●【EXCEL VBA 別のブックからデータをコピーする（ブック間のシートコピー）、下記の参照して下さい】
● ExecuteExcel4Macroメソッドを利用するには、下記の通りに設定を行います。
【使用例】
※下記のプログラムは、ブックを開かずに参照先ブック「Masterbook.xlsx」シート「Sheet1」セルA1からデータを取得するサンプルプログラムです。
|
1 2 3 4 5 6 7 8 9 |
' ' Sub ExecuteExcel4Macro00() '指定したブック・セル位置より、ブックを開かずにデータを取得します。 Range("A1") = ExecuteExcel4Macro("'C:\Users\akira\OneDrive\デスクトップ\[MasterBook.xlsx]sheet1'!R1C1") '別ブック（MasterBook.xlsx）より、ワークシート（Sheet01）のセル（A1）からデータを取得して取得元のブックのセル「A1」へ転記します。 End Sub ' |
【処理内容】
※上記プログラムの処理内容は、下図の通りになります。
①参照元の「ThisBook」から参照先のブック「Masterbook.xlsx」・シート「Sheet1」・セルA1よりデータを取得します。
②取得したデータを参照元の「ThisBook」のシート「Sheet1」・セルA1へデータが転記されます。
③参照先のブック「ThisBook」のシート「Sheet1」・セルA1に「ABCED」が表示「格納」されます。
【注意点】
※ExecuteExcel4Macroメソッドの利用については、2点ほど注意事項が有ります。
① 参照先のセルを指定する時に、R1C1形式でセル位置を指定します。
② 参照先のセルが空白の場合、数字の「０」を返します。
EXCEL VBA ブックを開かずに指定したブックからデータを所得する。（別ブックからコピー）
下記のサンプルプログラムは、ExecuteExcel4Macroメソッドを利用して、ブックを開かずに指定したブックからデータを取得するサンプルプログラムです。取得するデータについては、シート名「Sheet1」のセルA1を起点に縦10行×横７列分のデータを取得します。
【プログラム実行条件】
① 参照先のブックを作成：ブック名「MasterBook01.xlsx」ワークシート「Sheet1」にA1を起点に縦10×横7のデータを作成します。（上記参照）
② 参照先ブックのフルパス（ファイル位置）を実際に保存しているパスをプログラムに記入。
※注意：プログラム内に記述している参照先フルパスを書き換える↓この部分
(“‘C:\Users\akira\OneDrive\デスクトップ\[MasterBook01.xlsx]sheet1’）
③ 参照元のブックを作成：ブック名は、何でもよい。転記先のワークシート「Sheet１」を作成します。（空白でもよい）（上記参照）
※プログラムは、参照元「ThisBook」に記述します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
' ' Sub ExecuteExcel4Macro01() 'EXCEL VBA ブックを開かずに指定したブックからデータを所得する。 Dim L, I As Long For L = 1 To 10 '縦10行を繰り返します。 For I = 1 To 7 '横７列を繰り返します。 Cells(L, I) = ExecuteExcel4Macro("'C:\Users\akira\OneDrive\デスクトップ\[MasterBook01.xlsx]sheet1'!R" & L & "C" & I) '別ブックからセル単位でデータを順番に取得します。 Next I Next L End Sub ' |
（画面クリックして拡大）
EXCEL VBA ブックを開かずに指定したブックからデータを所得する。（別ブックからコピー）※データの取得範囲が分からない場合
下記のサンプルプログラムは、ExecuteExcel4Macroメソッドを利用して、ブックを開かずに指定したブックからデータを取得するサンプルプログラムです。今回は、前回のサンプル①の応用になります。前回のサンプル①では、取得するブックの取得範囲はセルA1を起点に縦10行×横７列分のデータを取得しますが、今回のサンプル②では、取得するブックの取得範囲が分からなくても、A１を起点に自動的に範囲を把握してデータを取得するサンプルプログラムです。
【プログラム実行条件】
① 参照先のブックを作成：ブック名「MasterBook.xlsx」ワークシート「Sheet1」にA1を起点にのマスターデータを作成します。（上記参照）
② 参照先ブックのフルパス（ファイル位置）を実際に保存しているパスをプログラムに記入。
※注意：プログラム内に記述している参照先フルパスを書き換える↓この部分
(“‘C:\Users\akira\OneDrive\デスクトップ\[MasterBook.xlsx]sheet1’）
③ 参照先「Masterbook」・ワークシート「Sheet１」のセル範囲を取得します。※（空白＝”０”でデータ範囲の最終行・最終列を判定します。）
④ 参照元のブックを作成：ブック名は、「ThisBook」参照元のワークシート名「Sheet１」を作成します。（中身は空白でもよい）（上記参照）
※下記プログラムは、参照元「ThisBook」に記述します。
【注意事項】
参照先「Masterbook」のセルのデータに「０」が登録されていると、空白を同じ認識されてしまうため、数字は、参照先「Masterbook」には、「０」は登録しないで下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
' ' Sub ExecuteExcel4Macro02() '指定したブック・セル位置より、ブックを開かずにデータを取得します。 Dim L, I As Long Dim Str As String L = 1 I = 1 Do Do Str = ExecuteExcel4Macro("'C:\Users\akira\OneDrive\デスクトップ\[MasterBook.xlsx]sheet1'!R" & L & "C" & I) '別ブックからセル単位でデータを順番に取得します。 If Str <> "0" Then Cells(L, I) = Str '習得したデータを同じワークシート・セル位置に転記します。 L = L + 1 '行セル＋１加算します。（縦） Else L = 1 '1行に戻します。 I = I + 1 '列セル＋１加算します。（横） End If Loop While Str <> "0" Str = ExecuteExcel4Macro("'C:\Users\akira\OneDrive\デスクトップ\[MasterBook.xlsx]sheet1'!R" & L & "C" & I) '別ブックからセル単位でデータを順番に取得します。※次の列の1行目にデータが有るか確認します。 Loop While Str <> "0" 'データが無ければ、別ブックからデータ取得を終了します。 MsgBox "別ブックからデータを取得しました。" End Sub ' |
（画面クリックして拡大）
EXCEL VBA ブックを開かずに指定したブックからデータを所得する。（別ブックからコピー）※複数シートからデータを取得する。
下記のサンプルプログラムは、ExecuteExcel4Macroメソッドを利用して、ブックを開かずに指定したブックからデータを取得するサンプルプログラムです。今回は、前回のサンプル②の応用になります。前回のサンプル②では、取得するブックの取得範囲が分からなくても、A１を起点に自動的に範囲を把握してデータを取得するサンプルプログラムですが、今回は、更に拡張性を持たせて、複数指定したシートのデータも転記先（Masterbook）のブックから取得します。
【プログラム実行条件】
① 参照先のブックを作成：ブック名「MasterBook.xlsx」ワークシート「マスターデータ」・「人事データ」にA1を起点にのマスターデータと人事データを作成します。（上記参照）
② 参照先ブックのフルパス（ファイル位置）を実際に保存しているパスをプログラムに記入。
※注意：プログラム内に記述している参照先フルパスを書き換える
③ 参照先「Masterbook」・ワークシート「マスターデータ」・「人事データ」のセル範囲を取得します。※（空白＝”０”でデータ範囲の最終行・最終列を判定します。）
④ 参照元のブックを作成：ブック名は、「ThisBook」参照元にのワークシート名「マスターデータ」と「人事データ」を作成します。（中身は空白でもよい）（上記参照）
※下記プログラムは、参照元「ThisBook」に記述します。
【注意事項】
参照先「Masterbook」のセルのデータに「０」が登録されていると、空白を同じ認識されてしまうため、数字は、参照先「Masterbook」には、「０」は登録しないで下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
' ' ' Sub ExecuteExcel4Macro03() '指定したブック・複数シートより、ブックを開かずにデータを取得します。 Dim L, I, W, lRow As Long Dim Str, WsName As String Dim Ws01, Ws02 As Worksheet Set Ws02 = Worksheets("シート名一覧") lRow = Ws02.Cells(Rows.Count, "A").End(xlUp).Row 'ワークシート「シート一覧」A列の最終行を取得します。 For W = 2 To lRow 'ワークシート「シート一覧」A列の最終行まで繰り返します。 WsName = Ws02.Cells(W, "A") 'ワークシート名を順番に取得します。 Set Ws01 = Worksheets(WsName) 'ワークシート名をセットします。Ws01へ L = 1: I = 1 'セル位置A1「Cells(1,1)」から取得できるように設定します。 Do Do Str = ExecuteExcel4Macro("'C:\Users\akira\OneDrive\デスクトップ\[MasterBook.xlsx]" & WsName & "'!R" & L & "C" & I) '別ブックからセル単位でデータを順番に取得します。 If Str <> "0" Then Ws01.Cells(L, I) = Str '取得したデータを同じワークシート・セル位置に転記します。 L = L + 1 '行セル＋１加算します。（縦） Else L = 1 '1行に戻します。 I = I + 1 '列セル＋１加算します。（横） End If Loop While Str <> "0" Str = ExecuteExcel4Macro("'C:\Users\akira\OneDrive\デスクトップ\[MasterBook.xlsx]" & WsName & "'!R" & L & "C" & I) '別ブックからセル単位でデータを順番に取得します。※次の列の1行目にデータが有るか確認します。 Loop While Str <> "0" 'データが無ければ、別ブックからデータ取得を終了します。 Next W MsgBox "別ブックの指定したワークシート全てからデータを取得しました。" End Sub ' |
（画面クリックして拡大）
EXCEL VBA ブックを開かずに選択したファイル（ブック）からデータを所得する。（別ブックからコピー）※毎回選択したフォルダーからファイルを取得
下記のサンプルプログラムは、ExecuteExcel4Macroメソッドを利用して、ブックを開かずにダイヤログボックスから選択したブックのデータを取得するサンプルプログラムです。今回のサンプルプログラムの場合は、取得するファイルを選択する事ができるので、取得するファイルの位置やフォルダーの階層が変わってもダイアログボックスから選択する事で該当データを取得する事ができます。
【プログラム実行条件】
① 参照先のブックを作成：ブック名「MasterBook.xlsx」ワークシート「顧客データM」A1を起点にマスターデータを作成します。（③：上記参照）
② 参照先「Masterbook」・ワークシート「顧客データM」のセル範囲を取得します。※（空白＝”０”でデータ範囲の最終行・最終列を判定します。）
③ 各ブックのワークシート名については、「ThisBook」は、「顧客データ」・「MasterBook」は、「顧客データＭ」に固定しています。（変更できません。※プログラムの変更が必要）
※参照先の「MasterBook」については、どのフォルダーに置いても大丈夫です。（ダイアログボックスで選択する事が範囲ならＯＫ）
※ファイル名については、参照元：「ThisBook」及び参照先：「MasterBooｋ」共にファイル名を変更しても大丈夫です。ワークシート名は変更不可
【注意事項】
参照先「Masterbook」のセルのデータに「０」が登録されていると、空白を同じ認識されてしまうため、数字は、参照先「Masterbook」には、「０」は登録しないで下さい。
★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● ExecuteExcel4Macro2（サンプルプログラム）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
Sub ExecuteExcel4Macro04() 'ブックを開かずに選択したファイル（ブック）からデータを所得する。（ダイアログボックス表示） Dim ws01 As Worksheet Dim L, I As Long Dim Str As String Dim FilePath, FileName As Variant Set ws01 = Worksheets("顧客データ") FilePath = Application.GetOpenFilename(FileFilter:="Excelファイル,*.xls*") 'ファイルを選択するダイアログボックスを表示します。 If FilePath = False Then 'ファイルが選択されているか判定します。 Exit Sub 'ファイルが選択されていない場合は、プログラムから抜けます。 End If ws01.Cells.ClearContents FileName = Dir(FilePath) '選択したファイルパスからファイル名を抜き出します。 FilePath = Replace(FilePath, FileName, "") 'ファイルパスからファイル名を削除します。 L = 1: I = 1 'セル位置A1「Cells(1,1)」から取得できるように設定します。 Do Str = ExecuteExcel4Macro("'" & FilePath & "[" & FileName & "]" & "顧客データM" & "'!R" & L & "C" & I) '選択した別ブックからセル単位でデータを順番に取得します。 If Str <> "0" Then ws01.Cells(L, I) = Str L = L + 1 '行セル＋１加算します（縦） Else I = I + 1 '列セル＋１加算します（列） L = 1 '行を１行目に戻します。 Str = ExecuteExcel4Macro("'" & FilePath & "[" & FileName & "]" & "顧客データM" & "'!R" & L & "C" & I) '選択した別ブックからセル単位でデータを順番に取得します。（次の列にデータが有るか確認します。） End If Loop While Str <> "0" '取得するデータが無けれ終了します。 MsgBox "選択したブックよりデータを取得しました。" End Sub ' |
（画面クリックして拡大）
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。