Excel VBA:Line Inputステートメントを使用してCSVファイル(カンマ区切り)の中身を一行ずつ読み込んでワークシートのセルに書き出すVBAのサンプルプログラム

Excel

Excelであれば通常そのままCSVファイルを直接開くことができますが、VBAのLine Inputステートメントを使用してCSVファイルのデータを一行ずつ読み込んでワークシートの各セルに読み込んだデータを書き込む方法についての覚書です。

CSVファイルを読み込む際に個々のデータに何らかの加工をした上でExcelに展開したかったり、同じ構造を持つ複数のCSVファイルを一つの表としてExcelに書き込ませたいときなどに使いまわしているプログラム紹介します。

サンプルプログラムには、[ファイルを開く]ダイアログボックスを表示して任意のファイルを指定した上で読み込むサンプル、[フォルダ選択]ダイアログボックスを表示して指定した任意のフォルダ内にある全てのCSVファイルを連続して読み込むサンプルプログラムも紹介します。

CSVファイルをVBAを使用して読み込む方法について

CSVファイルに限らずテキスト形式のファイルを読み込む際には主に2つの方法があります。

Line Inputステートメントを使用する

VBAの標準ファイルI/O(Input、Output)として用意されているLine Inputステートメントを使用してファイルを開き、一行ずつ読み込みます。

FileSystemObjectのReadLineを使用する

ExcelからWindows Script Hostオブジェクトを参照して、FileSystemObjectオブジェクトの機能を借りてテキストファイルに対する処理を実行します。

Line Inputと比べるとファイルの内容をいっぺんに読み込めたり、行のスキップができたりなど使い勝手が良いです。

今回のサンプルプログラムは、Line Inputステートメントを使用しています。

FileSystemObjectを使用したサンプルは別の記事で紹介したいと思います。

Line Inputステートメントを使用したCSVファイルの読み込み基本サンプルプログラム

以下にLine Inputステートメントを使用した基本的なサンプルプログラムを紹介します。

イミディエイトウィンドウにデータを出力するので、[表示]メニューの[イミディエイト ウィンドウ]をクリックしてイミディエイト ウィンドウを表示しておいてください。

サンプルプログラムの説明

Line Inputステートメントを使用してファイルを開いてデータを読み込む際には、必ず以下の情報が必要となります。

  • ファイル名(フルパス) 開きたいファイルのフルパス名です。
  • ファイル番号

ファイル番号について

プログラムから開くファイルに対して一意の識別番号を割り当てる必要があります。

特定の数値を直接割り当てることもできますが、サンプルプログラムでは開いている番号を自動で割り当ててくれるFreeFile関数を使用しています。

ファイルに割り当てた識別番号は、そのファイルが閉じるまで有効となります。

サンプルプログラムでは、上記の情報を全て変数に割り当てています。

サンプルプログラムの流れとしては以下のとおりです。

  1. OpenステートメントでFreeFile関数によって割り当てられたファイル番号を使用してファイルを開く
  2. Line Inputステートメントにより開いたCSVファイルの1行目がstrTextLine変数に代入される Line Inputが実行されるたびに1行ずつ読み込まれます。
  3. 変数strTextLineに読み込まれた1行分のデータをイミディエイト ウィンドウに出力する
  4. 2~3の処理をファイルの終端まで繰り返す
  5. ファイルの終端まで来たら繰り返し処理(Do Until Loop)を抜けてファイルを閉じる

サンプルプログラムのポイント

Line Inputステートメントを使用する上でのポイントです。

  • Openステートメントを使用してファイルを開く際にファイルを識別するための番号を付与する必要がある。
  • Line Inputステートメントではファイルに付与された識別番号を指定する。
  • Line Inputステートメントが実行されたタイミングで指定した変数に1行分のデータ(先頭から改行コードまで)が代入される。
  • Line Inputステートメントを実行するごとに1行ずつ行が進んでいくので、明示的に次の行に進む命令を実行する必要はないし、そもそもそのような命令もない。
  • Line Inputステートメントでは行をスキップしたり、指定した行番号から読み込むというような指定はできない。 ただし、別の方法で行をスキップや指定した行数から読み込みを開始したり、条件に合致した時だけ読み込ませることは可能。
  • 開いたファイルに付与した識別番号は、そのファイルが閉じるまで保持されるため、必要なくなったら明示的に必ずファイルを閉じるのがエチケット。

CSVファイルから読み込んだデータをワークシートのセルに書き出すサンプルプログラム

以下にSample01プログラムを利用して読み込んだデータをワークシートのセルに書き出すサンプルプログラムを紹介します。

Sample02

Sample02のポイント

読み込んだ1行分のデータをセルに書き出す際のポイントは、1行分のデータを”,”を区切り記号として個々のデータとして分解し、配列として変数に代入する点と配列をForループを使ってセルに書き出している点です。

Forループの部分をもう少し詳しく説明します。

変数strTextLineには、Line Inputステートメントによって1行分のデータが入っています。

例えばこんな感じです。

strTextLine = A,B,C

それを”,”(カンマ)区切りで分解して配列として変数arrDataに代入しているのでarrDataの中身を表すとこんな感じです。

arrData(0) = A arrData(1) = B arrData(2) = C

※配列の要素の開始番号は、明示的内定がなければ0からとなります。

Forループで使用されているUbound関数は、配列の要素番号の最大値を返す関数です。 上の例で言えば”2″を返します。

Forループの”i = 0 To Ubound(arrData)”は、arrData配列の0番から2番まで、つまり配列の要素数分繰り返すことを意味しています。

ActiveCell.Offset(行,列) は、現在アクティブなセルから指定された数値分、行方向、列方向分ずらしたセルを示します。

つまり、Forループで i の値は0、1、2と変化していくので以下のようになります。

i = 0 の時

ActiveCell.Offset(0, 0).Value = arrData(0) となり、現在アクティブなセルにarrData(0)の値が書き込まれる。

i = 1 の時

ActiveCell.Offset(0, 1).Value = arrData(1) となり、現在アクティブなセルの1列右のセルにarrData(1)の値が書き込まれる。

i = 2 の時

ActiveCell.Offset(0, 2).Value = arrData(2) となり、現在アクティブなセルの2列右のセルにarrData(2)の値が書き込まれる。

“,”を区切りとして1行分のデータが分解され、各値がセルの横方向に1つ1つ書き出されていきます。

1行分のデータのセルへの書き出しが終わったら(Forループを抜けたら)次の行を書き出す開始位置として、今アクティブなセルを1行下にずらすためにActiveCell.Offset(1, 0).Select を実行しています。

少しわかりづらいかもしれませんが、ウォッチ式で各変数の値を見ながらプログラムをステップ実行すると理解しやすくなると思います。

[ファイルを開く]ダイアログボックスを表示して任意のCSVファイルを指定してセルに書き出すサンプルプログラム

Sample02を更に改良して、プログラム実行時に[ファイルを開く]ダイアログボックスを表示して任意のCSVファイルを選択できるようにしたサンプルプログラムをいかに紹介します。

Sample03

Sample03のポイント

Sample02では開きたいCSVファイルをプログラム中で直接変数に代入していましたが、Sample03ではGetOpenFilenameメソッドを使用して[ファイルを開く]ダイアログボックスを表示し、[ファイルを開く]ダイアログボックスで選択されたファイルを変数に代入するように変更しています。

また、[ファイルを開く]ダイアログボックスで[キャンセル]ボタンが押された時は処理を終了するようにIf文が追加されています。

GetOpenFilenameメソッドを使用して[ファイルを開く]ダイアログボックスを表示する方法についての詳細は以下の記事を参照してください。

ExcelのVBAを使用して[ファイルを開く]ダイアログボックスを表示し、選択されたファイルの名前を取得するサンプルプログラム。 ExcelのVBAで用意されているGetOpenFilenameメソッドを使えば簡単にできます。

指定した任意のフォルダ内にある全てのCSVファイルを読み込んでセルに書き出すサンプルプログラム

指定した任意のフォルダに保存されている複数のCSVファイルを全て読み込んでセルに書き出すサンプルプログラムを紹介します。

サンプルプログラムではFileDialogオブジェクトを使用しますが、このオブジェクトはExcelに標準で存在するオブジェクトではないため、以下の設定を事前に行ってください。

オブジェクトライブラリの参照設定

サンプルプログラムで使用するFileDialogを使用する場合、以下のオブジェクトライブラリを参照設定しておく必要があります。

Microsoft Office 15.0 Object Library

※15.0というバージョンは、Excelのバージョンによって異なります。

オブジェクトライブラリの参照手順

以下に「Microsoft Office 15.0 Object Library」の参照手順を記載します。

  1. Excelのリボンツールバーの[開発]タブをクリックする。
  2. [開発]タブの右端にある[Visual Basic]をクリックする。
  3. [Microsoft Visual Basic for Applications]画面(VBAエディタ)が開くので[ツール]メニューの[参照設定]をクリックする。
  4. [参照可能なライブラリ ファイル]の一覧(五十音順に並んでいます)から「Microsoft Office 15.0 Object Library」のチェックをオンにして[OK]をクリックする。

Microsoft Office 15.0 Object Library

Sample04

Sample04のポイント

ポイントとしては、フォルダを任意に指定できる[参照]ダイアログボックスの表示とDir関数により指定されたフォルダに存在する全てのCSVファイルのファイル名を取得している点です。

Sample04の欠点

サンプルプログラムを実行するとすぐにわかると思いますが、当然ながら全てのCSVファイルの先頭行が読み込まれますので、先頭行が項目名になっている場合、読み込んだファイル分項目名もセルに書き込まれてしまいます。

なので、もう少し改造してみます。

最初に読み込んだファイルの項目名のみ残して他のファイルの項目行(1行目)はスキップするサンプルプログラム

Sample04を改造して最初のファイルの項目名のみ残して以降のファイルの項目名はスキップさせる処理を追加します。

Sample05

Sample05のポイント

一番最初に開いたファイルの項目名だけはセルに書き出し、以降に開いたファイルの1行目にある項目名部分は読み込まないという条件を判断できるようにすることがポイントです。

一番最初に読み込んだファイルのなのか、2ファイル目以降なのかは、ファイルを開くたびに開いたファイル数をカウントする変数を用意してカウントさせます。

読み込む行も同じように今読み込んだ行が開いているファイル何行目なのかをカウントする変数を用意してカウントさせます。

後は、「開いているファイルが2ファイル目以降でかつ、先頭行の場合」という条件を2つのカウント用変数を使って判断させれば良いだけです。

開いているファイルが2ファイル目以降 :

fileCounter > 1

先頭行の場合 :

lineCounter = 1

つまり、

If fileCounter > 1 And lineCounter = 1 Then

となります。

1つ大事な点は、2つのカウント用の変数をリセット(初期化)するコードの挿入場所を間違えるとうまく動作しないので注意してください。

行のスキップについては、Line Inputを実行した時点で行を読み込むので、何も処理せずもう一度Line Inputを実行すれば次の行が読み込まれるのでスキップすることになります。

CSVファイルの中身を一行ずつワークシートのセルに書き出すVBAのサンプルプログラムまとめ

今回のサンプルプログラムは、ExcelのVBAの標準I/OのLine Inputステートメントについて、基本から徐々に改造して複数のファイルを1つのシートにまとめるまで作ってみました。

サンプルプログラムは、ExcelのVBAとして作っていますが、Line Input部分についてはAccessのVBAでも利用できますので参考にしてください。