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

はじめに

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

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

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

CSV ファイルの書き込みについては「CSV ファイルの書き込みと保存」をご覧ください。
  • 目次
    • QueryTables.Add との違い
    • ファイルの読み込み
    • Workbooks.OpenText 関数

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 ファイルを開くと次のようになります。

1
Workbooks.OpenText を何度も呼び出すとき、引数の値が前回の値を引き継ぎます。このため引数を省略すると既定値ではなく、前回の値になることに注意してください。これは Excel を閉じるまで引き継がれます。

文字コードの指定

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 です。

定数 説明
xlGeneralFormat (既定)1自動判定
xlTextFormat2文字列
xlMDYFormat3MDY 日付形式
xlDMYFormat4DMY 日付形式
xlYMDFormat5YMD 日付形式
xlMYDFormat6MYD 日付形式
xlDYMFormat7DYM 日付形式
xlYDMFormat8YDM 日付形式
xlSkipColumn9その列を読み込まない
xlEMDFormat10EMD 日付形式

数値の前 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)

名前 説明
引数「Filename」開くファイルのパスを指定します。
引数「Origin」省略できます。文字コードを指定します。
引数「StartRow」省略できます。読み込みを開始する行番号を指定します。既定値は 1 です。
引数「DataType」省略できます。データの形式を指定します。
  • xlDelimited (既定):区切り文字によってファイルが区切られます。
  • xlFixedWidth:ファイルのデータが固定幅の列に配置されます。
引数「TextQualifier」省略できます。文字列の引用符を指定します。
  • xlTextQualifierDoubleQuote (既定):ダブルクォーテーション。
  • xlTextQualifierNone:引用符なし。
  • xlTextQualifierSingleQuote:シングルクォーテーション。
引数「ConsecutiveDelimiter」省略できます。連続した区切り文字を 1 文字として扱うかを指定します。
  • True:扱う
  • False (既定):扱わない
引数「Tab」省略できます。区切り文字にタブを使うときは True を指定します。既定値は False です。
引数「Semicolon」省略できます。区切り文字にセミコロンを使うときは True を指定します。既定値は False です。
引数「Comma」省略できます。区切り文字にカンマを使うときは True を指定します。既定値は False です。
引数「Space」省略できます。区切り文字にスペースを使うときは True を指定します。既定値は False です。
引数「Other」省略できます。区切り文字に OtherChar 引数の文字を使うときは True を指定します。既定値は False です。
引数「OtherChar」省略できます。区切り文字に使う文字を 1 文字だけ指定します。
引数「FieldInfo」省略できます。読み込む列のデータの型を指定します。
引数「TextVisualLayout」省略できます。テキストの視覚的な配置を指定します。
引数「DecimalSeparator」省略できます。Excel で数値を認識する場合に使う小数点の記号です。既定はシステム設定です。
引数「ThousandsSeparator」省略できます。Excel で数値を認識する場合に使う桁区切り記号です。既定はシステム設定です。
引数「TrailingMinusNumbers」省略できます。末尾に負符号が付く数値を負の数値として扱う場合は、True を指定します。False を指定するか、引数を省略した場合、末尾に負符号が付く数値は文字列として扱われます。
引数「Local」省略できます。区切り記号、数値、およびデータの書式にコンピューターの地域設定を使用する場合は、True を指定します。
戻り値の型なし