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で書式を指定する場合はすべてのフィールド(列)に対して書式を設定しなければいけません。


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

スポンサーリンク

関連記事・広告