ブックを開かずにセル値を取得（ExecuteExcel4Macro,Excel.Application）
「ブックを開かずにセル値を取得」に関する検索が非常に多いようです。
おそらく、ExecuteExcel4Macroに関連しているとか、
もしくは、大量のファイルがある為、マクロVBAの処理時間がかかってしまうと言うものでしょう。
ExecuteExcel4Macroについての簡単な解説は、「ExecuteExcel4Macroについて」
・ ・ ・ExecuteExcel4Macroの使用例 ・ExecuteExcel4Macroの最後に
サンプルを通して、方法論を解説します。
以下では、マクロ記述してExcelファイルと同一フォルダ内の"test"サブフォルダの全Excelファイルについて処理します。
"test"フォルダには、100KB強のExcelファイルを100個入れて、以下のテストを実行しています。
ExecuteExcel4Macroで１個のセル値を取得
Sub sample1()
Dim i As Long
Dim strDir As String
Dim strFile As String
Debug.Print Timer
Application.ScreenUpdating = False
strDir = ThisWorkbook.Path & "\test\"
strFile = Dir(strDir)
i = 1
Do While strFile <> ""
Cells(i, 1) = ExecuteExcel4Macro("'" & strDir & "["
& strFile & "]Sheet1'!R1C1")
strFile = Dir()
i = i + 1
Loop
Application.ScreenUpdating = True
Debug.Print Timer
End Sub
"Sheet1"のA1セルのみ取得しています。
この処理は非常に速いです。
私の環境では、0.3秒程度で完了します。
Excel.Applicationで１個のセル値を取得
Sub sample2()
Dim i As Long
Dim strDir As String
Dim strFile As String
Dim xls As New Excel.Application
Dim wb As Workbook
Debug.Print Timer
Application.ScreenUpdating = False
strDir = ThisWorkbook.Path & "\test\"
strFile = Dir(strDir)
i = 1
Do While strFile <> ""
Set wb = xls.Workbooks.Open(strDir & strFile)
Cells(i, 1) = wb.Worksheets("Sheet1").Range("A1")
wb.Close Savechanges:=False
strFile = Dir()
i = i + 1
Loop
Set xls = Nothing
Application.ScreenUpdating = True
Debug.Print Timer
End Sub
"Sheet1"のA1セルのみ取得しています。
私の環境では、11秒程度で完了します。
New Excel.ApplicationとExcelインスタンスを生成せずに、
普通に、Workbooks.Open(strDir & strFile)とやるよりは、はるかに高速に動作します。
これですと、30～40秒くらいの時間はかかってしまうはずです。
ExecuteExcel4Macroで100個のセル値を取得
Sub sample1_2()
Dim i As Long
Dim j As Long
Dim strDir As String
Dim strFile As String
Debug.Print Timer
Application.ScreenUpdating = False
strDir = ThisWorkbook.Path & "\test\"
strFile = Dir(strDir)
i = 1
Do While strFile <> ""
For j = 1 To 100
Cells(i, j) = ExecuteExcel4Macro("'" & strDir & "["
& strFile & "]Sheet1'!R1C" & j)
Next
strFile = Dir()
i = i + 1
Loop
Application.ScreenUpdating = True
Debug.Print Timer
End Sub
"Sheet1"のA1～CV1セルの取得をしています。
私の環境では、約25秒程度で完了します。
sample1の１個のセル取得に比べて、約100倍の時間がかかっています。
これは、単純に、ExecuteExcel4Macroの実行回数に比例していると言う事になります。
Excel.Applicationで100個のセル値を取得
Sub sample2_2()
Dim i As Long
Dim j As Long
Dim strDir As String
Dim strFile As String
Dim xls As New Excel.Application
Dim wb As Workbook
Debug.Print Timer
Application.ScreenUpdating = False
strDir = ThisWorkbook.Path & "\test\"
strFile = Dir(strDir)
i = 1
Do While strFile <> ""
Set wb = xls.Workbooks.Open(strDir & strFile)
For j = 1 To 100
Cells(i, 1) = wb.Worksheets("Sheet1").Cells(1, j)
Next
wb.Close Savechanges:=False
strFile = Dir()
i = i + 1
Loop
Set xls = Nothing
Application.ScreenUpdating = True
Debug.Print Timer
End Sub
"Sheet1"のA1～CV1セルの取得をしています。
私の環境では、約38秒程度で完了します。
sample2の１個のセル取得に比べて、約3倍以上の時間がかかっています。
しかし、データ個数の増加に比べると短い時間で済んでいます。
これは、セル値の取得より、ブックのOpenに時間がかかっている為です。
しかし、ちょっと考えてみて下さい。
連続したセル値を取得するなら、もっと効率的な記述ができます。
Excel.Applicationで100個のセル値を取得2
Sub sample2_3()
Dim i As Long
Dim strDir As String
Dim strFile As String
Dim xls As New Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Debug.Print Timer
Application.ScreenUpdating = False
strDir = ThisWorkbook.Path & "\test\"
strFile = Dir(strDir)
i = 1
Do While strFile <> ""
Set wb = xls.Workbooks.Open(strDir & strFile)
Set ws = wb.Worksheets("Sheet1")
Range(Cells(i, 1), Cells(i, 100)).Value = ws.Range(ws.Cells(1, 1), ws.Cells(1,
100)).Value
wb.Close Savechanges:=False
strFile = Dir()
i = i + 1
Loop
Set xls = Nothing
Application.ScreenUpdating = True
Debug.Print Timer
End Sub
私の環境では、約12秒程度で完了します。
sample2の１個のセル取得と、ほとんど変わっていません。
つまり、
Cells(i, 1) = wb.Worksheets("Sheet1").Range("A1")
と
Range(Cells(i, 1), Cells(i, 100)).Value = ws.Range(ws.Cells(1, 1), ws.Cells(1, 100)).Value
これは、ほとんど処理時間に差が無いと言うことになります。
ブックを開かずにセル値を取得
おもに、処理時間について考察しましたが、
ExecuteExcel4Macroには、重大な欠点があります。
それは、シート名が固定でなければ取得できないことです。
ExecuteExcel4Macroには、重大な欠点があります。
それは、シート名が固定でなければ取得できないことです。
別の方法で、ブックを開かずにシート名を取得する方法はあります。
Excelファイルを開かずにシート名を取得
Excelファイルを開かずにシート名を取得
Excelファイルを開くときは、ファイルサイズに比して、とても遅い場合があります、ファイルを開かずに、Excelの情報を取得したいという要望は少なからずあるようです、そこで、処理の基本となるシート名を取得する方法になります。もちろん、開かないと言っても、厳密には必ず開いているわけですが、Excelのブックとして開か…
ExecuteExcel4Macro("'" & strDir & "[" & strFile & "]Sheet1'!R1C1")
この、Sheet1は固定で記述しなければなりません。
つまり、シート名が不明な場合はExecuteExcel4Macroは使えないのです。
Excel.Applicationを利用した方法なら、
先頭シート、Worksheets(1)のように記述も出来ますし、もちろん全シートに対しての処理も問題ありません。
結論として、ExecuteExcel4Macroが有用なのは、
・ファイル数が極めて多い
・シート名が固定されている
・取得するセル数が限定的である事
ということになります。
なお、「ExecuteExcel4Macroについて」にもある通り、ExecuteExcel4Macroでは関数も使えます。
・ ・ ・ExecuteExcel4Macroの使用例 ・ExecuteExcel4Macroの最後に
ただし、単一の値を取得する関数に限られます。同じテーマ「マクロVBAサンプル集」の記事
マクロVBAの中で、他のブックを開く事はよくあります、ブックを開く時、ブックが存在しているか、既に開いてないか これらのチェックをせずに開いてしまうと、マクロがエラー停止してしまいます。ここでは、これらのチェック方法についての具体的なVBAコードを紹介します。
ブックを閉じる（Close）ブックを閉じる時の処理方法です。まずは、保存して閉じる場合。これは、次のように書くこともできます。Subsample2()ThisWorkbook.CloseSaveChanges:=TrueEnd Sub では、保存しないで閉じる場合。
他ブックを開いて閉じる（Open,Close）・パターン1：ActiveWorkbook ・パターン2：Workbooks.Count ・パターン3：オブジェクト変数 ・パターン4：Withステートメント ・パターン5：オブジェクト変数+Withステートメント ・開いているブックをブック名で検索し閉じる場合
ブックを開かずにセル値を取得（ExecuteExcel4Macro,Excel.Application）罫線を簡単に引く（Borders,BorderAround）
エクセルでは、罫線を引く事が多いです。この罫線は、以外に処理時間もかかりますし、やり方によっては、VBAの行数も多くなります。とにかく、簡単に罫線を引く方法です。以下の表を作成する場合です。罫線を引く順番等、いろいろありますが、とにかく短いコードにしてみます。
マクロVBAの開始時と終了時に指定しておくべきApplicationのプロパティ・マクロVBA開始時 ・マクロVBA終了時 ・Applicationのプロパティ解説 ・マクロが途中で終了した場合
オートフィルター（AutoFilter）エクセルでは、定番機能のフィルターです。「Sheet1」のA列でフィルターし、「Sheet2」へコピーします。ごく基本的なフィルターです。フィルターのセル範囲指定は、いろいろな指定が可能です。「連続セル範囲の選択 」も参考にして下さい。
日付のオートフィルタ（AutoFilter）とても便利なオートフィルターですが、日付となると、結構大変です。以下の表で説明します。普通は、こんなように指定します。Operator:=xlFilterValues は2007以降で追加された機能です。
印刷ダイアログを使用する（xlDialogPrint）入力しやすいように、セルに色をつけている事が多いと思いますが、印刷時には、ちょっとじゃまな場合もあります。インク（トナー）も無駄ですしね。印刷時に色指定を解除して印刷する方法になります。Application.Dialogs(xlDialogPrint).Show 印刷タイアログを表示します。
名前定義の一覧と削除（Name）・名前定義の一覧を取得し、シートに書き出すマクロVBA ・非表示の名前定義を表示 ・サイト内の関連ページ
シートを名前順に並べ替えるシートを名前順に並べ替える方法になります。配列を使っていますが、配列が難しい場合は、シートに書き出して処理すれば良いでしょう、並べ替えもシート上なら簡単です。上記では、シート名を文字列として処理しています。
新着記事NEW ・・・新着記事一覧を見る
イータ縮小ラムダ（eta reduced lambda）｜エクセル入門(2023-11-20)
PIVOTBY関数（縦軸と横軸でグループ化して集計）｜エクセル入門(2023-11-19)
GROUPBY関数（縦軸でグループ化して集計）｜エクセル入門(2023-11-18)
PY関数（Pythonコードをセル内で実行）｜エクセル入門(2023-11-17)
画像「セルに配置」のVBAについて（365の新機能）｜VBA技術解説(2023-11-13)
スピルのゴーストの範囲を選択するVBA｜ツイッター出題回答 (2023-10-31)
エクセル試験3：月間の所定労働時間｜エクセル練習問題(2023-10-04)
エクセル試験2：所得税の計算｜エクセル練習問題(2023-10-04)
エクセル試験1：曜日別の平均客単価｜エクセル練習問題(2023-10-04)
列全体を指定する時のRangeとColumnsの違い｜ツイッター出題回答 (2023-09-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.繰り返し処理（For Next)｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.変数宣言のDimとデータ型｜VBA入門
6.条件分岐（IF）｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBAサンプル集
- ブックを開かずにセル値を取得（ExecuteExcel4Macro,Excel.Application）
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。