大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAを使って指定したフォルダ内のすべてのテキストデータを探索して任意のデータを取得する方法をご紹介します。

はじめに

この記事でご紹介したい内容はこちらです。

ご紹介したいこと

  • 指定したフォルダ内のすべてのテキストファイルのフルパスをExcel VBAで取得する方法
  • 取得したすべてのテキストファイルから任意のデータをExcel VBAで取得する方法

本記事の内容を動画でまとめています

この記事で紹介するExcel VBAのコードを実行するとこんなことができます。Excel VBAの実行結果をパッと見たい方はどうぞご覧ください。

目次から見たい項目へ移動すると便利ですよ。

目次

実行結果

実行した結果から先にご紹介します。

まずは、取得したいテキストファイルについてです。

取得したいテキストファイル

取得したいテキストファイル

フォルダの中に複数のテキストファイルが保存されています。

それぞれのテキストファイルには見出しとデータが1行分入っています。

テキストファイルの中身

Excel VBAを実行

今回はすべてのテキストファイルから2行目のデータを取得します。

ではコードを実行してみます。

Excel VBA実行します

取得したいテキストファイルを選択

VBAコードを実行するとフォルダ取得用のダイアログが表示されますので、テキストファイルが保存されているフォルダを選択します。

取得したいテキストファイルを選択

VBAコードの実行結果です。

Excel VBA実行結果

テキストデータの取得結果

今回は10個のテキストファイルを用意しましたので10個分のデータが取得できています。

ちなみにテキストデータの取得はかなり高速です。10個のデータなのでちょっと参考にはならないかもしれませんが、0.1015625秒で取得できます(^^)。

Excel VBAコードご紹介

ご紹介といえるほどでもないですが(^_^)、すべてのテキストファイルから任意のデータを取得するVBAコードです。

こちらのコードは標準モジュールへ記載します。

Excel VBAコード全体

'指定フォルダの全テキストの任意行を取得
Sub GetAllTextData()
    
    'フォルダ指定用のダイアログを表示します
    With Application.FileDialog(msoFileDialogFolderPicker)
        
        'カレントディレクトリを指定します
        .InitialFileName = ThisWorkbook.Path
        
        '設定しなかったら終了します
        If .Show = False Then Exit Sub
            
        '設定したフォルダを表示します
        Dim Fname
        Fname = .SelectedItems(1)
        
    End With
    
    '参照設定
    Dim FSO As Object, Folder As Variant, File As Variant
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Dim FilePath As Variant
    ReDim FilePath(1 To 100) As Variant
    
    '指定フォルダ内の.txtファイルを探索します
    i = 0
    For Each File In FSO.GetFolder(Fname).Files
        If InStr(File.Name, ".txt") > 0 Then
            i = i + 1
            FilePath(i) = File.Path 'ファイルのフルパスを取得
        End If
    Next
    
    '配列の大きさは状況に応じ変更してください
    Dim Hozon, GetData As Variant
    ReDim GetData(1 To 100, 1 To 100) As Variant
    
    '全テキストファイルの任意行のデータを取得する
    m = 0
    For k = 1 To UBound(FilePath, 1)
        
        'テキストファイルが存在する場合に実行
        If IsEmpty(FilePath(k)) = False Then
            
            '保存する配列を空にする
            ReDim Hozon(1 To 100, 1 To 100) As Variant
            
            'テキストを開いて配列にデータを保存
            Open FilePath(k) For Input As #1
                i = 0
                'テキストをすべて取得する
                Do Until EOF(1)
                    Line Input #1, buf
                    i = i + 1
                    'コンマ区切りでデータを取得する
                    a = Split(buf, ",")
                    For j = 0 To UBound(a, 1)
                        Hozon(i, j + 1) = a(j)
                    Next
                Loop
            Close #1
            
            '▼取得したいデータに応じ変更してください
            '任意行の値を取得する
            i = 2 '2行目のデータを取得
            m = m + 1
            For j = 1 To UBound(Hozon, 2)
                GetData(m, j) = Hozon(i, j)
            Next
            
        End If
    Next
    
    'データ貼り付け
    With ActiveSheet
        .Range(.Cells(2, 1), .Cells(2, 1).Offset(UBound(GetData, 1) - 1, UBound(GetData, 2) - 1)) = GetData
    End With
    
End Sub

すこし長いですがすみません、私にはこれが限界です (;^_^)

ひとつずつ説明していってみます。

最初はフォルダを指定するためのダイアログ表示のコードです。

フォルダ指定用のダイアログを表示するVBAコード

'フォルダ指定用のダイアログを表示します
With Application.FileDialog(msoFileDialogFolderPicker)
    
    'カレントディレクトリを指定します
    .InitialFileName = ThisWorkbook.Path
    
    '設定しなかったら終了します
    If .Show = False Then Exit Sub
        
    '設定したフォルダを表示します
    Dim Fname
    Fname = .SelectedItems(1)
    
End With

Application.FileDialog(msoFileDialogFolderPicker)を使用してダイアログを表示します。

.InitialFileName = ThisWorkbook.Pathのコードはダイアログで最初表示するフォルダをこのエクセルファイルが保存されているフォルダとします。

デスクトップとかが最初の表示だと選択するのが大変ですからね(^^)

フォルダを選択したら.SelectedItems(1)にフォルダのフルパスが保存されますので変数Fnameへ保存します。

このフルパスといっているのは、『C:〇〇\〇〇\フォルダ名』の絶対パスを指しています。

これでフォルダのフルパス取得ができます。

つづいて、指定したフォルダ内のすべてのテキストファイルを取得するVBAコードです。

すべてのテキストファイルのフルパスを取得するVBAコード

'参照設定
Dim FSO As Object, Folder As Variant, File As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")

Dim FilePath As Variant
ReDim FilePath(1 To 100) As Variant

'指定フォルダ内の.txtファイルを探索します
i = 0
For Each File In FSO.GetFolder(Fname).Files
    If InStr(File.Name, ".txt") > 0 Then
        i = i + 1
        FilePath(i) = File.Path 'ファイルのフルパスを取得
    End If
Next

Set FSO = CreateObject("Scripting.FileSystemObject")は参照設定でVBAでフォルダやファイルの操作をする際に使用します。

ReDim FilePath(1 To 100) As Variantはテキストファイルのフルパスを保存する配列の大きさを設定しています。

とりあえず100行としていますが、必要に応じ変更してみてください(^^)

For Each File In FSO.GetFolder(Fname).Filesのコードでフォルダ内のすべてのファイルを見ていきます。

今回はテキストファイルのみを取得したいので、If InStr(File.Name, ".txt") > 0 Thenと条件を付けています。

見つけたテキストファイルのフルパスをFilePath(i) = File.Pathで保存していきます。

これでFilePathへすべてのテキストファイルのフルパスを取得できます。

つづいてテキストファイル内の全データを取得するVBAコードです。

テキストのファイルの全データを取得するVBAコード

'テキストを開いて配列にデータを保存
Open FilePath(k) For Input As #1
    i = 0
    'テキストをすべて取得する
    Do Until EOF(1)
        Line Input #1, buf
        i = i + 1
        'コンマ区切りでデータを取得する
        a = Split(buf, ",")
        For j = 0 To UBound(a, 1)
            Hozon(i, j + 1) = a(j)
        Next
    Loop
Close #1

Open FilePath(k) For Input As #1でテキストファイルを開きます。

Do Until EOF(1)でテキストファイルの最終行までループします。

Line Input #1, bufでテキストファイルを1行分のデータをbufの変数へ保存します。

a = Split(buf, ",")でコンマ区切りで分けたデータを配列aへ保存します。

Hozon(i, j + 1) = a(j)で配列Hozonへ保存し直します。

UBound(a, 1)は配列の最終行を取得するコードです。UBound(a, 1)で最終行、UBound(a, 2)にすると最終列を取得できます。

これで、配列Hozonへテキストファイルの全データが保存できました。

次のコードが取得したい任意のデータを取得するコードになります。

テキストデータから任意のデータを取得するVBAコード

'▼取得したいデータに応じ変更してください
'配列の大きさは状況に応じ変更してください
Dim Hozon, GetData As Variant
ReDim GetData(1 To 100, 1 To 100) As Variant
'任意行の値を取得する
i = 2 '2行目のデータを取得
m = m + 1
For j = 1 To UBound(Hozon, 2)
    GetData(m, j) = Hozon(i, j)
Next

GetDataは取得したい全データを保存する配列になります。

ReDim GetData(1 To 100, 1 To 100) As Variantでとりあえず簡単に配列GetDataの大きさを100行100列としています(;^_^)

必要に応じで大きさ変えてみてください。

ReDimは説明のためこちらに記載していますが、コード自体は次に説明するコードFor k = 1 To UBound(FilePath, 1)の前に記載する必要があります。

今回はテキストファイルの2行目を取得したいのでi = 2としています。

GetData(m, j) = Hozon(i, j)は、前のコードで取得した配列Hozonから取得したい2行目だけを抜き出して保存するコードです。

For j = 1 To UBound(Hozon, 2)で配列Hozonの列数分ループして全列分のデータを取得します。

『テキストのファイルの全データを取得するVBAコード』と『テキストデータから任意のデータを取得するVBAコード』をテキストファイルの数だけループします。

それが次のコードです。

すべてのテキストファイル分ループするVBAコード

'全テキストファイルの任意行のデータを取得する
For k = 1 To UBound(FilePath, 1)
    
  テキストのファイルの全データを取得するVBAコード

  テキストデータから任意のデータを取得するVBAコード

Next

『テキストデータから任意のデータを取得するVBAコード』に記載したGetData(m, j)の部分に取得したい全データが保存されます。

変数mがテキストのファイル数で変数jがテキストデータの列数になります。

最後は、エクセルのシートへ取得したデータを貼り付けて終了です。

エクセルのシートへ取得したデータを貼り付けるVBAコード

'データ貼り付け
With ActiveSheet
    .Range(.Cells(2, 1), .Cells(2, 1).Offset(UBound(GetData, 1) - 1, UBound(GetData, 2) - 1)) = GetData
End With

貼付けする範囲は.Offseteを使って配列GetDataの行数分:UBound(GetData, 1) - 1と列数分:UBound(GetData, 2) - 1)を指定しています。

-1しているのは.Offsetの始まりが.Offset(0, 0)で0から始まるので1行1列分ずらしているからです。

おわりに

だいぶ長い説明となってしまいましたが(;^_^)、テキストデータの取得は今回紹介したコードを使えば十分できるようになります。

また、『テキストデータから任意のデータを取得するVBAコード』のコードを変更すれば取得したいテキストデータを自由に取得することができます。

例えば、商品名がレモンのデータをすべて取得だったり、価格が1000~2000円のデータをすべて取得といったこともできます。臨機応変に使えますね(^^)。

最後までご覧くださいましてありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す