VBA CSV ファイルの読み込み (QueryTables.Add 関数を使う)

はじめに

Excel VBA マクロで CSV ファイルの読み込みをする方法を紹介します。

QueryTables.Add 関数を使って、テキストファイルウィザードと同じようにファイルを開けます。

文字コードや区切り文字、ダブルクォーテーションの扱いや前 0 の表示などを指定できます。

Workbooks.OpenText との違い

似た機能に Workbooks.OpenText 関数があります。二つの特徴の違いを紹介します。

QueryTables.Add 関数の特徴：

• ファイルの読み込み：既存のシートにデータを挿入します。
• 拡張子 .csv の制御：テキスト形式なら、どんな拡張子でも制御できます。
• Excel の「外部データの取り込み」から表示される「テキストファイルウィザード」と同じ機能です。

Workbooks.OpenText 関数の特徴：

• ファイルの読み込み：必ず新しい Workbook が作成されます。
• 拡張子 .csv の制御：区切り文字や項目の型など自動制御されます。.txt であれば制御できます。

おすすめは .csv を制御でき、このページで紹介する QueryTables.Add 関数です。

ファイルの読み込み

Dim ws As Worksheet
Set ws = ActiveSheet ' CSV のデータを取り込むシート

Dim qt As QueryTable
Set qt = ws.QueryTables.Add(Connection:="TEXT;D:\Tips.csv", Destination:=ws.Range("A1")) ' CSV を開く
With qt
    .TextFilePlatform = 932          ' 文字コードを指定
    .TextFileParseType = xlDelimited ' 区切り文字の形式
    .TextFileCommaDelimiter = True   ' カンマ区切り
    .RefreshStyle = xlOverwriteCells ' セルに上書き
    .Refresh                         ' データを表示
    .Delete                          ' CSV との接続を解除
End With

ws.QueryTables.Add 関数で指定したパスのファイルを開きます。戻り値で QueryTable オブジェクトを取得します。

第 1 (Connection) 引数に TEXT;ファイルパス のように TEXT; に続けて開くファイルのパスを指定します。存在しないパスを指定すると、その後の Refresh 関数の呼び出し時にエラーが発生します。

第 2 (Destination) 引数にデータの表示先の左上のセルを指定します。そのセルを基点にして貼り付けたようになります。

QueryTables.Add 関数と Destination 引数の Range は同じ Worksheet にする必要があります。

QueryTable.Refresh 関数を実行するとデータが表示されます。

QueryTable.Delete 関数でファイルとの接続を解除できます。これを実行しないとファイルの変更が Excel にも反映されるようになります。

文字コードの指定

With qt
    .TextFilePlatform = 932   ' Shift_JIS を開く
    .TextFilePlatform = 65001 ' UTF-8 を開く
    .TextFilePlatform = 1200  ' UTF-16 を開く、エラー発生
    .Refresh
End With

TextFilePlatform プロパティにコードページの番号を指定すると、その文字コードでファイルを開きます。省略すると Shift_JIS で開きます。

• Shift_JIS：932
• UTF-8：65001
• UTF-16：1200

UTF-16 のファイルを開くときに .TextFilePlatform = 1200 を指定するとエラーになります。このため値を指定しないで開くことになるので、正しく開けるかどうかはあいまいです。

区切り文字を指定

With qt
    .TextFileParseType = xlDelimited   ' 区切り文字の形式
    .TextFileCommaDelimiter = True     ' カンマ区切り
    .TextFileTabDelimiter = True       ' タブ区切り
    .TextFileSemicolonDelimiter = True ' セミコロン区切り
    .TextFileSpaceDelimiter = True     ' スペース区切り
    .TextFileOtherDelimiter = "a"      ' 区切り文字を指定できる
    .Refresh
End With

TextFileParseType プロパティに xlDelimited を指定すると、指定した区切り文字で区切られて表示されます。既定値は xlDelimited です。

TextFileCommaDelimiter, TextFileTabDelimiter, TextFileSemicolonDelimiter, TextFileSpaceDelimiter プロパティに True を設定すると、それらを区切り文字として使用します。

TextFileTabDelimiter プロパティだけ既定値が True になっています。それ以外の既定値は False です。

TextFileSpaceDelimiter プロパティを True に設定したときは、半角と全角の両方のスペースを区切り文字として使用します。

TextFileOtherDelimiter プロパティには、任意の区切り文字を 1 文字だけ指定できます。

読み込む開始行を指定

With qt
    .TextFileStartRow = 1 ' 1 行目から読み込み
    .TextFileStartRow = 2 ' 2 行目から読み込み
    .Refresh
End With

TextFileStartRow プロパティに指定した位置の行から読み込みを開始します。

既定値は 1 です。最初の行から読み込むときは 1 を指定します。

1 行目が見出しで 2 行目から読み込みたいときは 2 を指定します。

項目の型を指定

With qt
    .TextFileColumnDataTypes = Array(xlTextFormat, xlGeneralFormat, xlYMDFormat) ' 文字列、数値、日付の順に読み込む
    .Refresh
End With

TextFileColumnDataTypes プロパティに各列の型を指定して読み込めます。

何も指定しないまたは xlGeneralFormat を指定したときは、Excel が自動的に判別して、文字や数値、日付に変換します。

指定の仕方は配列で Array(1 列目の型, 2 列目の型 …) のように設定します。

数値の前 0 を表示させたいときは、xlTextFormat を指定して文字列にすると表示されます。

データの挿入の仕方

With qt
    .RefreshStyle = xlInsertDeleteCells ' 挿入または削除します
    .RefreshStyle = xlOverwriteCells    ' セルに上書きします
    .RefreshStyle = xlInsertEntireRows  ' 挿入します
    .Refresh
End With

RefreshStyle プロパティにデータを挿入する方法を指定します。指定の方法は次の通りです。

• xlInsertDeleteCells (既定値)：表示されるセルにデータがあるときは、上書きされないように必要な分の列を挿入します。削除される条件はわかりません。
• xlOverwriteCells：表示されるセルに上書きします。
• xlInsertEntireRows：表示されるセルにデータがあるときは、上書きされないように必要な分の列を挿入します。

データを読み込む前の状態。B2 のセルに RefreshStyle を指定して表示します。

xlInsertDeleteCells を指定します。列が挿入されています。

xlOverwriteCells を指定します。セルにそのまま上書きされています。

xlInsertEntireRows を指定します。列が挿入されています。

その他のプロパティ

With qt
    .AdjustColumnWidth = False                          ' 列幅を自動調整しない
    .TextFileTextQualifier = xlTextQualifierDoubleQuote ' 引用符の指定
    .Refresh
End With

QueryTable オブジェクトには、この他にもプロパティがたくさんあります。その中でも良く使いそうなのを紹介します。

AdjustColumnWidth プロパティを True にすると、表示するデータに合わせて列幅を自動調整します。既定値は True です。

TextFileTextQualifier プロパティで項目の引用符を指定できます。次の種類があります。

• xlTextQualifierNone：引用符なし
• xlTextQualifierDoubleQuote (既定値)：ダブルクォーテーション
• xlTextQualifierSingleQuote：シングルクォーテーション