この記事では、Excelのマクロ・コマンドプロンプト・Accessを使ってパソコン内のフォルダとファイルを検索する方法についてご説明します。
検索した結果は、シート上に設置されたListboxに表示します。
【動画】マクロ・コマンドプロンプト・SQL Serverのストアドプロシージャを使ってパソコン内のフォルダとファイルを検索する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
①完全一致で検索
②部分一致で検索
③前方一致で検索
④後方一致で検索
指定したフォルダからファイルとフォルダのフルパスをCSVに出力してAccessのテーブルに追加。
追加したファイルとフォルダをマクロが取得してListboxに表示しています。
【参考】テーブルの定義
今回使用するAccessのテーブルの定義は次の通りです。
①テーブル名：tbl_folderfile_list
②テーブル名：tmpTbl(一時テーブル)
※テーブル「tmpTbl」はあらかじめ作成・定義せずにマクロのコード内で作成(Create)(と削除(Drop))しますが、参考までにご紹介します。
'一時テーブルを作成する
sqlStr = "CREATE TABLE " & tmpTbl & "("
sqlStr = sqlStr & "dirPath memo NULL"
sqlStr = sqlStr & ")"
'SQL文を実行する
accApp.DoCmd.RunSQL sqlStr
Excelのマクロ・コマンドプロンプト・Accessを使ってパソコン内のフォルダとファイルを検索する処理の流れ
本処理はExcelのマクロ・コマンドプロンプト・Accessを使っているので、処理の流れをここで整理します。
Excelのマクロ・コマンドプロンプト・Accessを使ってパソコン内のフォルダとファイルを検索する処理の流れは次の通りです。
処理の流れ
取得したフォルダ・ファイル一覧はCSVに出力します。
DoCmdオブジェクトを使うことで、様々なAccessのメソッド(TransferTextもその中の一つ)を使用することができます。
TransferTextメソッドとは、CSVファイルやテキストファイルをAccessにインポート・エクスポートすることができるメソッドです。
マクロを実行する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のマクロの作成の流れ
すでにシートが存在する場合は何もしません。
GetObject関数の引数にAccessファイルのフルパスを指定して実行するとAccessファイルが起動します。
DoCmdオブジェクトを使うことで、様々なAccessのメソッド(TransferTextもその中の一つ)を使用することができます。
TransferTextメソッドとは、CSVファイルやテキストファイルをAccessにインポート・エクスポートすることができるメソッドです。
テーブル「tbl_folderfile_list」は今回の説明で使う、パスとフォルダ名・ファイル名を格納するテーブルです。
コードの例
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 sqlStr As String 'SQL文
Dim wshObj As WshShell 'WshShellオブジェクト
Dim fso As FileSystemObject 'FileSystemObjectのインスタンス用変数
Dim accApp As New Access.Application 'Accessアプリケーション参照用変数
Dim shtExistFlg As Boolean 'シート存在確認フラグ
Dim tmptblExistflg As Boolean '一時テーブル存在確認フラグ
Dim adoRS As Recordset2 'レコードセット用変数
Dim tbldef As DAO.TableDef 'Accessテーブル定義用変数
Dim rng As Range 'Rangeオブジェクト格納用変数
Const rowMaxCnt As Long = 1048576 'Excelファイルの行数
Const fldrFileNMExptTxt As String = "data.csv" 'フォルダ名とファイル名を出力させるCSVファイル名
Const accessFileNM As String = "0110.mdb" 'Accessファイル名
Const impTbl As String = "tbl_folderfile_list" 'パスとフォルダ名・ファイル名を追加するテーブル名
Const tmpTbl As String = "tmpTbl" '一時テーブル名
Const sheetNM As String = "data" 'SQL Serverのテーブルから取得したデータを貼り付けるシート名
'カウンタの初期化を設定
cnt = 1
'シート存在確認フラグにfalse(存在しない)を設定する
shtExistFlg = False
'サブフォルダ含めて全てのフォルダ名・ファイル名を取得したいパス
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 & """" & " > " & ActiveWorkbook.Path & "\" & fldrFileNMExptTxt
'コマンドプロンプトで変数「cmdTxt」のコマンドを実行する
Call wshObj.Run("%ComSpec% /c " & cmdTxt, 1, WaitOnReturn:=True)
'フォルダ名とファイル名貼り付け用シート有無のチェック
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
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
'Accessファイルを開く
Set accApp = GetObject(ActiveWorkbook.Path & "\" & accessFileNM)
'Accessを非表示にする
accApp.Visible = False
'Accessの確認ダイアログを非表示にする
accApp.DoCmd.SetWarnings False
'テーブルデータを削除する
sqlStr = "DELETE FROM " & impTbl
'SQL文を実行する
accApp.DoCmd.RunSQL sqlStr
'Accessの確認ダイアログを表示可にする
accApp.DoCmd.SetWarnings True
'一時テーブル存在確認
For Each tbldef In accApp.CurrentDb.TableDefs
If tbldef.Name = tmpTbl Then
'一時テーブルが存在している場合
'変数「tmptblExistflg」にTrue(テーブルが存在している)を設定する
tmptblExistflg = True
End If
Next
If tmptblExistflg = False Then
'一時テーブルが存在しない場合
'一時テーブルを作成する
sqlStr = "CREATE TABLE " & tmpTbl & "("
sqlStr = sqlStr & "dirPath memo NULL"
sqlStr = sqlStr & ")"
'SQL文を実行する
accApp.DoCmd.RunSQL sqlStr
End If
'CSVファイルのデータをテーブルにインポートする
accApp.DoCmd.TransferText acImportDelim, "T定義", tmpTbl, ActiveWorkbook.Path & "\" & fldrFileNMExptTxt
'一時テーブルのデータをテーブル「tbl_folderfile_list」に追加する
sqlStr = "insert into " & impTbl & " ("
sqlStr = sqlStr & "dirPath , fName )"
sqlStr = sqlStr & "select"
sqlStr = sqlStr & " left(dirPath,instrrev(dirPath,'\')-1)"
sqlStr = sqlStr & ",replace(dirPath,left(dirPath,instrrev(dirPath,'\')),'')"
sqlStr = sqlStr & " from " & tmpTbl
'SQL文を実行する
accApp.DoCmd.RunSQL sqlStr
'テーブルデータを取得するSQL文を用意する
sqlStr = "select"
sqlStr = sqlStr & " dirPath"
sqlStr = sqlStr & ", fName"
sqlStr = sqlStr & " from " & impTbl
If Sheets("top").OptionButtons("opb_allmatch").Value = 1 Then
'完全一致の場合
'条件(完全一致で取得)
sqlStr = sqlStr & " where fName = '" & Range("searchStr").Value & "'"
ElseIf Sheets("top").OptionButtons("opb_partmatch").Value = 1 Then
'部分一致の場合
'条件(部分一致で取得)
sqlStr = sqlStr & " where fName like '*" & Range("searchStr").Value & "*'"
ElseIf Sheets("top").OptionButtons("opb_fwdmatch").Value = 1 Then
'前方一致の場合
'条件(前方一致で取得)
sqlStr = sqlStr & " where fName like '" & Range("searchStr").Value & "*'"
ElseIf Sheets("top").OptionButtons("opb_rearmatch").Value = 1 Then
'後方一致の場合
'条件(後方一致で取得)
sqlStr = sqlStr & " where fName like '*" & Range("searchStr").Value & "'"
End If
'Select文を実行してAccessのテーブルからデータを取得する
Set adoRS = accApp.CurrentDb.OpenRecordset(sqlStr)
If adoRS.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
'フォルダ名とファイル名が出力されたファイルを削除する
Call fso.DeleteFile(ActiveWorkbook.Path & "\" & fldrFileNMExptTxt, True)
MsgBox "検索データがありません。"
'処理を終了する
End
ElseIf adoRS.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
'フォルダ名とファイル名が出力されたファイルを削除する
Call fso.DeleteFile(ActiveWorkbook.Path & "\" & fldrFileNMExptTxt, True)
MsgBox "検索データの件数がExcelの最大行数を超えているので処理を終了します。"
'処理を終了する
End
End If
'テーブルデータをシートに貼り付ける
Worksheets(sheetNM).Range("B2").CopyFromRecordset adoRS
'セルの範囲を取得する(通番を出力するセルの範囲)
Set rng = Worksheets("data").Range("A2:A" & adoRS.recordCount + 1)
'行番をセルに出力する(通番として使用)
rng.Formula = "=row()-1"
With ListBox1
'Listboxの表示するデータのセル範囲を指定する
.ListFillRange = sheetNM & "!$A$2:$C$" & adoRS.recordCount + 1
'ヘッダを表示させる
.ColumnHeads = True
'Listboxの列数を設定する
.ColumnCount = 3
'Listboxの列の幅を設定する
.ColumnWidths = "50;260;800"
End With
'フォルダ名とファイル名が出力されたファイルを削除する
Call fso.DeleteFile(ActiveWorkbook.Path & "\" & fldrFileNMExptTxt, True)
'一時テーブルを削除する
sqlStr = "DROP TABLE " & tmpTbl
'SQL文を実行する
accApp.DoCmd.RunSQL sqlStr
'後処理
Set ws = Nothing
Set wshObj = Nothing
Set fso = Nothing
Set adoRS = Nothing
Sheets("top").Select
MsgBox "完了"
End Sub
コードの解説
注目すべきコード①
最初に見て頂きたいのは49行目から52行目です。
'実行するコマンド：dirコマンドでフルパスをCSVファイルに書き出す
cmdTxt = "chcp 65001 | dir /b /s " & """" & getPath & """" & " > " & fldrFileNMExptTxt
'コマンドプロンプトで変数「cmdTxt」のコマンドを実行する
Call wshObj.Run("%ComSpec% /c " & cmdTxt, 1, WaitOnReturn:=True)
以上のコードは、コマンドプロンプトを呼び出してサブフォルダ含めて全てのフォルダ名とファイル名を取得したいパスをCSVファイルに書き出す処理です。
49行目でフォルダ名とファイル名を出力させるCSVファイルの出力を行うコマンド文(dirコマンド)を変数「cmdTxt」に格納し、52行目でそのコマンドをコマンドプロンプトで実行します。
なぜわざわざコマンドプロンプトを使っているのか(呼び出しているのか)というと、処理の高速化を図るためです。
dirコマンド実行後に出力されるフォルダとファイルの一覧は、この後のDoCmdオブジェクトのTransferTextメソッド実行時にSQL Serverのテーブルに追加されます。
DoCmdオブジェクトのTransferTextメソッドを使えばAccessのテーブルへの追加がかなり速くてExcelのマクロのコード数も少ないので、DoCmdオブジェクトのTransferTextメソッド実行用にコマンドプロンプトでdirコマンドを使ってフォルダとファイルの一覧をCSVファイルに書き出します。
なお、コマンドプロンプトで実行するコマンド文は、実は2つのコマンドを1行で実行しています。
2つのコマンド(例)は次の通りです。(コマンド文は「部分一致」のコマンド文)
①chcp 65001
②dir /b “C:\work\” > C:\work\data.csv
- ①のコマンドは、文字コードをUTF-8に設定するコマンドで、文字化けしないように文字コードをUTF-8に設定しています。
(文字コードをUTF-8に設定すると文字化けしない) - ②のコマンドは、dirコマンドを実行して、サブフォルダ含めて全ファイルと全フォルダの一覧をファイル「data.csv」に出力しています。
以上、上記のコマンドが実行されると、フォルダ名とファイル名が出力された「data.csv」というファイルが出力されます。
ちなみに、フォルダ名とファイル名を全てセルに書き出したら、必要がなくなるので281行目で削除します。
'フォルダ名とファイル名が出力されたファイルを削除する
Call fso.DeleteFile(ActiveWorkbook.Path & "\" & fldrFileNMExptTxt, True)
注目すべきコード②
次に見て頂きたいのは101行目から104行目です。
'Accessファイルを開く
Set accApp = GetObject(ActiveWorkbook.Path & "\" & accessFileNM)
'Accessを非表示にする
accApp.Visible = False
101行目で、GetObject関数の引数にAccessファイルのフルパスを指定して実行しAccessファイルを起動します。
GetObject関数を実行して変数accApp(Accessアプリケーション参照用変数)に割り当てることで、AccessのDoCmdオブジェクトをExcelのマクロで扱うことができるようになります。
変数accAppへの割り当て処理を行うことで、今回は次の処理を行っています。
- SQL文の実行
- Select文を実行して取得したAccessのテーブルデータをRecordsetから取得
- Select文を実行して取得したAccessのテーブルデータの件数を取得
- Accessのテーブル存在確認
- CSVファイルのデータをAccessのテーブルに追加
- Accessの確認ダイアログの表示・非表示
- Accessファイルの非表示
また、GetObject関数を実行するとAccessのファイルが起動して表示されますが、表示は必要ないため104行目のVisibleプロパティにFalseを設定してAccessのファイルを非表示にします。
注目すべきコード③
次に見て頂きたいのは119行目から144行目です。
'一時テーブル存在確認
For Each tbldef In accApp.CurrentDb.TableDefs
If tbldef.Name = tmpTbl Then
'一時テーブルが存在している場合
'変数「tmptblExistflg」にTrue(テーブルが存在している)を設定する
tmptblExistflg = True
End If
Next
If tmptblExistflg = False Then
'一時テーブルが存在しない場合
'一時テーブルを作成する
sqlStr = "CREATE TABLE " & tmpTbl & "("
sqlStr = sqlStr & "dirPath memo NULL"
sqlStr = sqlStr & ")"
'SQL文を実行する
accApp.DoCmd.RunSQL sqlStr
End If
上記コードでは一時テーブルの存在確認及び一時テーブルの作成を行っています。
121行目では、まず一時テーブルが存在しているかを確認するためにTableDefsコレクションに対して検索を行います。
検索というのは、Accessに存在するテーブル全てに対してFor Eachでループさせて一時テーブルのテーブル名と一致しているテーブルがないかを121行目のif文で確認します。
もし一時テーブルが存在していれば126行目の変数「tmptblExistflg」にTrueを設定し、132行目の条件に当てはまらずそのまま144行目以降の処理に進み、一時テーブルが存在していなければ137行目から139行目でCreate文を用意して142行目で一時テーブルを作成します。
注目すべきコード④
次に見て頂きたいのは147行目です。
'CSVファイルのデータをテーブルにインポートする
accApp.DoCmd.TransferText acImportDelim, "T定義", tmpTbl, ActiveWorkbook.Path & "\" & fldrFileNMExptTxt
TransferTextメソッドはCSVファイルのデータをAccessのテーブルに追加します。
なお、今回使ったTransferTextメソッドに設定した引数は次の通りです。
- 第1引数：データを取り込む際の変換の種類
- 第2引数：インポートの定義名
- 第3引数：CSVファイルのデータをインポートするテーブル名
- 第4引数：取り込むCSVファイル名
第1引数：データを取り込む際のデータ変換の種類
第1引数には、データを取り込む際のデータ変換の種類を指定します。
今回指定した定数「acImportDelim」は、カンマ、タブなど区切り記号つきのデータをインポートする場合に指定する定数です。
第2引数：インポートの定義名
第2引数には、取り込むCSVデータのインポートの定義名を指定します。
この定義名とは一体何なのかというと、Accessのインポート定義の画面で作成するインポートの定義名のことです。
Accessのインポート定義の作成をあらかじめ行い、インポート定義名をこの第2引数に指定しないとCSVデータのインポートができないので必ずマクロを実行する前に定義の作成を行う必要があります。
Accessのインポート定義の作成については「Accessのインポート定義の作成方法について」の説明をご覧ください。
第3引数：CSVファイルのデータをインポートするテーブル名
第3引数には、CSVファイルのデータをインポートするテーブル名を指定します。
今回は一時テーブル「tmpTbl」を第3引数に指定しています。
第4引数：取り込むCSVファイル名
第4引数には、取り込むCSVファイル名を指定します。
なお、指定するにはCSVファイル名だけではなく、フルパスで指定します。
注目すべきコード⑤
次に見て頂きたいのは150行目から158行目です。
'一時テーブルのデータをテーブル「tbl_folderfile_list」に追加する
sqlStr = "insert into " & impTbl & " ("
sqlStr = sqlStr & "dirPath , fName )"
sqlStr = sqlStr & "select"
sqlStr = sqlStr & " left(dirPath,instrrev(dirPath,'\')-1)"
sqlStr = sqlStr & ",replace(dirPath,left(dirPath,instrrev(dirPath,'\')),'')"
sqlStr = sqlStr & " from " & tmpTbl
'SQL文を実行する
accApp.DoCmd.RunSQL sqlStr
上記のコードでは「注目すべきコード④」で説明した一時テーブル「tmpTbl」のデータから、パスとフォルダ名・ファイル名をtbl_folderfile_listのテーブルにinsert intoで追加します。
一時テーブル「tmpTBL」のselect文の内容は次の通りです。
- パス：left(dirPath,instrrev(dirPath,’\’)-1)
- フォルダ名・ファイル名：replace(dirPath,left(dirPath,instrrev(dirPath,’\’)),”)
パス
フォルダ・ファイルのフルパスから取得したパスです。
フルパス「dirPath」に対してinstrrev関数を使って「￥」を後方から検索し、最初に見つかった文字位置取得します。
次にleft関数を使って、フルパスに対してinstrrev関数を使った文字位置から左側の文字列を取得します。
この取得した文字列がフォルダ・ファイルのパスになります。
フォルダ名・ファイル名
フォルダ・ファイルのフルパスから取得したフォルダ名・ファイル名です。
フルパス「dirPath」から、先ほど取得したフォルダ・ファイルのパスを削除(ブランクに置換)して残った文字列がフォルダ名・ファイル名になります。
注目すべきコード⑥
次に見て頂きたいのは161行目から197行目です。
'テーブルデータを取得するSQL文を用意する
sqlStr = "select"
sqlStr = sqlStr & " dirPath"
sqlStr = sqlStr & ", fName"
sqlStr = sqlStr & " from " & impTbl
If Sheets("top").OptionButtons("opb_allmatch").Value = 1 Then
'完全一致の場合
'条件(完全一致で取得)
sqlStr = sqlStr & " where fName = '" & Range("searchStr").Value & "'"
ElseIf Sheets("top").OptionButtons("opb_partmatch").Value = 1 Then
'部分一致の場合
'条件(部分一致で取得)
sqlStr = sqlStr & " where fName like '*" & Range("searchStr").Value & "*'"
ElseIf Sheets("top").OptionButtons("opb_fwdmatch").Value = 1 Then
'前方一致の場合
'条件(前方一致で取得)
sqlStr = sqlStr & " where fName like '" & Range("searchStr").Value & "*'"
ElseIf Sheets("top").OptionButtons("opb_rearmatch").Value = 1 Then
'後方一致の場合
'条件(後方一致で取得)
sqlStr = sqlStr & " where fName like '*" & Range("searchStr").Value & "'"
End If
'Select文を実行してAccessのテーブルからデータを取得する
Set adoRS = accApp.CurrentDb.OpenRecordset(sqlStr)
以上のコードは、Accessのテーブルに追加されたフォルダ・ファイル一覧から検索したフォルダとファイルを取得する処理です。
161行目から164行目まででテーブルデータを取得するSQL文を用意し、そのSQL文の条件として171行目で「完全一致」、178行目で「部分一致」、185行目で「前方一致」192行目で「後方一致」を設定しています。
select文が用意出来たら197行目でOpenRecordsetメソッドにそのselect文を設定して実行します。(実行後は取得したデータの参照が可能になります)
「完全一致」が選択されている場合
「完全一致」が選択されている場合に、
・探しているファイルが「test.csv」
の場合は次のselect文(例)が生成されます。
select dirPath , fName from tbl_folderfile_list where fName = 'test.csv'
「部分一致」が選択されている場合
「部分一致」が選択されている場合に、
・「test」という文字列を含んでいるフォルダかファイルを探している
場合は次のselect文(例)が生成されます。
select dirPath , fName from tbl_folderfile_list where fName like '*test*'
「前方一致」が選択されている場合
「前方一致」が選択されている場合に、
・「test」という文字列を含んでいるフォルダかファイルを前方一致で探している
場合は次のselect文(例)が生成されます。
select dirPath , fName from tbl_folderfile_list where fName like 'test*'
「後方一致」が選択されている場合
「後方一致」が選択されている場合に、
・「test」という文字列を含んでいるフォルダかファイルを後方一致で探している
場合は次のselect文(例)が生成されます。
select dirPath , fName from tbl_folderfile_list where fName like '*test'
OpenRecordsetメソッドを使ってrecordsetからデータを取得する場合、ワイルドカード文字に「%」を使うとrecordsetからデータが取得できません。
「*」でないとrecordsetからデータが取得できないので注意してくださいね。
ワイルドカード文字に「*」を使用した場合
検索対象が取得できてrecordCountプロパティにデータが1件あることが確認できています。
ワイルドカード文字に「%」を使用した場合
検索対象が取得できずにrecordCountプロパティにデータが0件と表示されています。
注目すべきコード⑦
次に見て頂きたいのは256行目です。
'テーブルデータをシートに貼り付ける
Worksheets(sheetNM).Range("B2").CopyFromRecordset adoRS
256行目では、CopyFromRecordsetメソッドを使い、recordsetのデータをセルに出力します。
CopyFromRecordsetメソッドの引数にレコードセット用変数「adoRS」を指定して実行することで、recordsetからデータ(パスとフォルダ名・ファイル名)を取得することができます。
注目すべきコード⑧
次に見て頂きたいのは259行目から262行目です。
'セルの範囲を取得する(通番を出力するセルの範囲)
Set rng = Worksheets("data").Range("A2:A" & adoRS.recordCount + 1)
'行番をセルに出力する(通番として使用)
rng.Formula = "=row()-1"
以上のコードは、通番を設定するコードです。
259行目で通番を設定したいセルの範囲を取得し、262行目でそのセルの範囲全てにExcel関数のrow関数を出力します。
row関数が出力されたセルにはそのセルの行位置を出力しますが、データは2行目から出力されるので-1しています。
注目すべきコード⑨
次に見て頂きたいのは264行目から278行目です。
With ListBox1
'Listboxの表示するデータのセル範囲を指定する
.ListFillRange = sheetNM & "!$A$2:$C$" & adoRS.recordCount + 1
'ヘッダを表示させる
.ColumnHeads = True
'Listboxの列数を設定する
.ColumnCount = 3
'Listboxの列の幅を設定する
.ColumnWidths = "50;260;800"
End With
以上のコードは、取得したフォルダ名ファイル名をListboxに表示させる処理です。
267行目では、ListBoxのListFillRangeプロパティに、シート「data」に貼り付けたAccessのテーブルから取得したデータのセル範囲を設定します。
セル範囲を設定することで、そのセル範囲のセルの値がListBoxに表示されます。
270行目では、ListBoxのColumnHeadsプロパティにTrueを設定することでヘッダが表示されるようになります。
273行目では、ListBoxのColumnCountプロパティに値を設定することで、その値の数だけ列が表示されます。(3を設定すると3列が表示されます)
276行目のListBoxのColumnWidthsプロパティには列の幅を設定しています。
Accessのインポート定義の作成方法について
TransferTextメソッドを使ってCSVファイルのデータをAccessのテーブルに追加するにはAccessのインポート定義を引数に指定する必要があります。
Accessのインポート定義の作成は次の通りに作成します。
今回のマクロで必要なAccessのインポート定義は一時テーブルに対してです。
ですが、一時テーブルはマクロの実行中に作成し削除してしまうため、あらかじめ手動で一時テーブルを作成しておき、一時テーブルのインポート定義を作成しておきます。
①テーブルのエクスポートで「テキストファイル」をクリック
エクスポートするテーブルを右クリックし、「エクスポート」→「テキストファイル」をクリックします。
②「データのエクスポート先の選択ウィザード」でファイル名を入力
「データのエクスポート先の選択ウィザード」でファイル名を入力し、OKボタンをクリックします。(参照先とファイル名は任意です。今回はAccessファイルと同じ場所に作成しています)
エクスポートのオプションは任意です。本記事では何も選択しないで説明を続けます。
③「テキストエクスポートウィザード」の「設定」ボタンをクリック
「テキストエクスポートウィザード」の「設定」ボタンをクリックします。
④エクスポート定義画面で「コードページ」のリストから「Unicode(UTF-8)」を選択する
エクスポート定義画面で「コードページ」のリストから「Unicode(UTF-8)」を選択します。
なぜ「Unicode(UTF-8)」を選択するのかというと、CSVファイルのデータがUTF-8形式で生成されているからです。
下のコードはコマンドプロンプト上でdirコマンドを実行する際に「chcp 65001」(UTF-8形式)というコマンドを実行しています。
'実行するコマンド：dirコマンドでフルパスをCSVファイルに書き出す
cmdTxt = "chcp 65001 | dir /b /s " & """" & getPath & """" & " > " & fldrFileNMExptTxt
UTF-8形式でCSVファイルを生成しているので、それに合わせてAccessのインポート定義の作成する際に「Unicode(UTF-8)」を選択する必要があります。
⑤保存ボタンをクリックする
保存ボタンをクリックします。
⑥インポート定義名の入力
「インポート/エクスポート定義の保存」ダイアログボックスでインポート定義名を入力します。
今回は「T定義」という名称にしました。
この「T定義」という名称をTransferTextメソッドの第2引数に設定します。
accApp.DoCmd.TransferText acImportDelim, "T定義", tmpTbl, ActiveWorkbook.Path & "\" & fldrFileNMExptTxt
⑦OKをクリックする
OKボタンをクリックします。
⑧完了ボタンをクリックする
完了ボタンをクリックします。
⑨閉じるボタンをクリック
閉じるボタンをクリックします。
エクスポート定義の作成は以上です。
エクスポート定義の確認
エクスポート定義は「エクスポート定義ウィザード」画面で「定義」ボタンをクリックすると、「インポート/エクスポート定義」ダイアログが表示されるので、先ほど作成したエクスポート定義名が表示されていることを確認できればOKです。
動作確認
マクロを実行した実行結果は記事内の次のコントロールの内容をご覧ください(クリックすると記事内の対象の説明に飛びます。)
- 「完全一致」(のオプションボタン(opb_allmatch))が選択された状態でマクロを実行した場合
- 「部分一致」(のオプションボタン(opb_partmatch))が選択された状態でマクロを実行した場合
- 「前方一致」(のオプションボタン(opb_fwdmatch))が選択された状態でマクロを実行した場合
- 「後方一致」(のオプションボタン(opb_rearmatch))が選択された状態でマクロを実行した場合
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft Access 15.0 Object Library(MSACC.OLB)
- Windows Script Host Object Model(wshom.ocx)
- microsoft office 15.0 access database engine object library(ACEDAO.DLL)
なぜ必要かというと、Excelのマクロのコードの12行目の「Access.Application」というオブジェクトが「MSACC.OLB」というファイルを、10行目の「WshShell」と11行目の「FileSystemObject」というオブジェクトが「wshom.ocx」というファイルを、15行目の「Recordset2」と16行目の「DAO.TableDef」というオブジェクトが「ACEDAO.DLL」というファイルを参照するからです。
Dim accApp As New Access.Application 'Accessアプリケーション参照用変数
Dim wshObj As WshShell 'WshShellオブジェクト
Dim fso As FileSystemObject 'FileSystemObjectのインスタンス用変数
Dim adoRS As Recordset2 'レコードセット用変数
Dim tbldef As DAO.TableDef 'Accessテーブル定義用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「MSACC.OLB」「wshom.ocx」「ACEDAO.DLL」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「Access.Application」「WshShell」「FileSystemObject」「Recordset2」「DAO.TableDef」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、Excelのマクロ・コマンドプロンプト・Accessを使ってパソコン内のフォルダとファイルを検索する方法についてご説明しました。
処理の流れをおさらいしておくと次の通りです。
取得したフォルダ・ファイル一覧はCSVに出力します。
フォルダ名・ファイル名を検索する対象の、サブフォルダ含めた格納先配下のフォルダ・ファイル全てを、dirコマンドをコマンドプロンプト上で実行して取得します。
取得したフルパスの一覧からフォルダ名・ファイル名を取り出してAccessのテーブルに追加します。
追加したフォルダ名・ファイル名の中から、マクロが検索するフォルダ名・ファイル名をselect文で取り出しListboxに表示させます。
以上で、検索したパソコン内のフォルダとファイルを見つけることができます。
パソコン内のフォルダとファイルを検索したい時は参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
