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

はじめに

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

Workbooks.OpenText 関数を使って csv 形式のファイルを開けます。

文字コードや区切り文字、各列の型などを指定できます。

QueryTables.Add との違い

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

Workbooks.OpenText 関数の特徴：

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

QueryTables.Add 関数の特徴：

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

おすすめは .csv を制御できる「CSV ファイルの読み込み (QueryTables.Add)」です。

ファイルの読み込み

Call Workbooks.OpenText("D:\Tips.csv", Origin:=932, Comma:=True) ' CSV を開く

Workbooks.OpenText 関数で指定したパスのファイルを開きます。開いたファイルは、必ず新しい Workbook にファイル名のシートで作成されます。 Tips.csv ファイルを開くと次のようになります。

文字コードの指定

Call Workbooks.OpenText("D:\TipsJIS.csv", Origin:=932, Comma:=True)       ' Shift_JIS を開く
Call Workbooks.OpenText("D:\TipsUTF8.txt", Origin:=65001, Comma:=True)    ' .txt の UTF-8 を開く
Call Workbooks.OpenText("D:\TipsUTF8.csv", Origin:=65001, Comma:=True)    ' .csv の UTF-8 は文字化け
Call Workbooks.OpenText("D:\TipsUTF8BOM.csv", Origin:=65001, Comma:=True) ' BOM 付き を開く
Call Workbooks.OpenText("D:\TipsUTF16.csv", Comma:=True)                  ' UTF-16 を開く
Call Workbooks.OpenText("D:\TipsUTF16.csv", Origin:=1200, Comma:=True)    ' エラー発生

Origin 引数にコードページの番号を指定すると、その文字コードでファイルを開きます。

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

Origin 引数は必ず指定するようにします。省略すると何の文字コードで開くかあいまいになります。

UTF-8 のファイルを開くとき BOM 付きなら正しく開けます。一般的な BOM なしだと文字化けします。これを回避するには拡張子を .csv から .txt にすることです。.txt なら BOM なしの UTF-8 のファイルを正しく開けます。

UTF-16 のファイルを開くときに Origin:=1200 を指定するとエラーになります。このため UTF-16 のファイルは正しく開ける時もあれば、文字化けするときもあります。

区切り文字を指定

Call Workbooks.OpenText("D:\Tips.csv", Origin:=932, Comma:=True)     ' カンマ区切り
Call Workbooks.OpenText("D:\Tips.txt", Origin:=932, Tab:=True)       ' タブ区切り
Call Workbooks.OpenText("D:\Tips.txt", Origin:=932, Semicolon:=True) ' セミコロン区切り
Call Workbooks.OpenText("D:\Tips.txt", Origin:=932, Space:=True)     ' スペース区切り
Call Workbooks.OpenText("D:\Tips.txt", Origin:=932, Other:=True, OtherChar:="a") ' 区切り文字を指定できる

Comma, Tab, Semicolon, Space, Other の引数に True を設定すると、それらを区切り文字として使用します。ただし拡張子が .csv のときは強制的に Comma が True になり、それ以外は無効になります。

Space 引数を True にしたときは、半角と全角の両方のスペースを区切り文字として使用します。

Other 引数を True にしたときは、OtherChar 引数に任意の区切り文字を 1 文字だけ指定できます。

読み込む開始行を指定

Call Workbooks.OpenText("D:\Tips.csv", Origin:=932, Comma:=True)              ' 1 行目から読み込み
Call Workbooks.OpenText("D:\Tips.csv", Origin:=932, Comma:=True, StartRow:=1) ' 1 行目から読み込み
Call Workbooks.OpenText("D:\Tips.csv", Origin:=932, Comma:=True, StartRow:=2) ' 2 行目から読み込み

StartRow 引数に指定した位置の行から読み込みを開始します。

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

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

項目の型を指定

Dim fi As Variant
fi = Array(Array(1, xlTextFormat), _
    Array(2, xlGeneralFormat), _
    Array(3, xlYMDFormat))

Call Workbooks.OpenText("D:\Tips.txt", Origin:=932, FieldInfo:=fi) ' 文字列、数値、日付の順に読み込む

FieldInfo 引数に各列の型を指定して読み込めます。ただし拡張子が .csv のときは自動判別されるため、指定しても意味がありません。

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

FieldInfo 引数の指定の仕方は配列で Array(Array(列番号, 型), Array(列番号, 型) …) のようにして渡します。1 番目の列番号は 1 です。

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

開いたシートを移動する

開いたシートを VBA を実行しているワークブックに移動するには次のようにします。

Call Workbooks.OpenText("D:\Tips.csv", Origin:=932, Comma:=True)    ' CSV を開く

Dim ws As Worksheet
Set ws = Workbooks.Item(Workbooks.Count).Sheets(1)                  ' 開いた CSV のシート
Call ws.Move(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ' VBA を実行しているワークブックの最後に移動

シートは移動せずにデータだけをコピーするには次のようにします。

      
Application.ScreenUpdating = False ' 描画を止める

Call Workbooks.OpenText("D:\Tips.csv", Origin:=932, Comma:=True) ' CSV を開く

Dim csvWb As Workbook
Set csvWb = Workbooks.Item(Workbooks.Count) ' 開いた CSV のワークブック

Dim csvWs As Worksheet
Set csvWs = csvWb.Sheets(1)                 ' 開いた CSV のシート

Dim vbaWs As Worksheet
Set vbaWs = ThisWorkbook.ActiveSheet        ' VBA を実行しているワークブックの選択しているシート

csvWs.UsedRange.Copy                            ' CSV のデータの範囲をコピー
Call vbaWs.Range("A1").PasteSpecial(xlPasteAll) ' 選択しているシートの A1 に貼り付け
Application.CutCopyMode = False                 ' コピー状態を解除

csvWb.Close (False)      ' CSV のワークブックを閉じる
vbaWs.Range("A1").Select ' 貼り付けた範囲選択を解除する

Application.ScreenUpdating = True ' 描画を再開する

描画を止めているのは、新しいワークブックが一瞬だけ表示されるのを防ぐためです。

Set vbaWs = ThisWorkbook.ActiveSheet で、今表示しているシートを取得して、そこに貼り付けようとしています。これを変更すれば貼り付けるシートを変えられます。

csvWs.UsedRange.Copy で、読み込んだデータの全ての範囲をコピーします。

Call vbaWs.Range("A1").PasteSpecial(xlPasteAll) で、コピーしたデータを A1 のセルから貼り付けています。このセルを変更すれば好きな位置に貼り付けられます。

Workbooks.OpenText 関数

Workbooks.OpenText(Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)