ExcelVBAを使ったCSVデータの読み込みまとめ②

VBAを使用したCSVデータ（拡張子txt）の一括読み込み方法について備忘録的に紹介していきたいと思います。

前回は、CSV読込のためのOpenステートメント、Line Input #ステートメントを使ったコードを紹介しました。

ExcelVBAを使ったCSVデータの読み込み　まとめ①

VBAを使用したCSVデータの一括読み込み方法について紹介しています。

VBAを使ったCSVデータの読み込み（データ要素内の「,」「”」処理）はこちら↓↓

VBAを使ったCSVデータの読み込み「,」「”」処理

VBAでCSVデータを読み込みたい場合に、データ要素内に「,（カンマ）」や「”（）ダブルクォート」が存在している場合の対処方法について解説しています。

今回は、ファイルシステムオブジェクトのText Streamオブジェクトを使った方法を紹介します。

SHIFT-JIS（ＡＮＳＩ）カンマ区切りテキストファイル３つを一括してエクセルに読み込む
1. エクセルへの読込
2. 大まかなコードの流れ（方法）
ファイルシステムオブジェクト
1. FSOのオブジェクト構成
2. ファイルシステムオブジェクト
  1. FSOへの参照設定（事前バインディング）
  2. CreateObjectで生成（遅延バインディング）
フォルダの選択
フォルダの中の全てのファイルを取得
拡張子を取得してファイルを選別
TextStreamオブジェクト
読み込みのループ
まとめ

SHIFT-JIS（ＡＮＳＩ）カンマ区切りテキストファイル３つを一括してエクセルに読み込む

SHIFT-JIS（ＡＮＳＩ）カンマ区切りテキストファイルの３ファイルを一括してエクセルに読み込んでみたいと思います。（インプット先のエクセル（CSV連結マクロ.xlsm）に同一フォルダ内の「うまい棒一覧データ」を読み込みます。）

3つのテキストファイルには上記のように日付ごとのデータが入っています。

エクセルへの読込

エクセルへの読み込み

大まかなコードの流れ（方法）

ファイルシステムオブジェクト（FSO）のインスタンス生成
Application.FileDialogを使ってテキストファイルが入っているフォルダを取得（指定）する
FSOとFor Each分を使ってフォルダ内のファイルを１つずつ取得していく
FSOのGetExtensionNameメソッドを使って拡張子がTXTのみのファイルを選ぶ
選んだファイルをTextStreamオブジェクトで読込モードにする
読込モードのテキストファイルを1行ずつ変数に格納したうえでエクセルにインプットしていく


Sub CSV読み込み_2()

１　Dim FSO As New Scripting.FileSystemObject 'ファイルシステムオブジェクトインスタンス生成
２　Dim fd As FileDialog
３　Dim FldName As String
４　Dim f As file
５　Dim file As TextStream 'テキストストリームオブジェクト変数
６　Dim arrybuf As Variant
７　Dim r As Integer

８　Set fd = Application.FileDialog(msoFileDialogFolderPicker) 'フォルダの選択

９　If fd.Show = True Then
10    FldName = fd.SelectedItems(1) '選択したフォルダを変数に格納
11  Else
12    Exit Sub
13  End If

14  r = 2 '読込先セルの先頭行

15  For Each f In FSO.GetFolder(FldName).Files 'For～Each文でフォルダ内のファイルを１つずつ取り出す

16  If FSO.GetExtensionName(f) Like "txt" Then '拡張子がtxtならば
17     Set file = FSO.OpenTextFile(f, ForReading) 'テキストファイルを読み込みモードで変数に格納
18     file.SkipLine 'タイトル行除外
  
19  Do Until file.AtEndOfStream = True '読み込み位置がテキストファイルの末尾になるまで
20   arrybuf = Split(file.ReadLine, ",") '配列変数に格納
21  Sheet1.Cells(r, 1).Resize(, UBound(arrybuf) + 1).Value = arrybuf '配列変数をセルに読み込み
22   r = r + 1 '次の行に移行
23  Loop
      
24 End If
  
25 Next

26 file.Close
27 Set FSO = Nothing
28 Set file = Nothing

End Sub

ファイルシステムオブジェクト

ファイルシステムオブジェクト（以下FSO)とは、フォルダーやファイル、ドライブを操作するためのオブジェクトです。

オブジェクトなので、例えばワークブックやワークシート、セルと同じ位置づけです。

それらにプロパティやメソッドが存在するのと一緒で、FSOにもファイル等を操作するプロパティやメソッドが存在します。

今回使っているのはFSOのプロパティ、メソッドは、

①FSOとしてフォルダオブジェクトを取得するGetFolderメソッド（コード：15行目）

②フォルダオブジェクトのファイルを取得するFilesプロパティ（コード：15行目）

③ファイルの拡張子を取得するGetExtensionNameメソッド（コード：16行目）

④テキストファイルの読み込みや書き込みをするTextStreamオブジェクト（コード：５，17行目）

⑤指定したファイルをTextStreamオブジェクトとして開くOpen TextFileメソッド（コード：17行目）

⑥TextStreamオブジェクトとして開いたファイルの読み込み位置を1行移動させるSkipLineメソッド（コード：18行目）

⑦TextStreamオブジェクトとして開いたファイルの読み込み位置がテキストファイルの末尾かどうかを調べるAtEndOfStreamプロパティ（コード：19行目）

⑧TextStreamオブジェクトとして開いたファイルを1行ずつ読み込むReadLineメソッド（コード：20行目）

このようになります。

ただ、ここで疑問なのは、フォルダオブジェクト、TextStreamオブジェクトが混ざっているという点です。オブジェクトの中にオブジェクトがあります。

FSOのオブジェクト構成

ＦＳＯの構成として以下のようになっています。

ファイルシステムオブジェクト・・・最上位オブジェクト

　　Driveオブジェクト・・ドライブを表すオブジェクト

　　Folderオブジェクト・・フォルダを取得するオブジェクト

　　Fileオブジェクト・・ファイルを取得するオブジェクト

　　TextStreamオブジェクト・・テキストファイルを取得するオブジェクト

つまり、FSOはファイルやフォルダなどを操作するオブジェクトですが、FSOで実際にファイルやフォルダ、TextStream（テキストファイルの読み書き）操作するためには、それらのオブジェクトを取得・呼び出さないといけないというわけです。（※それぞれの取得メソッドは赤字部分です。）

FSOは最上位オブジェクトなので、必ずFSOを取得してから、その他の下位オブジェクトを取得するとう順番です。

FSOでAフォルダに入っているファイルを操作したい！

じゃあ、FSO.GetFolderメソッドでAフォルダを取得すればいいよ

これがFSOのお作法なんだね！

ファイルシステムオブジェクト

話は前後しますが、ファイルシステムオブジェクト自体を取得するにはどうしたらよいか？

FSOへの参照設定→「Microsoft Scripting Runtime」→インスタンスの生成
CreateObjectで生成

上記の２つの方法から選べます。

１は実行時バインディング、２は遅延バインディングと言います。

１の参照設定はブックごとに行う必要があります。つまり、ブックを変えてFSOを使う場合はブックを変える度に参照設定を行うということです。

どちらの方法でも良いと思いますが、１の利点としてはVBEのインテリセンス機能が使えて自動メンバー表示されます。

FSOへの参照設定（事前バインディング）

CreateObjectで生成（遅延バインディング）


Sub ファイルシステムオブジェクト()

    Dim FSO As Object
     
    Set FSO = CreateObject("Scripting.FileSystemObject")
     
End Sub

フォルダの選択


８　Set fd = Application.FileDialog(msoFileDialogFolderPicker) 'フォルダの選択

９　If fd.Show = True Then
10    FldName = fd.SelectedItems(1) '選択したフォルダを変数に格納
11  Else
12    Exit Sub
13  End If

Application.fileDialog（filedialogtype）は、エクスプローラーを開いてフォルダーを選択する動作です。

【使用できる定数一覧】

msoFileDialogFilePicker。ユーザーがファイルを選択するのを許可します。
msoFileDialogFolderPicker。ユーザーがフォルダーを選択するのを許可します。
msoFileDialogOpen。ユーザーがファイルを開くのを許可します。
msoFileDialogSaveAs。ユーザーがファイルを保存するのを許可します

Application.FileDialog プロパティ (Excel)

OfficeVBAリファレンストピック

ファイル選択・フォルダ選択の詳細記事はこちら↓

VBAファイルを開く方法まとめ②

Application内のFileDialogオブジェクトについて、プロパティ、メソッドの活用方法をコードを使って紹介しています。

フォルダの中の全てのファイルを取得


15  For Each f In FSO.GetFolder(FldName).Files 'For～Each文でフォルダ内のファイルを１つずつ取り出す

先に述べたGetFolderメソッドでファイルシステムオブジェクトとしてフォルダを取得します。

FSOとしてフォルダを取得しないとフォルダオブジェクトのプロパティ、メソッドが使えません。

FSOとしてフォルダオブジェクトを取得して、その中のフォルダオブジェクト.FilesでFilesコレクションの中からFor Eachで１つずつファイルを取り出していきます。（filesコレクションはファイルの集合体のことです。）

余談ですが、FSOを扱う場合、これはFSOのプロパティなのか？フォルダオブジェクトのメソッド？プロパティ？ファイルオブジェクトの呼び出し方は？みたいな形でどのオブジェクトのプロパティなのか？メソッドなのか？ということを整理しながら使うことが大事です。

拡張子を取得してファイルを選別


16  If FSO.GetExtensionName(f) Like "txt" Then '拡張子がtxtならば

FSOのGetExtensionNameメソッドで拡張子txtのみを選別します。

これについてはフォルダの中にテキストファイル以外のファイルが入っている場合に有効です。今回はインプット先の「CSV連結マクロ.xlsm」、「アポロチョコ一覧.csv」は除外ですね。

TextStreamオブジェクト


５　Dim file As TextStream 'テキストストリームオブジェクト変数


17     Set file = FSO.OpenTextFile(f, ForReading) 'テキストファイルを読み込みモードで変数に格納
18     file.SkipLine 'タイトル行除外

TextStreamオブジェクトはテキストファイルの読み込みや書き込みをするためのオブジェクトです。コード：５行目のようにTextStreamオブジェクト変数を宣言します。

コード：17行目のようにOpenTextFileメソッドで、テキストファイルを指定のモード（定数）で開きます。

OpenTextFile(FineName,IOMode,Create)

IoModeの定数	内容
ForReadding	読み込みモードでテキストファイルを開く
ForWriting	書き込み（上書き）モードでテキストファイルを開く
ForAppending	書き込み（追記）モードでテキストファイルを開く

定数

Create・・・テキストファイルが存在しない場合の動作を指定（省略可）

コード：18行目はTextStreamオブジェクトのSkipLineメソッドです。読込位置を１行下に移動させます。

読み込みのループ


19  Do Until file.AtEndOfStream = True '読み込み位置がテキストファイルの末尾になるまで
20   arrybuf = Split(file.ReadLine, ",") '配列変数に格納
21  Sheet1.Cells(r, 1).Resize(, UBound(arrybuf) + 1).Value = arrybuf '配列変数をセルに読み込み
22   r = r + 1 '次の行に移行
23  Loop

コード：19行目はTextStreamオブジェクトのAtEndOfStreamプロパティで、テキストファイルの末尾にある場合はTrueを返します。よって、末尾になるまでループということですね。

コード：20行目はTextStreamオブジェクトのReadLineメソッドです。１行ずつ読み込んで配列変数に格納しています。

読み込み方の２パターン