サイトについて      連絡先   検索

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

はじめに

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

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

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

QueryTables.Add との違い

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

Workbooks.OpenText 関数の特徴:

QueryTables.Add 関数の特徴:

おすすめは .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 引数にコードページの番号を指定すると、その文字コードでファイルを開きます。

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 を指定します。
戻り値なし

関連ページ