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

Excel
スポンサーリンク

VBAを使用したテキスト形式CSVデータの一括読み込み方法について備忘録的に紹介していきたいと思います。

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

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

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

スポンサーリンク

SHIFT-JIS(ANSI)カンマ区切りテキストファイル3つを一括してエクセルに読み込む

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

フォルダ内のファイル
CSVファイルのデータ内容

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

エクセルへの読込

エクセルへの読み込み

大まかなコードの流れ(方法)

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

Sub CSV読み込み_2()

1 Dim FSO As New Scripting.FileSystemObject 'ファイルシステムオブジェクトインスタンス生成
2 Dim fd As FileDialog
3 Dim FldName As String
4 Dim f As file
5 Dim file As TextStream 'テキストストリームオブジェクト変数
6 Dim arrybuf As Variant
7 Dim r As Integer

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

9 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文でフォルダ内のファイルを1つずつ取り出す

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オブジェクト(コード:5,17行目)

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

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

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

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

このようになります。

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

FSOのオブジェクト構成

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

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

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

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

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

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

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

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

FSO構成図

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

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

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

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

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

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

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

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

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

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

自動メンバー表示

FSOへの参照設定(事前バインディング)

CreateObjectで生成(遅延バインディング)


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

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

フォルダの選択


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

9 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)
Office VBA reference topic

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


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

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

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

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

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

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


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

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

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

ファイルの選別

TextStreamオブジェクト


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

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

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

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

OpenTextFile(FineName,IOMode,Create)

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

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

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

読み込みのループ


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メソッドです。1行ずつ読み込んで配列変数に格納しています。

読み込み方の2パターン

ReadAllメソッド読み込み位置から末尾までを返す
ReadLineメソッド読み込み位置のある行を1行返す

まとめ

今回はファイルシステムオブジェクトのTextStreamオブジェクトを使ったCSVデータ(テキストファイル)の読み込み方法についてまとめてみました。

次回はADODB.Streamオブジェクトを使ったcsvデータ読込方法と文字化け対策(文字コード自動判定機能)について紹介します。

ExcelVBAを使ったCSVデータの読み込み まとめ③
CSVテキストファイルを文字コードを指定して読み込む方法と各ファイルの文字コードを自動判定して読み込む方法を紹介しています。

コメント

タイトルとURLをコピーしました