ExcelマクロVBAサンプル集 | ブックを開かずにセル値を取得(ExecuteExcel4Macro,Excel.Application) | Excelマクロの実用サンプル、エクセルVBA集と解説



最終更新日:2013-09-18

ブックを開かずにセル値を取得(ExecuteExcel4Macro,Excel.Application)


「ブックを開かずにセル値を取得」に関する検索が非常に多いです、

おそらく、ExecuteExcel4Macroに関連しているとか、

もしくは、大量のファイルがある為、時間がかかってしまうと言うものでしょう。

ExecuteExcel4Macroについての簡単な解説は、「ExecuteExcel4Macroについて


サンプルを通して、方法論を解説します。


以下では、マクロ記述してExcelファイルと同一フォルダ内の"test"サブフォルダの全Excelファイルについて処理します。

"test"フォルダには、100KB強のExcelファイルを100個入れて、以下のテストをしました。

ExecuteExcel4Macroで1個のセル値を取得

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.2〜0.3秒で完了します。

Excel.Applicationで1個のセル値を取得

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セルのみ取得しています。

私の環境では、9〜10秒で完了します。

この処理は、ExecuteExcel4Macroに比べたら遅いのですが、

New Excel.ApplicationとExcelインスタンスを生成せずに、

普通に、Workbooks.Open(strDir & strFile)とやるよりは、はるかに高速に動作します。

これですと、3〜4分程度の時間がかかってしまいます。

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セルの取得をしています。

私の環境では、約20秒で完了します。

sample1の1個のセル取得に比べて、約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セルの取得をしています。

私の環境では、約33秒で完了します。

sample2の1個のセル取得に比べて、約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

私の環境では、約10秒で完了します。

sample2の1個のセル取得と、ほとんど変わっていません。

つまり、

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("'" & strDir & "[" & strFile & "]Sheet1'!R1C1")

この、Sheet1は固定で記述しなければなりません。

つまり、シート名が不明な場合はExecuteExcel4Macroは使えないのです。

Excel.Applicationを利用した方法なら、

先頭シート、Worksheets(1)のように記述も出来ますし、もちろん全シートに対しての処理も問題ありません。

結論として、ExecuteExcel4Macroが有用なのは、

・ファイル数が極めて多い
・シート名が固定されている
・取得するセル数が限定的である事


ということになります。

なお、「ExecuteExcel4Macroについて」にもある通り、ExecuteExcel4Macroでは関数も使えます。

ただし、単一の値を取得する関数に限られます。

余程の事情が無い限り、ExecuteExcel4Macroは積極的に使うものではないでしょう。




同じテーマ「ExcelマクロVBAサンプル集」の記事

罫線を簡単に引く(Borders,BorderAround)
マクロの開始・終了(Applicationのプロパティ)
オートフィルター(AutoFilter)
日付のオートフィルタ(AutoFilter)
印刷ダイアログを使用する(xlDialogPrint)
名前定義の一覧(Name)
シートを名前順に並べ替える

新着記事 ・・・新着記事一覧を見る

スプレッドシートが非常に遅い、高速化するには|Google Apps Script入門(1月17日)
画像のトリミング(PictureFormat,Crop)|ExcelマクロVBAサンプル集(12月27日)
シート保護|Google Apps Script入門(12月24日)
表示の固定|Google Apps Script入門(12月24日)
グラフ|Google Apps Script入門(12月21日)
入力規則|Google Apps Script入門(12月13日)
並べ替え|Google Apps Script入門(12月12日)
メモの挿入・削除と改行文字|Google Apps Script入門(12月6日)
リンクの挿入・編集・削除|Google Apps Script入門(12月6日)
セルに数式を入れる|Google Apps Script入門(12月1日)

アクセスランキング ・・・ ランキング一覧を見る

1.RangeとCellsの使い方|ExcelマクロVBA入門
2.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.CSVの読み込み方法|ExcelマクロVBAサンプル集
9.変数とデータ型(Dim)|ExcelマクロVBA入門
10.VBAのFindメソッドの使い方には注意が必要です|ExcelマクロVBA技術解説



  • >
  • >
  • >
  • ブックを開かずにセル値を取得(ExecuteExcel4Macro,Excel.Application)

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP