VBAでCSVファイルを高速にシートに貼り付ける

外部データの取り込み機能

Excelには外部データの取り込み機能が標準で実装されています。

CSVファイルもこの機能で取り込むことが出来るのですが、VBAで1行ずつ処理するよりもとても高速に取り込めることが特徴です。

外部データの取り込み機能はVBAではQueryTableクラスを使います。QueryTableクラスには多くのメソッドとプロパティがありますが、あまり気にする必要はありません。というのも、QueryTableクラスを直接書くよりも、マクロの記録機能を使って外部データの取り込み機能を記録させた方が都合がいいためです。

実際私も何度かこの機能を使ったことがありますが、マクロの記録機能のままで作成されたコードをほとんどそのまま使っていますし、それで問題が起きたことはありません。

外部データの取り込み機能のマクロの記録

CSVファイルの取り込みは以下の手順で行います。

開発タブで「マクロの記録」を押す。記録先はどこでもいいがとりあえず作業中のブックとする。開発タブがない場合はファイルタブ→オプション→リボンのユーザ設定　で「開発」にチェックを付ける。
データタブ→テキストファイル　を押す。ファイル選択ダイアログが表示される。
ファイル選択ダイアログで取り込むCSVファイルを選択し、インポートボタンを押す。ファイルの種類のコンボボックスは「テキストファイル」のままでOK。
テキストファイルウィザードを以下のようにCSVファイルの内容に合わせて適宜設定する。３ページ目は下段の「データのプレビュー」から列を選択して、左上の「列のデータ形式」で列ごとに書式を設定する。ここでは一番左の列を日付YMDで、一番右の列を文字列に設定。
データの取り込みで出力先のシートとセルを設定する。
開発タブで「記録終了」を押す。

以上でQueryTableクラスを使ったCSVファイルの取り込むコードが記録されます。

コードの修正点

上記手順を実行すると以下のようなコードが出力されます。

Sub Macro3()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\test\a.csv", Destination:= _
        Range("$A$1"))
        .Name = "a"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 932
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(5, 1, 1, 1, 1, 1, 1, 1, 1, 2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Sub Macro3()

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;C:\test\a.csv", Destination:= _

Range("$A$1"))

.Name = "a"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 932

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(5, 1, 1, 1, 1, 1, 1, 1, 1, 2)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

End Sub

プロパティがたくさん出力されていますが、無くてもいいものもあります。

元のままでも構いませんが、最低限必要な設定だけでよければ以下のように不要なプロパティを削除できます。

Sub Macro3()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\test\a.csv", Destination:= _
        Range("$A$1"))
        .TextFilePlatform = 932
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(5, 1, 1, 1, 1, 1, 1, 1, 1, 2)
        .Refresh BackgroundQuery:=False
    End With
End Sub

Sub Macro3()

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;C:\test\a.csv", Destination:= _

Range("$A$1"))

.TextFilePlatform = 932

.TextFileCommaDelimiter = True

.TextFileColumnDataTypes = Array(5, 1, 1, 1, 1, 1, 1, 1, 1, 2)

.Refresh BackgroundQuery:=False

End With

End Sub

あとは作成した関数を実行するとシートにCSVファイルの内容が取り込まれます。

プロパティについてはMicrosoftのヘルプの以下がありますので参考にしてください。
https://msdn.microsoft.com/ja-jp/library/office/dn254409.aspx

CSVファイルを外部データ取り込み機能で取り込む場合の注意点

QueryTableクラスでの取り込みを行うと、取り込んだ部分のセルは外部データ範囲として扱われます。

外部データ範囲は元データの関係を保つことが可能です。

そのため、RefreshOnFileOpenプロパティがTrueの場合などで、元のCSVファイルが更新されると、ブックを開く度に最新状態が反映されることになります。

元のCSVファイルとの関係を解除することも可能ですが、それをするかどうかは個別の条件によって異なると思います。

このように、元データとの関係が続くこともありえるため、取り込み後に独立したデータとして扱いたい場合は注意が必要です。

外部データの取り込み機能

外部データの取り込み機能のマクロの記録

コードの修正点

CSVファイルを外部データ取り込み機能で取り込む場合の注意点

関連記事