Excel VBA カンマ区切りのテキストデータを読み込む

カンマ区切りのテキストデータを読み込む方法

異なるアプリケーション間でデータの受け渡しをする場合は、ひと固まりのデータをカンマで区切ったCSV形式のテキストファイルを利用することが良くあります。

CSV形式のテキストファイルをエクセルで開くにはWorkbooks【ﾜｰｸﾌﾞｯｸｽ】コレクションのOpenText【ｵｰﾌﾟﾝﾃｷｽﾄ】メソッドを使用します。

カンマ区切りのテキストデータを読み込むコードと解説

このコードが記述されたブックと同じフォルダ内のテキストファイルをテキスト名と同じ名前の新しいブックで開いて「.xlsx」形式で保存するコードです。このコードが記述されたブックと同じフォルダー内にテキストファイルをおいて、マクロを実行してください。

Sub テキスト読み込み()
Dim 新ブック名 As String
 Workbooks.OpenText Filename:=ThisWorkbook.Path & "¥*.txt", DataType:=xlDelimited, Comma:=True
 新ブック名 = ActiveWorkbook.Name
 新ブック名 = Left(新ブック名, Len(新ブック名) - 4)
 新ブック名 = 新ブック名 & ".xlsx"
 ActiveSheet.Range("A1").CurrentRegion.EntireColumn.AutoFit
 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "¥" & 新ブック名, FileFormat:=xlOpenXMLWorkbook
 Application.Quit
 Workbooks(新ブック名).Close True
End Sub

2行目【 Dim 新ブック名 As String 】
テキストの内容を読み込むブックの名前を格納する変数「新ブック名」を文字列型(String)で宣言します。

3行目【Workbooks.OpenText Filename:=ThisWorkbook.Path & “¥*.txt”, DataType:=xlDelimited, Comma:=True　】
Workbooks【ﾜｰｸﾌﾞｯｸｽ】コレクションのOpenText【ｵｰﾌﾟﾝﾃｷｽﾄ】メソッドを使用して、引数Filename【ﾌｧｲﾙﾈｰﾑ】に、このブックのパス（場所）でファイル名がわからないのでファイル名にワイルドカード(*)を使用し、拡張子をtxtにします。
引数DataType【ﾃﾞｰﾀﾀｲﾌﾟ】をカンマ区切り形式にして引数Comma【ｶﾝﾏ】をTrueにして、テキストファイルを新しいエクセルブックで開きます。ワイルドカードとは、どんな対象文字、ないし文字列にもマッチするもののことで（?)は任意の1文字（*）は任意の文字列を意味します。

4行目【新ブック名 = ActiveWorkbook.Name 】
OpenText【ｵｰﾌﾟﾝﾃｷｽﾄ】メソッドで、開かれた新しいブックの名前つまり、ファイル名を変数「新ブック名」に代入します。

5行目【新ブック名 = Left(新ブック名, Len(新ブック名) – 4) 】
文字列の左から指定した文字数分の文字列を取り出すLeft【ﾚﾌﾄ】関数を使用して変数「新ブック名」に格納されている文字列の文字数を文字列の長さを取得するLen【ﾚﾝ】関数で文字列の数を取得し、変数「新ブック名」に格納されているファイル名の拡張子部分(.txt)4文字分を減算して拡張子の無いファイル名を取得して変数「新ブック名」に改めて代入します。

6行目【新ブック名 = 新ブック名 & “.xlsx” 】
変数「新ブック名」に格納されている拡張子が無いブック名にエクセルファイルの拡張子（.xlsx）をアンパサンド「&」で連結して、変数「新ブック名」に代入します。

7行目【 ActiveSheet.Range(“A1”).CurrentRegion.EntireColumn.AutoFit 】
Range【ﾚﾝｼﾞ】オブジェクトのCurrentRegion【ｶﾚﾝﾄﾘｰｼﾞｮﾝ】プロパティでA1セルから始まるデータ範囲を参照し、Range【ﾚﾝｼﾞ】オブジェクトのEntireColumn【ｴﾝﾀｲﾔｶﾗﾑ】プロパティで指定したセル範囲のすべての列を参照して、AutoFit【ｵｰﾄﾌｨｯﾄ】メソッドで文字列の幅に列幅を合わせます。

8行目【 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & “¥” & 新ブック名, FileFormat:=xlOpenXMLWorkbook 】
Workbook【ﾜｰｸﾌﾞｯｸ】オブジェクトのSaveAs【ｾｰﾌﾞｱｽﾞ】メソッドを使用してブックを「名前を付けて保存」します。保存時のファイル形式を設定する引数FileFormat【ﾌｧｲﾙﾌｫｰﾏｯﾄ】には、「Excelブック」を表す定数を指定します。

9行目【 Application.Quit 】
Application【ｱﾌﾟﾘｹｰｼｮﾝ】オブジェクトのQuit【ｸｲｯﾄ】メソッドを使用してエクセルアプリケーションの終了予約を宣言します。※他のエクセルが開いていると保存しないで閉じてしまうので他のエクセルファイルはあらかじめ
閉じておいてください。

10行目【 Workbooks(新ブック名).Close True 】

Workbooks【ﾜｰｸﾌﾞｯｸｽ】プロパティで参照した「新ブック」のWorkbook【ﾜｰｸﾌﾞｯｸ】オブジェクトのClose【ｸﾛｰｽﾞ】メソッドでブック「新ブック」を引数にTrueを設定したので保存して閉じます。

フォーマットを指定して読み込む方法

例えば、A列のデータが0から始まる何かのコード番号（0001)のような場合、Excelでは、数値と判断して（1）に変換してしまいます。このような場合は、OpenText【ｵｰﾌﾟﾝﾃｷｽﾄ】メソッドの引数FieldInfo【ﾌｨｰﾙﾄﾞｲﾝﾌｫ】に書式を設定して読み込みます。

引数FieldInfo(ﾌｨｰﾙﾄﾞｲﾝﾌｫ)の設定書式

FieldInfo:=Array(Array(列情報,書式）,Array(列情報,書式）・・）

列情報の部分は、CSV形式であれば列番号、固定長形式であれば、各列の始まるバイト数を指定します。

書式を設定する定数一覧表

定数	値	内容
xlGeneralFormat	1	標準(自動判断)
xltextFormat	2	文字列
xYMDFormat	5	日付(YMD式)
xlSkipColumn	9	読み込まない

コード例

FieldInfo:=Array(Array(1,2）,Array(2,5）,Array(3,1））

このコードをOpenText【ｵｰﾌﾟﾝﾃｷｽﾄ】メソッドの引数として指定します。
1列目の書式を2、定数xltextFormat（文字列）に指定しています。
2列目の書式を3、定数xlYMDFormat（日付）に指定しています。
3列目の書式を1　定数xlGeneralForma（標準）に指定しています。
※FieldInfoで書式を指定する場合はすべてのフィールド（列）に対して書式を設定しなければいけません。

以上でカンマ区切りのテキストデータを読み込む方法についての解説を終了します。
ありがとうございました。