Access+VBAでファイル一覧を取得する

2022年7月26日 07:54

今回は趣向を変えてAccessを使ったプログラムを書いてみます。
内容としては「フォルダ（サブフォルダ含む）を検索し、ファイル名・フルパスをテーブルに格納する」といったものです。ちょっと仕事の関係で必要になったのですが、作り方を忘れると後々面倒なので備忘録として残します。

1.下準備

まずはAccessで.accdbファイルを作成。作成したファイル内に以下のオブジェクトを作成します。

テーブル
FileInfo

クエリ
Q_NoEx

マクロ
Main

モジュール
FileSelector

それぞれの説明を簡単に。

・テーブル（FileInfo）
検索結果を格納するテーブル。「FullPath・FileName・NoExtension」の3つのフィールドからなり、データ型はすべて「短いテキスト」。主キーなしという冒涜的な構造。
※たぶんFullPathを主キーにして問題ないとは思うけど、まぁ良いか。

・クエリ（Q_NoEx）
ファイル拡張子を外したファイル名を取得するために使う。内容は後述。残念ながら特定のファイル名にはちゃんと動かない。

・マクロ（Main）
処理開始用のマクロ。後述するモジュールの関数を呼び出すためだけのもの。「プロシージャの実行」しか書かれていない。

・モジュール（FileSelector）
今回のメイン。ここでファイル検索を行う。

そしてコーディングを行う前にVBAの参照先を追加。VBAエディタを開き「ツール」から「参照設定」をクリック。Microsoft Scripting Runtime にチェックを入れます。

2.コーディング

ではモジュールをコーディングしていきます。
今回の全体像は「FileSelector」というファイル検索用の関数を作り、それを「CallFileSelector」という呼び出し用の関数を作って実行するというものです。
まずはFileSelector関数から。

Sub FileSelector(p)
    On Error GoTo ERR_MSG
    DoCmd.SetWarnings False

    Dim path        As String
    Dim obj         As FileSystemObject
    Dim foldor      As Folder
    Dim subfolder   As Folder
    Dim file        As file
    Dim fillparh    As String
    
    path = p
    
    'ファイルシステムオブジェクトを作成。引数で受け取ったパスを格納。
    Set obj = CreateObject("Scripting.FileSystemObject")
    Set foldor = obj.GetFolder(path)
    
    '起点フォルダ内のファイルについて処理
    For Each file In foldor.Files
        FullPath = path & "\" & file.Name
        'SQLでインサート
        DoCmd.RunSQL "INSERT INTO FileInfo ( FullPath, FileName ) VALUES('" & FullPath & "', '" & file.Name & "');"
    Next
    

    'フォルダ内のサブフォルダを探索。
    For Each subfoldor In foldor.SubFolders
        'サブフォルダも検査するため、自身を再帰的に呼び出す
        FileSelector subfoldor.path
    Next
    
    'オブジェクトクリア
    Set obj = Nothing
    Exit Sub

ERR_MSG:
    MsgBox ("エラーが発生しました。")

End Sub

部分ごとに見ていきます。

    Dim path        As String
    Dim obj         As FileSystemObject
    Dim foldor      As Folder
    Dim subfolder   As Folder
    Dim file        As file
    Dim fillparh    As String

▲頭の方でDimを使って変数を定義しています。FileSystemObjectとか普段はあまり見ない定義が出てきました。

path = p

▲この関数は引数として検索の起点となるフォルダを受け取るので、それをpathに格納。

    'ファイルシステムオブジェクトを作成。引数で受け取ったパスを格納。
    Set obj = CreateObject("Scripting.FileSystemObject")
    Set foldor = obj.GetFolder(path)

▲objにファイルシステムオブジェクトを作成。これでobjを介してファイルシステムを操作できるようになりました。obj.GetFolder(path)で起点となるフォルダを取得。foldorに格納します。

 '起点フォルダ内のファイルについて処理
    For Each file In foldor.Files
        FullPath = path & "\" & file.Name
        'SQLでインサート
        DoCmd.RunSQL "INSERT INTO FileInfo ( FullPath, FileName ) VALUES('" & FullPath & "', '" & file.Name & "');"
    Next

▲サブフォルダを見に行く前にまずは起点フォルダを検索します。foldor.Filesで起点フォルダ内のファイルを取得し、それを繰り返し処理でテーブルに格納します。

    'フォルダ内のサブフォルダを探索。
    For Each subfoldor In foldor.SubFolders
        'サブフォルダも検査するため、自身を再帰的に呼び出す
        FileSelector subfoldor.path
    Next

▲ここからがちょっとややこしい。For-Nextを使ってサブフォルダをぐるぐると探索していきます。foldor.SubFoldersで起点フォルダのサブフォルダを取得。取得したサブフォルダを引数として自身（FileSelector関数）を呼び出します。すると先ほど書いたファイル名取得の部分が実行され、またサブフォルダの探索が行われ….といった具合に入れ子構造になって処理が進んでいきます。
この辺りを変に作ってしまうと無限ループができて処理が終わらなくなってしまいます。

    'オブジェクトクリア
    Set obj = Nothing
    Exit Sub

ERR_MSG:
    MsgBox ("エラーが発生しました。")

End Sub

▲最後にファイルオブジェクトのクリアとエラー時にメッセージボックスを表示する部分を作って終わり。

続いて呼び出し用のCallFileSelector関数です。こっちは簡単。

Function CallFileSelector()
    On Error GoTo ERR_Log01
    DoCmd.SetWarnings False
    '探索起点となるフォルダ名を設定
    FileSelector "C:\python"
    DoCmd.OpenQuery ("Q_NoEx")
    Exit Function
    
ERR_Log01:
    MsgBox ("エラーが発生しました。")
    
    
End Function

▲起点となるフォルダを引数にFileSelectorを呼び出します。今回は遊んでいるpythonファイルが置いてある"C:\python"を設定しました。FileSelector関数の処理が終わったら拡張子を省くクエリのQ_NoExを呼び出して終わり。

3.クエリ作成（不完全）

さて上記のVBAで取得したファイル名から拡張子を省くクエリを作ります。…がこれうまくいってません。理由は後述するとしてとりあえずクエリを。

NoExtensionのフィールドに拡張子を省いた形でファイル名を格納します。
更新値はこんな感じ

Left([FileName],InStr([FileName],".")-1)

ファイル名の「.」から左側を取ってくるというものです。これでいけるかと思いきや、「ファイル名に . が含まれている」場合や「そもそも拡張子がついていないファイル」はうまく回りません。まぁこれはおまけのような部分なので今回は目をつぶります。

4.マクロ作成、実行

作っておいたMainマクロに処理を書きます。

指定するのは呼び出し用のCallFileSelector関数の方です。

あとはこのマクロをダブルクリックすれば処理開始。

実行してみました。

良いんじゃないでしょうか。拡張子の省略はちょっと微妙ですがとりあえず最低限のやりたいことはできている気がします。

今回はここまで。
Accessを使ってのファイル操作はやったことがなかったので良い勉強になりました。

読んでいただきありがとうございました。

ここまで読んでいただきありがとうございます！