概要

Excel VBAでCSVオープンするときのパフォーマンス比較です。
ついでにCSVオープン用のクラスモジュールも作ったので紹介してます。

比較対象

下記方法の実行時間を比較します。
1. Workbooks.Openでファイルオープン + セル参照
2. Workbooks.OpenTextでファイルオープン + セル参照
3. Openで開いて1行ずつ読む + Splitで分割
4. OpenAsTextStream.ReadAllで全部読む + 正規表現で解析
5. QueryTablesを使ってファイルオープン + セル参照

結果

ちょうど環境があったExcel 2007とExcel 2013での計測結果です。
横軸は、10個のCSVファイルのオープンを3回行ったときの処理時間になってます。

遅い。読み込むだけなら使わない方がいい。
遅い。読み込むだけなら使わない方がいい。
ダントツ早い。ただし、ダブルクォーテーション内にカンマがあると正しく読めない。
シンプルなCSVであればオススメ。
そこそこ早い。CSVにダブルクォーテーションが入っている場合は、こちらがオススメ。
そこそこ早い。読み込んだ結果をシートに貼り付けたい場合は、こちらがオススメ。

実際のコード

以下に置いてます。
https://github.com/minoru-nagasawa/ExcelVbaCsvReader/
https://github.com/minoru-nagasawa/ExcelVbaCsvReader/tree/master/PerformanceData

計測コードの中身

以下が計測に使用したコードです。
※ 各関数の呼び出し元でApplication.ScreenUpdating = Falseしてます。

1. Workbooks.Openでファイルオープン + セル参照

MainModule.vb

Private Sub BookOpen()
    Dim count As Long
    count = 0

    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim folder, file
    Set folder = fso.GetFolder(Range("FOLDER"))
    For Each file In folder.Files
        Dim wb As Workbook
        Set wb = Workbooks.Open(file.Path)

        Dim i As Long
        i = 1
        Do While wb.Worksheets(1).Cells(i, 1) <> ""
            count = count + wb.Worksheets(1).Cells(i, 1)
            i = i + 1
        Loop

        wb.Close
        Set file = Nothing
    Next

    ' 念のため解放
    Set folder = Nothing
    Set fso = Nothing
End Sub

2. Workbooks.OpenTextでファイルオープン + セル参照

MainModule.vb

Private Sub BookOpenText()
    Dim count As Long
    count = 0

    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim folder, file
    Set folder = fso.GetFolder(Range("FOLDER"))
    For Each file In folder.Files
        Call Workbooks.OpenText(file.Path)
        Dim wb As Workbook
        Set wb = Workbooks.Item(file.name)

        Dim i As Long
        i = 1
        Do While wb.Worksheets(1).Cells(i, 1) <> ""
            count = count + wb.Worksheets(1).Cells(i, 1)
            i = i + 1
        Loop

        wb.Close
        Set file = Nothing
    Next

    ' 念のため解放
    Set folder = Nothing
    Set fso = Nothing
End Sub

3. Openで開いて1行ずつ読む + Splitで分割

MainModule.vb

Private Sub OpenSplit()
    Dim count As Long
    count = 0

    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim folder, file
    Set folder = fso.GetFolder(Range("FOLDER"))
    For Each file In folder.Files

        Dim num As Long
        num = FreeFile
        Open file.Path For Input As #num

        Dim line As String
        Do While Not EOF(num)
            Line Input #num, line

            Dim splitted
            splitted = Split(line, ",")
            count = count + Val(splitted(0))
        Loop
        Close #num
        Set file = Nothing
    Next

    ' 念のため解放
    Set folder = Nothing
    Set fso = Nothing
End Sub

4. OpenAsTextStream.ReadAllで全部読む + 正規表現で解析

MainModule.vb

Private Sub OpenAsTextStreamRegex()
    Dim count As Long
    count = 0

    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim reader As CsvReader
    Set reader = New CsvReader

    Dim folder, file
    Set folder = fso.GetFolder(Range("FOLDER"))
    For Each file In folder.Files
        Call reader.OpenCsv(file.Path)

        Dim i As Long
        For i = 1 To reader.RowCount
            count = count + Val(reader.At(i, 1))
        Next
        Set file = Nothing
    Next

    ' 念のため解放
    Set folder = Nothing
    Set fso = Nothing
End Sub

5. QueryTablesを使ってファイルオープン + セル参照

MainModule.vb

Private Sub BookQueryTables()
    Dim count As Long
    count = 0

    ' QueryTables.Addすると定義される名前
    Dim tempQueryTableName As String
    tempQueryTableName = "BookQueryTablesQueryTable"

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Temp")

    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim folder, file
    Set folder = fso.GetFolder(Range("FOLDER"))
    For Each file In folder.Files
        Dim qt As QueryTable
        Set qt = ws.QueryTables.Add(Connection:="TEXT;" & file.Path, Destination:=ws.Range("A1"))
        qt.name = tempQueryTableName
        qt.TextFileCommaDelimiter = True
        qt.RefreshStyle = xlOverwriteCells
        qt.Refresh
        qt.Delete

        Dim i As Long
        i = 1
        Do While ws.Cells(i, 1) <> ""
            count = count + ws.Cells(i, 1)
            i = i + 1
        Loop

        ws.Cells.Clear
        Set file = Nothing
    Next

    ' 名前が定義されるので削除
    Dim n
    For Each n In ActiveWorkbook.Names
        If InStr(1, n.name, tempQueryTableName) <> 0 Then
            n.Delete
        End If
    Next

    ' 念のため解放
    Set folder = Nothing
    Set fso = Nothing
End Sub

CsvReaderクラスモジュールの紹介

計測のついでに、Workbooks.Openに近い感じで使えるCsvReaderクラスモジュールを作りました。
Workbooks.Openで開いて、Cells(row,column)で参照していた代わりに
CsvReaderを生成 + OpenCsvで開く + At(row,column)で参照すればいいので、容易に移行できるかと思います。

使い方は以下です。

CsvReaderを使ってCSVファイルを開くコード

    ' 生成
    Dim reader As CsvReader
    Set reader = New CsvReader

    ' 開く
    Call reader.OpenCsv(ThisWorkbook.Path & "\CsvReaderTestCsv.csv")

    ' 参照
    Debug.Print reader.At(1, 1)

参考：Workbooks.Openを使ってCSVファイルを開くコード

    ' 開く
    Dim wb As Workbook
    Set wb = Workbooks.Open(filePath)

    ' 参照
    Debug.Print wb.Worksheets(1).Cells(1, 3)

    ' 閉じる
    wb.Close

内部の動きとしては、OpenAsTextStream.ReadAllで全部読み込んで、正規表現で解析して、Dictionaryに格納してます。
https://github.com/minoru-nagasawa/ExcelVbaCsvReader/blob/master/CsvReader.cls

注意点：フィールドが数値の時、Workbooks.OpenだとExcelが勝手にTrimしてますが、CsvReaderは生値を返すため、結果が違う場合があります。