【ExcelVBA】マクロ・コマンドプロンプト・SQL Serverのストアドプロシージャを使ってパソコン内のフォルダとファイルを検索する

この記事では、マクロ・コマンドプロンプト・SQL Serverのストアドプロシージャを使ってパソコン内のフォルダとファイルを検索する方法についてご説明します。

検索した結果は、シート上に設置されたListboxに表示します。

【動画】マクロ・コマンドプロンプト・SQL Serverのストアドプロシージャを使ってパソコン内のフォルダとファイルを検索する実際の動き

本題に入る前に、まずは次の動画をご覧ください。



指定したフォルダからファイルとフォルダのフルパスをCSVに出力してストアドプロシージャでSQL Serverのテーブルに追加。

追加したファイルとフォルダをマクロが取得してListboxに表示しています。

各名称について

今回の説明で使用するSQL Serverのデータベースやテーブル、ストアドプロシージャなどの名称は次の通りです。

  1. データベース:projDB
  2. テーブル:tbl_folderfile_list
  3. ストアドプロシージャ:sp_blkins_fdrfile_list

【参考】テーブルtbl_folderfile_listの定義

CREATE TABLE [dbo].[tbl_folderfile_list] (
    [infoNo]  INT            NOT NULL,
    [dirPath] NVARCHAR (MAX) NULL,
    [fName]   NVARCHAR (MAX) NULL
);

マクロ・コマンドプロンプト・SQL Serverのストアドプロシージャを使ってパソコン内のフォルダとファイルを検索する処理の流れ

本処理はマクロ・コマンドプロンプト・SQL Serverのストアドプロシージャを使っているので、処理の流れをここで整理します。

マクロ・コマンドプロンプト・SQL Serverのストアドプロシージャを使ってパソコン内のフォルダとファイルを検索する処理の流れは次の通りです。

処理の流れ

STEP.1
【マクロ】マクロがコマンドプロンプトを呼び出す
マクロがコマンドプロンプトを呼び出します。
STEP.2
【コマンドプロンプト】コマンドプロンプトでdirコマンドを実行してフォルダ・ファイルのパスの一覧を取得する
コマンドプロンプトでdirコマンドを実行してフォルダ・ファイルのパスの一覧を取得します。
取得したフォルダ・ファイル一覧はCSVに出力します。
STEP.3
【マクロ】マクロがストアドプロシージャを呼び出す
マクロがストアドプロシージャを呼び出します。
STEP.4
【ストアドプロシージャ】呼び出されたストアドプロシージャでSTEP.2で取得したフォルダ・ファイル一覧をテーブルに追加する
呼び出されたストアドプロシージャでSTEP.2で取得したフォルダ・ファイル一覧をテーブルに追加します。
STEP.5
【マクロ】STEP.4でSQL Serverのテーブルに追加されたフォルダ・ファイル一覧から検索したフォルダとファイルを取得する
STEP.4でSQL Serverのテーブルに追加されたフォルダ・ファイル一覧から検索したフォルダとファイルを取得します。
STEP.6
【マクロ】STEP.5で取得したフォルダ名ファイル名をListboxに表示させる
STEP.5で取得したフォルダ名ファイル名をListboxに表示させます。

マクロを実行するExcelファイル(例)

マクロを実行するExcelのシート(例)についても説明します。

今回使ったExcelのシート(例)は次の通りです。

配置されているコントロールやセルの名前は次の通りです。

searchpath

フォルダやファイルを探す対象のフォルダパスを指定するセルに、「searchpath」という名前を付けます。

例えば、Cドライブ配下全てでフォルダやファイルを検索する場合は「c:\」または「c:」と入力します。

searchStr

探したいフォルダ名やファイル名、もしくは探したいフォルダやファイルの文字列の一部を入力するセルに「searchStr」という名前を付けます。

このセルに探したいフォルダ名やファイル名、もしくは探したいフォルダやファイルの文字列の一部を入力して検索します。

例えば、「work」というフォルダ名や「test.txt」というファイル名を探す場合は、このセルに「work」または「test.txt」と入力し、検索すると検索結果が表示されます。

また、探したいフォルダやファイルの文字列の一部だけで検索する場合、例えば「temp」という文字列だけで探す場合は「temp」と入力します。(※部分一致や前方・後方一致などを選んで検索した場合)

文字列の一部だけで検索した場合は文字列を含むフォルダやファイルが検索結果に表示されます。

opb_allmatch(フォームコントロール)

フォルダやファイルを「完全一致」で検索するよう設定するオプションボタンです。

フォルダ名やファイル名と完全に一致するフォルダやファイルを検索します。

完全に一致しないと検索がヒットしません。

opb_partmatch(フォームコントロール)

フォルダやファイルを「部分一致」で検索するよう設定するオプションボタンです。

フォルダ名やファイル名の一部の文字列だけでフォルダやファイルを検索します。

opb_fwdmatch(フォームコントロール)

フォルダやファイルを「前方一致」で検索するよう設定するオプションボタンです。

フォルダ名やファイル名の前の文字から順に条件に一致するフォルダやファイルを検索します。

opb_rearmatch(フォームコントロール)

フォルダやファイルを「後方一致」で検索するよう設定するオプションボタンです。

フォルダ名やファイル名の後ろの文字から順に条件に一致するフォルダやファイルを検索します。

listbox1(ActiveXコントロール)

listboxで検索結果を表示させます。

Excelのマクロの作成の流れ

STEP.1
コマンドプロンプトを呼び出してサブフォルダ含めて全てのフォルダ名とファイル名を取得したいパスをCSVファイルに書き出す
コマンドプロンプトを呼び出してサブフォルダ含めて全てのフォルダ名とファイル名を取得したいパスをCSVファイルに書き出します。
STEP.2
パスとフォルダ名・ファイル名を列挙するためのシートを新規作成
パスとフォルダ名・ファイル名を列挙するためのシートを新規作成します。
すでにシートが存在する場合は何もしません。
STEP.3
STEP.1のCSVファイルをBULK INSERTを実行するストアドプロシージャの引数に渡す
STEP.1のCSVファイルをBULK INSERTを実行するストアドプロシージャの引数に渡します。
STEP.4
BULK INSERTを呼び出してSTEP.1のCSVファイルのフォルダ・ファイルのパス一覧をSQL Serverのテーブルに追加する
BULK INSERTを呼び出してSTEP.1のCSVファイルのフォルダ・ファイルのパス一覧をSQL Serverのテーブルに追加します。
STEP.5
探しているフォルダ名とファイル名をSQL Serverのテーブルから検索する
探しているフォルダ名とファイル名をSQL Serverのテーブルから検索します。
STEP.6
検索結果をListboxに表示させる
検索結果をListboxに表示させます。

ストアドプロシージャの作成の流れ

STEP.1
CSVファイルからパスとフォルダ名・ファイル名を追加するテーブルのデータを削除する
CSVファイルからパスとフォルダ名・ファイル名を追加するテーブルのデータを削除します。
STEP.2
CSVファイルをそのままの値で保存しておく一時テーブルを作成する
CSVファイルをそのままの値で保存しておく一時テーブルを作成します。
STEP.3
CSVファイルのデータをSTEP.2で作成した一時テーブルに追加する
CSVファイルのデータをSTEP.2で作成した一時テーブルに追加します。
STEP.4
一時テーブルのデータを、パスとファイル名・ファイル名の格納用テーブル(STEP.1のテーブル)に追加する
一時テーブルのデータを、パスとファイル名・ファイル名の格納用テーブル(STEP.1のテーブル)に追加します。
STEP.5
一時テーブルを削除する
一時テーブルを削除します。

コードの例

Excelのマクロのコード(例)

Option Explicit

Private Sub btn_getDirFileList_Click()

    Dim getPath             As String               'サブフォルダ含めて全てのフォルダ名とファイル名を取得したいパス
    Dim cmdTxt              As String               'コマンドプロンプトのコード用変数
    Dim cnt                 As Long                 'カウンタ(作業用)
    Dim ws                  As Worksheet            'Worksheet用変数
    Dim shtExistFlg         As Boolean              'シート存在確認フラグ
    Dim fldrFileNMExptTxt   As String               'フォルダ名とファイル名が出力されたファイル名
    Dim wshObj              As WshShell             'WshShellオブジェクト
    Dim fso                 As FileSystemObject     'FileSystemObjectのインスタンス用変数
    Dim connDB              As String               'データベース接続情報
    Dim objConnection       As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim objCommand          As ADODB.Command        'ADODB.Commandオブジェクトのインスタンス用変数
    Dim objParameter        As ADODB.Parameter      'パラメタオブジェクト用変数
    Dim oRS                 As ADODB.Recordset      'レコードセット用変数
    Dim sqlStr              As String               'SQL文
    Dim prmValAry()         As Variant              'ストアドプロシージャに渡すパラメタ用配列
    
    Const rowMaxCnt         As Long = 1048575                           'Excelの最大行数
    Const sheetNM           As String = "data"                          'SQL Serverのテーブルから取得したデータを貼り付けるシート名
    Const DBName            As String = "projDB"                        'データベース名
    Const spName            As String = "dbo.sp_blkins_fdrfile_list"    'ストアドプロシージャ名
    Const sp_prmBkIsFNM     As String = "insertDataFilePath"            'BULKINSERT用ファイル名を受け取るストアドプロシージャ側の変数名
    Const sp_prmTblName As String = "testDataDB"                        'データベース名を受け取るストアドプロシージャ側の変数名
    Const tblName As String = "tbl_folderfile_list"                     'インポート先のテーブル名の取得
    
    'シート存在確認フラグにfalse(存在しない)を設定する
    shtExistFlg = False
        
    'フォルダ名とファイル名を出力させるCSVファイル名を取得する
    fldrFileNMExptTxt = ActiveWorkbook.Path & "\" & "data.csv"
    
    '項目名とストアドの引数に渡したい値を配列prmValAryの格納する
    prmValAry = _
         Array( _
                Array(sp_prmBkIsFNM, _
                      sp_prmTblName), _
                Array(fldrFileNMExptTxt, _
                      tblName) _
              )
    
    'カウンタの初期化を設定
    cnt = 1
        
    'サブフォルダ含めて全てのフォルダ名・ファイル名を取得したいパス
    getPath = Sheets("top").Range("searchpath").Value
        
    'FileSystemObjectのインスタンスを生成する
    Set fso = New FileSystemObject
    
    'WshShellオブジェクトからインスタンスを生成する
    Set wshObj = New WshShell
            
    If Right(getPath, 1) <> "\" Then
    
        '入力されたパスの末尾に「\」が付いていない場合に付ける
        getPath = getPath & "\"
        
    End If
    
    '実行するコマンド:dirコマンドでフルパスをCSVファイルに書き出す
    cmdTxt = "chcp 65001 | dir /b /s " & """" & getPath & """" & " > " & fldrFileNMExptTxt
    
    'コマンドプロンプトで変数「cmdTxt」のコマンドを実行する
    Call wshObj.Run("%ComSpec% /c " & cmdTxt, 1, WaitOnReturn:=True)
 
    'データベース接続情報を取得
    connDB = "Provider=SQLNCLI11.1;"
    connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
    connDB = connDB & "Initial Catalog=" & DBName & ";"
    connDB = connDB & "Trusted_Connection=yes;"
 
    'DBへの接続
    Set objConnection = New ADODB.Connection
    objConnection.CursorLocation = adUseClient
    objConnection.Open connDB
 
    '実行するストアドプロシージャを設定
    Set objCommand = New ADODB.Command
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandType = adCmdStoredProc
    objCommand.CommandText = spName         'ストアドプロシージャ名
    objCommand.CommandTimeout = 300         'タイムアウト時間を設定する(ここでは300秒にしています)
     
    'ストアドプロシージャの引数に渡す値の設定(渡したい引数の数だけ繰り返す)
    For cnt = 0 To UBound(prmValAry(0), 1)
 
        'パラメタオブジェクトの作成
        Set objParameter = objCommand.CreateParameter()
 
        '引数の名前
        objParameter.Name = prmValAry(0)(cnt)
 
        'データ型の指定(ここでは文字列を指定)
        objParameter.Type = adWChar
 
        '文字列のサイズの指定
        objParameter.Size = Len(prmValAry(1)(cnt))
 
        'ストアドプロシージャの引数に渡す値
        objParameter.Value = prmValAry(1)(cnt)
 
        '入力パラメータ
        objParameter.Direction = adParamInput
 
        'ストアドプロシージャの引数に渡す値を設定する
        objCommand.Parameters.Append objParameter
 
    Next
    
    'ストアドプロシージャの呼び出し
    objCommand.Execute
 
    'Recordsetオブジェクトのインスタンスを生成する
    Set oRS = New ADODB.Recordset
 
    '先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、
    'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
    '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
    oRS.ActiveConnection = objConnection
    
    'カーソルタイプにキーセットカーソル使用
    oRS.CursorType = adOpenStatic
    
    'テーブルデータを取得するSQL文を用意する
    sqlStr = "select"
    sqlStr = sqlStr & "  ROW_NUMBER() OVER(ORDER BY dirPath, fName ASC) rnum"
    sqlStr = sqlStr & ", dirPath"
    sqlStr = sqlStr & ", fName"
    sqlStr = sqlStr & "  from " & tblName

    If Sheets("top").OptionButtons("opb_allmatch").Value = 1 Then

        '完全一致の場合
        
        '条件(完全一致で取得)
        sqlStr = sqlStr & "  where fName = N'" & Range("searchStr").Value & "'"

    ElseIf Sheets("top").OptionButtons("opb_partmatch").Value = 1 Then

        '部分一致の場合

        '条件(部分一致で取得)
        sqlStr = sqlStr & "  where fName like N'%" & Range("searchStr").Value & "%'"
                
    ElseIf Sheets("top").OptionButtons("opb_fwdmatch").Value = 1 Then

        '前方一致の場合

        '条件(前方一致で取得)
        sqlStr = sqlStr & "  where fName like N'" & Range("searchStr").Value & "%'"

    ElseIf Sheets("top").OptionButtons("opb_rearmatch").Value = 1 Then

        '後方一致の場合

        '条件(後方一致で取得)
        sqlStr = sqlStr & "  where fName like N'%" & Range("searchStr").Value & "'"

    End If
    
    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
 
    '指定したテーブルのデータを参照する(アクセスする)
    oRS.Open
    
    If oRS.RecordCount = 0 Then
    
        '検索データが存在しない場合
    
        With Worksheets(sheetNM)
            
            'シート「data」のセル全てをクリアする
            .Cells.Clear
        
            'listboxのヘッダに表示させる項目名をセルに設定する
            .Range("A1").Value = "項番"
            .Range("B1").Value = "パス"
            .Range("C1").Value = "フォルダ名/ファイル名"
        
            'Listboxの表示(ListFillRangeプロパティ)をクリアする
            ListBox1.ListFillRange = sheetNM & "!$A$2:$C$2"
            
        End With
    
        MsgBox "検索データがありません。"
        
        '処理を終了する
        End
    
    ElseIf oRS.RecordCount > rowMaxCnt Then
    
        '検索データの件数がExcelの最大行数を超えている場合
    
        With Worksheets(sheetNM)
            
            'シート「data」のセル全てをクリアする
            .Cells.Clear
        
            'listboxのヘッダに表示させる項目名をセルに設定する
            .Range("A1").Value = "項番"
            .Range("B1").Value = "パス"
            .Range("C1").Value = "フォルダ名/ファイル名"
        
            'Listboxの表示(ListFillRangeプロパティ)をクリアする
            ListBox1.ListFillRange = sheetNM & "!$A$2:$C$2"
            
        End With
    
        MsgBox "検索データの件数がExcelの最大行数を超えているので処理を終了します。"
        
        '処理を終了する
        End
    
    End If
        
    'フォルダ名とファイル名貼り付け用シート有無のチェック
    For Each ws In Worksheets
    
        If ws.Name = sheetNM Then
        
            'シート「data」が存在している場合
            
            '変数「shtExistFlg」にTrueを設定する
            shtExistFlg = True
        
        End If
        
    Next ws
    
    If shtExistFlg = False Then
    
        'シート「data」が存在しない場合
        
        'テーブルから取得するデータを列挙するためのシートを新規作成する
        Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
                       .Name = sheetNM
                       
    Else
    
        'シート「data」が存在する場合
    
        'シート「data」のセル全てをクリアする
        Worksheets(sheetNM).Cells.Clear
                   
    End If
    
    'listboxのヘッダに表示させる項目名をセルに設定する
    Worksheets(sheetNM).Range("A1").Value = "項番"
    Worksheets(sheetNM).Range("B1").Value = "パス"
    Worksheets(sheetNM).Range("C1").Value = "フォルダ名/ファイル名"
    
    'テーブルデータをシートに貼り付ける
    Worksheets(sheetNM).Range("A2").CopyFromRecordset oRS
        
    'シート「top」に表示を切り替える
    Sheets("top").Select
    
    With ListBox1
    
        'Listboxの表示するデータのセル範囲を指定する
        .ListFillRange = sheetNM & "!$A$2:$C$" & oRS.RecordCount + 1
        
        'ヘッダを表示させる
        .ColumnHeads = True
        
        'Listboxの列数を設定する
        .ColumnCount = 3
        
        'Listboxの列の幅を設定する
        .ColumnWidths = "50;260;800"
            
    End With
    
    '後処理
    
    'フォルダ名とファイル名が出力されたファイルを削除する
    Call fso.DeleteFile(fldrFileNMExptTxt, True)
        
    oRS.Close
    objConnection.Close
    
    Set objParameter = Nothing
    Set objCommand = Nothing
    Set objConnection = Nothing
    Set fso = Nothing
            
    MsgBox "完了"

End Sub

コードの解説

注目すべきコード①

最初に見て頂きたいのは64行目から67行目です。

    '実行するコマンド:dirコマンドでフルパスをCSVファイルに書き出す
    cmdTxt = "chcp 65001 | dir /b /s " & """" & getPath & """" & " > " & fldrFileNMExptTxt
    
    'コマンドプロンプトで変数「cmdTxt」のコマンドを実行する
    Call wshObj.Run("%ComSpec% /c " & cmdTxt, 1, WaitOnReturn:=True)

以上のコードは、コマンドプロンプトを呼び出してサブフォルダ含めて全てのフォルダ名とファイル名を取得したいパスをCSVファイルに書き出す処理です。

64行目でフォルダ名とファイル名を出力させるCSVファイルの出力を行うコマンド文(dirコマンド)を変数「cmdTxt」に格納し、67行目でそのコマンドをコマンドプロンプトで実行します。

なぜわざわざコマンドプロンプトを使っているのか(呼び出しているのか)というと、処理の高速化を図るためです。

dirコマンド実行後に出力されるフォルダとファイルの一覧は、この後のBULK INSERTコマンド実行時にSQL Serverのテーブルに追加されます。

BULK INSERTコマンドを使えばSQL Serverのテーブルへの追加がかなり速いので、BULK INSERTコマンド実行用にコマンドプロンプトでdirコマンドを使ってフォルダとファイルの一覧をCSVファイルに書き出します。

なお、コマンドプロンプトで実行するコマンド文は、実は2つのコマンドを1行で実行しています。

2つのコマンド(例)は次の通りです。(コマンド文は「部分一致」のコマンド文)
①chcp 65001
②dir /b “C:\work\” > C:\work\data.csv

コマンドの説明
  1. ①のコマンドは、文字コードをUTF-8に設定するコマンドで、文字化けしないように文字コードをUTF-8に設定しています。
    (文字コードをUTF-8に設定すると文字化けしない)
  2. ②のコマンドは、dirコマンドを実行して、サブフォルダ含めて全ファイルと全フォルダの一覧をファイル「data.csv」に出力しています。
コマンドの「|」とは?
コマンドで使われている「|」の文字は、実行する複数のコマンドを1行で記述したい時に使う文字です。

以上、上記のコマンドが実行されると、フォルダ名とファイル名が出力された「data.csv」というファイルが出力されます。

ちなみに、フォルダ名とファイル名を全てセルに書き出したら、必要がなくなるので281行目で削除します。

    'フォルダ名とファイル名が出力されたファイルを削除する
    Call fso.DeleteFile(fldrFileNMExptTxt, True)
注目すべきコード②

最初に見て頂きたいのは70行目から114行目です。

    'データベース接続情報を取得
    connDB = "Provider=SQLNCLI11.1;"
    connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
    connDB = connDB & "Initial Catalog=" & DBName & ";"
    connDB = connDB & "Trusted_Connection=yes;"
 
    'DBへの接続
    Set objConnection = New ADODB.Connection
    objConnection.CursorLocation = adUseClient
    objConnection.Open connDB
 
    '実行するストアドプロシージャを設定
    Set objCommand = New ADODB.Command
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandType = adCmdStoredProc
    objCommand.CommandText = spName         'ストアドプロシージャ名
    objCommand.CommandTimeout = 300         'タイムアウト時間を設定する(ここでは300秒にしています)
     
    'ストアドプロシージャの引数に渡す値の設定(渡したい引数の数だけ繰り返す)
    For cnt = 0 To UBound(prmValAry(0), 1)
 
        'パラメタオブジェクトの作成
        Set objParameter = objCommand.CreateParameter()
 
        '引数の名前
        objParameter.Name = prmValAry(0)(cnt)
 
        'データ型の指定(ここでは文字列を指定)
        objParameter.Type = adWChar
 
        '文字列のサイズの指定
        objParameter.Size = Len(prmValAry(1)(cnt))
 
        'ストアドプロシージャの引数に渡す値
        objParameter.Value = prmValAry(1)(cnt)
 
        '入力パラメータ
        objParameter.Direction = adParamInput
 
        'ストアドプロシージャの引数に渡す値を設定する
        objCommand.Parameters.Append objParameter
 
    Next
    
    'ストアドプロシージャの呼び出し
    objCommand.Execute

以上のコードは、CSVファイルをBULK INSERTを実行するストアドプロシージャの引数に渡し、BULK INSERTを呼び出してフォルダ・ファイルのパス一覧をSQL Serverのテーブルに追加する処理です。

70行目から73行目でSQL ServerのDBに接続する情報を取得し、76行目から78行目でDBに接続します。

81行目から111行目でストアドプロシージャに渡したい引数の設定を行い、114行目でストアドプロシージャを実行します。(ストアドプロシージャが実行される前に引数が渡されて実行されます)

注目すべきコード③

最初に見て頂きたいのは128行目から168行目です。

    'テーブルデータを取得するSQL文を用意する
    sqlStr = "select"
    sqlStr = sqlStr & "  ROW_NUMBER() OVER(ORDER BY dirPath, fName ASC) rnum"
    sqlStr = sqlStr & ", dirPath"
    sqlStr = sqlStr & ", fName"
    sqlStr = sqlStr & "  from " & tblName
 
    If Sheets("top").OptionButtons("opb_allmatch").Value = 1 Then
 
        '完全一致の場合
        
        '条件(完全一致で取得)
        sqlStr = sqlStr & "  where fName = N'" & Range("searchStr").Value & "'"
 
    ElseIf Sheets("top").OptionButtons("opb_partmatch").Value = 1 Then
 
        '部分一致の場合
 
        '条件(部分一致で取得)
        sqlStr = sqlStr & "  where fName like N'%" & Range("searchStr").Value & "%'"
                
    ElseIf Sheets("top").OptionButtons("opb_fwdmatch").Value = 1 Then
 
        '前方一致の場合
 
        '条件(前方一致で取得)
        sqlStr = sqlStr & "  where fName like N'" & Range("searchStr").Value & "%'"
 
    ElseIf Sheets("top").OptionButtons("opb_rearmatch").Value = 1 Then
 
        '後方一致の場合
 
        '条件(後方一致で取得)
        sqlStr = sqlStr & "  where fName like N'%" & Range("searchStr").Value & "'"
 
    End If
    
    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
 
    '指定したテーブルのデータを参照する(アクセスする)
    oRS.Open

以上のコードは、SQL Serverのテーブルに追加されたフォルダ・ファイル一覧から検索したフォルダとファイルを取得する処理です。

128行目から132行目まででテーブルデータを取得するSQL文を用意し、そのSQL文の条件として139行目で「完全一致」、146行目で「部分一致」、153行目で「前方一致」160行目で「後方一致」を設定しています。

select文が用意出来たら165行目でSourceプロパティにそのselect文を設定して、168行目でselect文を実行します。(実行後は取得したデータの参照が可能になります)

「完全一致」が選択されている場合

「完全一致」が選択されている場合に、
・探しているファイルが「test.csv」
の場合は次のselect文(例)が生成されます。

select
 ROW_NUMBER() OVER(ORDER BY dirPath, fName ASC) rnum
, dirPath
, fName
 from tbl_folderfile_list
 where fName = N'test.csv'
「部分一致」が選択されている場合

「部分一致」が選択されている場合に、
・「test」という文字列を含んでいるフォルダかファイルを探している
場合は次のselect文(例)が生成されます。

select
 ROW_NUMBER() OVER(ORDER BY dirPath, fName ASC) rnum
, dirPath
, fName
  from tbl_folderfile_list
  where fName like N'%test%'
「前方一致」が選択されている場合

「前方一致」が選択されている場合に、
・「test」という文字列を含んでいるフォルダかファイルを前方一致で探している
場合は次のselect文(例)が生成されます。

select
  ROW_NUMBER() OVER(ORDER BY dirPath, fName ASC) rnum
, dirPath
, fName
  from tbl_folderfile_list
  where fName like N'test%'
「後方一致」が選択されている場合

「後方一致」が選択されている場合に、
・「test」という文字列を含んでいるフォルダかファイルを後方一致で探している
場合は次のselect文(例)が生成されます。

select
  ROW_NUMBER() OVER(ORDER BY dirPath, fName ASC) rnum
, dirPath
, fName
  from tbl_folderfile_list
  where fName like N'%test'
注目すべきコード④

最初に見て頂きたいのは252行目から276行目です。

     'listboxのヘッダに表示させる項目名をセルに設定する
    Worksheets(sheetNM).Range("A1").Value = "項番"
    Worksheets(sheetNM).Range("B1").Value = "パス"
    Worksheets(sheetNM).Range("C1").Value = "フォルダ名/ファイル名"
    
    'テーブルデータをシートに貼り付ける
    Worksheets(sheetNM).Range("A2").CopyFromRecordset oRS
        
    'シート「top」に表示を切り替える
    Sheets("top").Select
    
    With ListBox1
    
        'Listboxの表示するデータのセル範囲を指定する
        .ListFillRange = sheetNM & "!$A$2:$C$" & oRS.RecordCount + 1
        
        'ヘッダを表示させる
        .ColumnHeads = True
        
        'Listboxの列数を設定する
        .ColumnCount = 3
        
        'Listboxの列の幅を設定する
        .ColumnWidths = "50;260;800"
            
    End With

以上のコードは、取得したフォルダ名ファイル名をListboxに表示させる処理です。

252行目から254行目では、Listboxのヘッダに表示させる文言です。今回はListboxに表示させるデータが3列あるので3つ設定しています。(セル「A1」「B1」「C1」の3つ)

257行目では、RecordsetオブジェクトのCopyFromRecordsetメソッドを使ってSQL Serverから取得したデータをシート「data」に出力します。

265行目では、ListBoxのListFillRangeプロパティに、シート「data」に貼り付けたSQL Serverから取得したデータのセル範囲を設定します。

セル範囲を設定することで、そのセル範囲のセルの値がListBoxに表示されます。

268行目では、ListBoxのColumnHeadsプロパティにTrueを設定することでヘッダが表示されるようになります。

271行目では、ListBoxのColumnCountプロパティに値を設定することで、その値の数だけ列が表示されます。(3を設定すると3列が表示されます)

274行目のListBoxのColumnWidthsプロパティには列の幅を設定しています。

ストアドプロシージャのコード(例)

CREATE PROCEDURE [dbo].[sp_blkins_fdrfile_list]

	--Excel側から渡された引数
	 @insertDataFilePath		NVARCHAR(100)	-- CSVファイル名(フルパス)
	,@tbleNM					NVARCHAR(100)	-- インポート先のテーブル名

AS
BEGIN

	SET NOCOUNT ON;

	--変数定義
	DECLARE @SQL		NVARCHAR(1000);		-- SQL文格納用変数
	DECLARE @tmpTbl		NVARCHAR(1000);		-- 一時テーブル

	--変数の初期化
	SET @SQL		= '';
	SET @tmpTbl		= 'tmpTBL';

	BEGIN TRANSACTION
	
	--CSVファイルのデータをインポートする前にテーブルデータを削除する
    SET @SQL = 'delete from ' + @tbleNM + ''
    EXEC sp_executesql @SQL;
	
	IF OBJECT_ID (N'##' + @tmpTbl, N'U') IS NULL 
	
		--一時テーブルが存在しない場合

		BEGIN

			--一時テーブルを作成する
			SET @SQL = 'CREATE TABLE ##' + @tmpTbl + '(
								dirPath	NVARCHAR (MAX) NULL
			)';

			EXEC sp_executesql @SQL;

		END

	--CSVに書き出されたフルパスを一時テーブルにインポートする
	SET @SQL = 'BULK INSERT ##' + @tmpTbl +
			   ' FROM ''' + @insertDataFilePath + '''' +		--Excel側から渡されたCSVファイル
			   ' WITH' +
			   ' (' +
			   '  FIELDTERMINATOR  = ''*''' +					--データがアスタリスク区切り(ファイルやフォルダー名に使用できない文字として「*」を採用)
			   ' ,ROWTERMINATOR = ''\n''' +					--終端が改行コード(\n)
			   ' ,FIRSTROW = 1 ' +								--インポートするデータの開始行
			   ' ,CODEPAGE = ''65001''' +
               ' );'

    EXEC sp_executesql @SQL;

	--一時テーブルのデータを、パスとファイル名・ファイル名の格納用テーブルに追加する
	 SET @SQL = 'insert into ' + @tbleNM + '(' +
				' infoNo, dirPath, fName' + 
				' )' +
				' select ROW_NUMBER() OVER(ORDER BY dirPath ASC) infoNo' +
				', LEFT(dirPath, LEN(dirPath) - CHARINDEX(''\'', REVERSE(dirPath))) dirPath' +
				', RIGHT(dirPath, CHARINDEX(''\'', REVERSE(dirPath)) - 1) fName' +
				' from ##' + @tmpTbl

    EXEC sp_executesql @SQL;

	--一時テーブルを削除する
	SET @SQL = N' drop table ##' + @tmpTbl;
	EXEC sp_executesql @SQL;

	COMMIT TRANSACTION;	

END

注目すべきコード①

最初に見て頂きたいのは4行目と5行目です。

	--Excel側から渡された引数
	 @insertDataFilePath		NVARCHAR(100)	-- CSVファイル名(フルパス)
	,@tbleNM					NVARCHAR(100)	-- インポート先のテーブル名

4行目と5行目の変数は、Excel側から渡された引数です。

4行目の変数「insertDataFilePath」

4行目の変数「insertDataFilePath」はCSVファイル名です。Excel側の設定値は次の通りです。

    'フォルダ名とファイル名を出力させるCSVファイル名を取得する
    fldrFileNMExptTxt = ActiveWorkbook.Path & "\" & "data.csv"

また、Excel側の次の行でストアドプロシージャの引数に渡すよう設定しています。

        'ストアドプロシージャの引数に渡す値
        objParameter.Value = prmValAry(1)(cnt)

5行目の変数「tbleNM」

次に5行目の変数「tbleNM」はパスとファイル名・ファイル名の格納用テーブル名です。Excel側の設定値は次の通りです。

    Const tblName As String = "tbl_folderfile_list"                     'インポート先のテーブル名の取得

また、Excel側の次の行で、テーブル名「tbl_folderfile_list」をストアドプロシージャの引数に渡すよう設定しています。

        'ストアドプロシージャの引数に渡す値
        objParameter.Value = prmValAry(1)(cnt)

注目すべきコード②

次に見て頂きたいのは33行目から37行目です。

			--一時テーブルを作成する
			SET @SQL = 'CREATE TABLE ##' + @tmpTbl + '(
								dirPath	NVARCHAR (MAX) NULL
			)';
 
			EXEC sp_executesql @SQL;

CSVファイルのデータをこの一時テーブルに追加したいので、一時テーブルを作成します。

なお、CSVファイルのデータからパスとフォルダ名とファイル名を取得してテーブル「tbl_folderfile_list」に追加した後は一時テーブルは不要になるので、ストアドプロシージャの処理の最後に削除(Drop)します。

	--一時テーブルを削除する
	SET @SQL = N' drop table ##' + @tmpTbl;
	EXEC sp_executesql @SQL;

注目すべきコード③

次に見て頂きたいのは42行目から52行目です。

	--CSVに書き出されたフルパスを一時テーブルにインポートする
	SET @SQL = 'BULK INSERT ##' + @tmpTbl +
			   ' FROM ''' + @insertDataFilePath + '''' +		--Excel側から渡されたCSVファイル
			   ' WITH' +
			   ' (' +
			   '  FIELDTERMINATOR  = ''*''' +					--データがアスタリスク区切り(ファイルやフォルダー名に使用できない文字として「*」を採用)
			   ' ,ROWTERMINATOR = ''\n''' +					--終端が改行コード(\n)
			   ' ,FIRSTROW = 1 ' +								--インポートするデータの開始行
			   ' ,CODEPAGE = ''65001''' +
               ' );'
 
    EXEC sp_executesql @SQL;

以上のコードでは、BULK INSERTコマンドを実行してCSVファイルのデータを一時テーブルに追加しています。

@insertDataFilePathがCSVファイルで、@tmpTblが一時テーブル「tmpTBL」になります。

この一時テーブル「tmpTBL」を元に、フルパスからパスとフォルダ名・ファイル名を取得します。(パスとフォルダ名・ファイル名の取得は、次の「注目すべきコード④」で行います)

注目すべきコード③

次に見て頂きたいのは55行目から61行目です。

	--一時テーブルのデータを、パスとファイル名・ファイル名の格納用テーブルに追加する
	 SET @SQL = 'insert into ' + @tbleNM + '(' +
				' infoNo, dirPath, fName' + 
				' )' +
				' select ROW_NUMBER() OVER(ORDER BY dirPath ASC) infoNo' +
				', LEFT(dirPath, LEN(dirPath) - CHARINDEX(''\'', REVERSE(dirPath))) dirPath' +
				', RIGHT(dirPath, CHARINDEX(''\'', REVERSE(dirPath)) - 1) fName' +
				' from ##' + @tmpTbl
 
    EXEC sp_executesql @SQL;

上記のコードは、一時テーブル「tmpTBL」からフルパスからパスとフォルダ名・ファイル名を取得して、テーブル「tbl_folderfile_list」に追加しています。(パスとフォルダ名・ファイル名とは別に通番も取得しています)

一時テーブル「tmpTBL」のselect文の内容は次の通りです。

  1. 通番:ROW_NUMBER() OVER(ORDER BY dirPath ASC) infoNo
  2. パス:LEFT(dirPath, LEN(dirPath) – CHARINDEX(”\”, REVERSE(dirPath))) dirPath
  3. フォルダ名・ファイル名:RIGHT(dirPath, CHARINDEX(”\”, REVERSE(dirPath)) – 1) fName

通番

データの通番です。

パス

フォルダ・ファイルのフルパスから取得したパスです。

フォルダ名・ファイル名

フォルダ・ファイルのフルパスから取得したフォルダ名・ファイル名です。

動作確認

マクロを実行した実行結果は記事内の次のコントロールの内容をご覧ください(クリックすると記事内の対象の説明に飛びます。)

  1. 「完全一致」(のオプションボタン(opb_allmatch))が選択された状態でマクロを実行した場合
  2. 「部分一致」(のオプションボタン(opb_partmatch))が選択された状態でマクロを実行した場合
  3. 「前方一致」(のオプションボタン(opb_fwdmatch))が選択された状態でマクロを実行した場合
  4. 「後方一致」(のオプションボタン(opb_rearmatch))が選択された状態でマクロを実行した場合

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

  1. Windows Script Host Object Model(wshom.ocx)
  2. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、Excelのマクロのコードの11行目の「WshShell」と12行目の「FileSystemObject」というオブジェクトが「wshom.ocx」というファイルを、14行目から17行目の「ADODB.Connection」「ADODB.Command」「ADODB.Parameter」「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim wshObj              As WshShell             'WshShellオブジェクト
    Dim fso                 As FileSystemObject     'FileSystemObjectのインスタンス用変数
    Dim objConnection       As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim objCommand          As ADODB.Command        'ADODB.Commandオブジェクトのインスタンス用変数
    Dim objParameter        As ADODB.Parameter      'パラメタオブジェクト用変数
    Dim oRS                 As ADODB.Recordset      'レコードセット用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「wshom.ocx」と「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「WshShell」「FileSystemObject」「ADODB.Connection」「ADODB.Command」「ADODB.Parameter」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

最後に

本記事では、マクロ・コマンドプロンプト・SQL Serverのストアドプロシージャを使ってパソコン内のフォルダとファイルを検索する方法についてご説明しました。

処理の流れをおさらいしておくと次の通りです。

STEP.1
【マクロ】マクロがコマンドプロンプトを呼び出す
マクロがコマンドプロンプトを呼び出します。
STEP.2
【コマンドプロンプト】コマンドプロンプトでdirコマンドを実行してフォルダ・ファイルのパスの一覧を取得する
コマンドプロンプトでdirコマンドを実行してフォルダ・ファイルのパスの一覧を取得します。
取得したフォルダ・ファイル一覧はCSVに出力します。
STEP.3
【マクロ】マクロがストアドプロシージャを呼び出す
マクロがストアドプロシージャを呼び出します。
STEP.4
【ストアドプロシージャ】呼び出されたストアドプロシージャでSTEP.2で取得したフォルダ・ファイル一覧をテーブルに追加する
呼び出されたストアドプロシージャでSTEP.2で取得したフォルダ・ファイル一覧をテーブルに追加します。
STEP.5
【マクロ】STEP.4でSQL Serverのテーブルに追加されたフォルダ・ファイル一覧から検索したフォルダとファイルを取得する
STEP.4でSQL Serverのテーブルに追加されたフォルダ・ファイル一覧から検索したフォルダとファイルを取得します。
STEP.6
【マクロ】STEP.5で取得したフォルダ名ファイル名をListboxに表示させる
STEP.5で取得したフォルダ名ファイル名をListboxに表示させます。

フォルダ名・ファイル名を検索する対象の、サブフォルダ含めた格納先配下のフォルダ・ファイル全てを、dirコマンドをコマンドプロンプト上で実行して取得します。

取得したフルパスの一覧からフォルダ名・ファイル名を取り出してテーブルに追加します。

追加したフォルダ名・ファイル名の中から、マクロが検索するフォルダ名・ファイル名をselect文で取り出しListboxに表示させます。

以上で、検索したパソコン内のフォルダとファイルを見つけることができます。

パソコン内のフォルダとファイルを検索したい時は参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→ TechAcademyの「1週間 無料体験」はこちら