VBA応用(テキストデータの読み込み)

ワークブックとして開くのではなく、テキストファイルを直接読み込みます。

デザイン済みのシートにデータを直接読み込むことができます。ここでは、古くからのBASICのステートメントの記述方法と、FSO(FileSystemObject)を操作する方法で、テキストファイルを読み書きする方法を解説します。
例えば、CSV形式のファイルを直接開くと、見出しもないデータの羅列がワークシートに展開されてしまいますが、ここでのサンプルの方法であればデザインしたワークシートの見出しの下の行からデータを読み込んで表示させたり、ワークシートに表示させないで内部処理を行なうなどのことができます。
なお、これから新しく取り組む方は古いステートメントではなく、最初からFSO(FileSystemObject)を学んで下さい。

まずは、「古くからあるステートメント」の方法です。

通常の文書のようなテキストデータを改行(CRLF)単位に1行ずつ読み込みます。
動作確認をされる場合は、Ascii(シフトJIS漢字)コードによるテキストファイルを指定して下さい。


'***************************************************************************************************
'   テキストファイル読み込みサンプル                                Module1(Module)
'
'   作成者:井上治  URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'03/07/25(1.00)新規作成
'03/12/04(1.01)初回修正
'20/02/26(1.10)*.xlsm化、他
'***************************************************************************************************
Option Explicit
'===================================================================================================
Private Const g_cnsTitle As String = "テキストファイル読み込み"
Private Const g_cnsFilter As String = "全てのファイル (*.*),*.*"

'***************************************************************************************************
'   ■■■ ワークシート側からの呼び出し処理 ■■■
'***************************************************************************************************
'* 処理名　：READ_TextFile1
'* 機能　　：テキストファイル読み込みサンプル
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2003年07月25日
'* 作成者　：井上　治
'* 更新日　：2020年02月26日
'* 更新者　：井上　治
'* 機能説明：
'* 注意事項：サンプルなのでエラー処理は行なっていません
'***************************************************************************************************
Sub READ_TextFile1()
    '-----------------------------------------------------------------------------------------------
    Dim intFF As Integer                                            ' FreeFile値
    Dim lngRow As Long                                              ' 収容するセルの行
    Dim lngRec As Long                                              ' レコード件数カウンタ
    Dim strFileName As String                                       ' OPENするファイル名(フルパス)
    Dim strRec As String                                            ' 読み込んだレコード内容
    Dim vntFileName As Variant                                      ' ファイル名受取用
    '-----------------------------------------------------------------
    ' ①｢ファイルを開く｣のダイアログでファイル名の指定を受ける
    Application.StatusBar = "読み込むファイル名を指定して下さい。"
    vntFileName = Application.GetOpenFilename(FileFilter:=g_cnsFilter, Title:=g_cnsTitle)
    ' キャンセルされた場合はFalseが返るので以降の処理は行なわない
    If VarType(vntFileName) = vbBoolean Then Exit Sub
    strFileName = vntFileName
    '-----------------------------------------------------------------
    ' ②FreeFile値の取得(以降この値で入出力する)
    intFF = FreeFile
    ' 指定ファイルをOPEN(入力モード)
    Open strFileName For Input As #intFF
    ' 先頭行の設定(2行目から掻き出すので1とする)
    lngRow = 1
    '-----------------------------------------------------------------
    ' ③ファイルのEOF(End of File)まで繰り返す
    Do Until EOF(intFF)
        ' レコード件数カウンタの加算
        lngRec = lngRec + 1
        Application.StatusBar = "読み込み中です．．．．(" & lngRec & "レコード目)"
        ' 改行までをレコードとして読み込む
        Line Input #intFF, strRec
        ' 行を加算しA列にレコード内容を表示(先頭は2行目)
        lngRow = lngRow + 1
        Cells(lngRow, 1).Value = strRec
    Loop
    '-----------------------------------------------------------------
    ' ④指定ファイルをCLOSE
    Close #intFF
    Application.StatusBar = False
    ' 終了の表示
    MsgBox "ファイル読み込みが完了しました。" & vbCr & _
        "レコード件数=" & lngRec & "件", vbInformation, g_cnsTitle
End Sub

'----------------------------------------<< End of Source >>----------------------------------------

ソースコードの線で囲って丸数字をコメントで書き込んだ4つのブロックについて概略を説明します。

№	処理工程概要
①	開くファイル名の受け取り部分です。 `(この画像をクリックすると、このページのサンプルがダウンロードできます)` ここではこのように「ファイルを開く」ダイアログを表示させてユーザーにファイル名を指定してもらっています。プログラム内で決まっているのであればこの工程はなくして定数でも構いません。
②	[前処理]として、指定のテキストファイルを開く処理です。「Openステートメント」では、「As #nn」のnnのところに「ファイルナンバー」を指定します。新規OPENの場合は空き番号ですが、通常は他にOPEN中のファイルがなければ「1」になります。ですが、例えば前回の処理で実行時エラー等でOPENしたままだとすると「1」は使用中ということになります。ここで「ファイルナンバー」を「FreeFile関数」から受け取るようにすれば空き番号を割り当ててくれます。
③	[主処理]として、実際にファイルをEOF(End of File)まで読み込むループです。ループ内では「Line Inputステートメント」で次に発生するCRLFコードまでを1行として読み込みます。読み込んだ内容はこのサンプルでは変数「strRec」に格納されるので、行を加算してA列のセルに転記しています。
④	[後処理]として、ファイルをCLOSEして、終了メッセージを表示しています。

最初に書いた通り、この「古くからあるステートメント」は新たに学ぶ必要はありません。実際のところネットワーク上などの長いファイル名に対応できないなどの制限もあるようです。これからやってみるという方は、この次のFSO(FileSystemObject)だけを学ぶことで良いと思います。

FSO(FileSystemObject)だと、このようになります。

一方、これをFSO(FileSystemObject)で記述すると、


'***************************************************************************************************
'   テキストファイル読み込みサンプル(FSO版)                         Module2(Module)
'
'   作成者:井上治  URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'   [参照設定]
'   ・Microsoft Scripting Runtime
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'03/07/25(1.00)新規作成
'03/12/04(1.01)初回修正
'20/02/26(1.10)*.xlsm化、他
'***************************************************************************************************
Option Explicit
'===================================================================================================
Private Const g_cnsTitle As String = "テキストファイル読み込み"
Private Const g_cnsFilter As String = "全てのファイル (*.*),*.*"

'***************************************************************************************************
'   ■■■ ワークシート側からの呼び出し処理 ■■■
'***************************************************************************************************
'* 処理名　：READ_TextFile2
'* 機能　　：テキストファイル読み込みサンプル(FSO版)
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2003年07月25日
'* 作成者　：井上　治
'* 更新日　：2020年02月26日
'* 更新者　：井上　治
'* 機能説明：
'* 注意事項：サンプルなのでエラー処理は行なっていません
'***************************************************************************************************
Sub READ_TextFile2()
    '-----------------------------------------------------------------------------------------------
    Dim objFso As FileSystemObject                              ' FileSystemObject
    Dim objTs As TextStream                                     ' TextStream
    Dim lngRow As Long                                          ' 収容するセルの行
    Dim lngRec As Long                                          ' レコード件数カウンタ
    Dim strFileName As String                                   ' OPENするファイル名(フルパス)
    Dim strRec As String                                        ' 読み込んだレコード内容
    Dim vntFileName As Variant                                  ' ファイル名受取用
    '-----------------------------------------------------------------
    ' ①｢ファイルを開く｣のダイアログでファイル名の指定を受ける
    Application.StatusBar = "読み込むファイル名を指定して下さい。"
    vntFileName = Application.GetOpenFilename(FileFilter:=g_cnsFilter, Title:=g_cnsTitle)
    ' キャンセルされた場合はFalseが返るので以降の処理は行なわない
    If VarType(vntFileName) = vbBoolean Then Exit Sub
    strFileName = vntFileName
    '-----------------------------------------------------------------
    ' ②FileSystemObject
    Set objFso = New FileSystemObject
    ' 指定ファイルをOPEN(入力モード)
    Set objTs = objFso.OpenTextFile(Filename:=strFileName, IOMode:=ForReading)
    Set objFso = Nothing
    ' 先頭行の設定(2行目から掻き出すので1とする)
    lngRow = 1
    '-----------------------------------------------------------------
    ' ③ファイルのEOFまで繰り返す
    Do Until objTs.AtEndOfStream
        ' レコード件数カウンタの加算
        lngRec = lngRec + 1
        Application.StatusBar = "読み込み中です．．．．(" & lngRec & "レコード目)"
        ' 改行までをレコードとして読み込む
        strRec = objTs.ReadLine
        ' 行を加算しA列にレコード内容を表示(先頭は2行目)
        lngRow = lngRow + 1
        Cells(lngRow, 1).Value = strRec
    Loop
    '-----------------------------------------------------------------
    ' ④指定ファイルをCLOSE
    objTs.Close
    Set objTs = Nothing
    Application.StatusBar = False
    ' 終了の表示
    MsgBox "ファイル読み込みが完了しました。" & vbCr & _
        "レコード件数=" & lngRec & "件", vbInformation, g_cnsTitle
End Sub

'----------------------------------------<< End of Source >>----------------------------------------

ソースコードの線で囲って丸数字をコメントで書き込んだ4つのブロックについて概略を説明します。

№	処理工程概要
①	開くファイル名の受け取り部分です。上の「古くからあるステートメント」と全く同じです。 ※この記述方法では「Microsoft Scripting Runtime」の参照設定が必要です。 VBEの方の「ツール」メニューから「参照設定」を選んで、「Microsoft Scripting Runtime」にチェックをつけてOKをクリックして下さい。
②	[前処理]として、指定のテキストファイルを開く処理です。 FileSystemObjectの「OpenTextFileメソッド」により、TextStreamを受け取ります。この記述では文字コードの指定を行なっていないのでAscii(シフトJIS漢字)コード形式となります。 Unicode形式の場合は、引数に「Format:=TristateTrue」を追加して下さい。 FileSystemObjectの方は以降は参照しないので、すぐに解放しています。
③	[主処理]として、実際にファイルをEOF(End of File)まで読み込むループです。ループ内では「ReadLineメソッド」で次に発生するCRLFコードまでを1行として読み込みます。読み込んだ内容はこのサンプルでは変数「strRec」に格納されるので、行を加算してA列のセルに転記しています。
④	[後処理]として、ファイルをCLOSEして、終了メッセージを表示しています。

このような単純な動作ではOpen,Inputステートメントとの優劣は見つかりにくいですが、フォルダ名を含めたフルパスのファイル名の総長の制限などではFSO(FileSystemObject)の方が有利なので、これからマスターされる方はこちらの方法を理解するようにして下さい。