この記事では、Excelのマクロ・PowerShellを使ってパソコン内のフォルダとファイルを検索する方法についてご説明します。
【動画】Excelのマクロ・PowerShellを使ってパソコン内のフォルダとファイルを検索する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
パソコン内のフォルダ名とファイル名をPowerShellで取得後、Excelのシートに貼り付けてListboxに表示させています。
なお動画内で行っているパソコン内のフォルダ名とファイル名の検索は、「完全一致」「部分一致」「前方一致」「後方一致」の4つの検索方法で行っています。
「完全一致」「部分一致」「前方一致」「後方一致」はSQLのSELECT文で行います。
マクロ作成の流れ
取得したフォルダ・ファイル一覧はCSVに出力します。
SELECT文には、指定された「完全一致」「部分一致」「前方一致」「後方一致」どれかの条件で検索して取得します。(Where句を使用)
Excelファイルの例
今回は次のExcelファイルを作成しました。
配置されているコントロールやセルの名前は次の通りです。
searchpath
フォルダやファイルを探す対象のフォルダパスを指定するセルに、「searchpath」という名前を付けます。
例えば、Cドライブ配下全てでフォルダやファイルを検索する場合は「c:\」または「c:」と入力します。
searchStr
探したいフォルダ名やファイル名(名称の一部でもOK)を入力するセルに「searchStr」という名前を付けます。
このセルに探したいフォルダ名やファイル名、もしくは探したいフォルダやファイルの文字列の一部を入力して検索します。
例えば、「work」というフォルダ名や「test.txt」というファイル名を探す場合は、このセルに「work」または「test.txt」と入力し、検索すると検索結果が表示されます。
また、探したいフォルダやファイルの文字列の一部だけで検索する場合、例えば「temp」という文字列だけで探す場合は「temp」と入力します。(※部分一致や前方・後方一致などを選んで検索した場合)
文字列の一部だけで検索した場合は文字列を含むフォルダやファイルが検索結果に表示されます。
opb_allmatch(フォームコントロール)
フォルダやファイルを「完全一致」で検索するよう設定するオプションボタンです。
フォルダ名やファイル名と完全に一致するフォルダやファイルを検索します。
検索したファイルが見つかりました。
また、完全に一致しないと検索がヒットしません。
opb_partmatch(フォームコントロール)
フォルダやファイルを「部分一致」で検索するよう設定するオプションボタンです。
フォルダ名やファイル名の一部の文字列だけでフォルダやファイルを検索します。
opb_fwdmatch(フォームコントロール)
フォルダやファイルを「前方一致」で検索するよう設定するオプションボタンです。
フォルダ名やファイル名の前の文字から順に条件に一致するフォルダやファイルを検索します。
opb_rearmatch(フォームコントロール)
フォルダやファイルを「後方一致」で検索するよう設定するオプションボタンです。
フォルダ名やファイル名の後ろの文字から順に条件に一致するフォルダやファイルを検索します。
cbx_dircmd_execornot(フォームコントロール)
マクロを実行したときに、フォルダ／ファイル一覧のCSVファイルを生成するかを判定するチェックボックスです。
チェックボックスにチェックが付いた状態でマクロを実行すると、フォルダ／ファイル一覧のCSVファイルが生成されます。
チェックボックスにチェックが付いていない状態でマクロを実行すると、フォルダ／ファイル一覧のCSVファイルは生成されません。
ちなみに、フォルダ／ファイル一覧のCSVファイルは「data_output.csv」という名前で、本マクロのExcelファイルと同じ場所に生成されます。
「data_output.csv」の中身は下の通りです。
チェックボックスにチェックが付いていない状態でマクロを実行すると、新たにフォルダ／ファイル一覧のCSVファイルは生成されず、すでにある「data_output.csv」を参照して「data_output.csv」からフォルダ名またはファイル名を取得しListboxに表示されます。
→1度はチェックボックスにチェックが付いた状態でマクロを実行しておく必要があります
もしフォルダやファイルを新規作成・削除・移動などしていない状態でマクロを実行する場合は、1度生成された「data_output.csv」を参照するだけなので時間短縮になります。(毎回「data_output.csv」を作ると時間がかかってしまうので)
listbox1(ActiveXコントロール)
listboxで検索結果を表示させます。
Excelのマクロのコード(例)
Option Explicit
Private Sub btn_exec_Click()
Dim ws As Worksheet 'Worksheet用変数
Dim outPutCSVFile As String '出力するCSVファイルのフルパス用変数
Dim searchPath As String '確認したいファイルが存在するフォルダのパス
Dim work_ws As Worksheet 'Worksheet用変数
Dim shtExistFlg As Boolean 'シート存在確認フラグ
Dim strCmd As String 'コマンド用変数
Dim oShell As Object 'WshShellオブジェクト用変数
Dim fso As Object 'FileSystemObjectのインスタンス用変数
Dim adoCON As ADODB.Connection 'Connection用変数
Dim oRS As ADODB.Recordset 'レコードセット用変数
Dim sqlStr As String 'SQL文
'出力するCSVファイル名
Const outPutCSVFileNM As String = "data_output.csv"
'CSVファイルから取得したデータを貼り付けるシート名
Const dtSheetNM As String = "data"
'Excelのシートの最大行数
Const rowMaxCnt As Long = 1048576
'本マクロのブックのシート名を取得する
Set ws = Worksheets("top")
'出力するCSVファイル名を取得する(保存先はマクロのExcelファイルと同じ場所とする)
outPutCSVFile = ThisWorkbook.Path & "\" & outPutCSVFileNM
'確認したいファイルが存在するフォルダのパス
searchPath = ws.Range("searchpath").Value
'フォルダ名とファイル名貼り付け用シート有無のチェック
For Each work_ws In Worksheets
If work_ws.Name = dtSheetNM Then
'シート「data」が存在している場合
'変数「shtExistFlg」にTrueを設定する
shtExistFlg = True
End If
Next work_ws
If shtExistFlg = False Then
'シート「data」が存在しない場合
'テーブルから取得するデータを列挙するためのシートを新規作成する
Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
.Name = dtSheetNM
Else
'シート「data」が存在する場合
'シート「data」のセル全てをクリアする
Worksheets(dtSheetNM).Cells.Clear
End If
If Worksheets("top").CheckBoxes("cbx_dircmd_execornot").Value = 1 Then
'「フォルダ／ファイル一覧を生成する」のチェックボックスにチェックが付いている場合
'フォルダとファイルを検索する検索文字列を条件に、パソコン内のフォルダとファイルを検索・出力してその出力内容をCSVファイルに書き込むためのPowrShellコマンド文を取得する
strCmd = "powershell.exe -Command ""Get-ChildItem -Path '" & searchPath & "' -Recurse | " & _
"Select-Object @{Name='Type'; Expression={If ($_.PSIsContainer) {'Folder'} Else {'File'}}}, " & _
"@{Name='Path'; Expression={If ($_.PSIsContainer) {Split-Path $_.FullName} Else {Split-Path $_.FullName}}}, Name, " & _
"@{Name='FileSize (MB)'; Expression={($_.Length / 1MB)}}, CreationTime, LastWriteTime | " & _
"Export-Csv -Path '" & outPutCSVFile & "' -Encoding UTF8 -NoTypeInformation"""
'WshShellオブジェクト用インスタンスを生成する
Set oShell = CreateObject("WScript.Shell")
'PowerShellを呼び出してコマンドを実行する
oShell.Run strCmd, 0, True
Else
'「フォルダ／ファイル一覧を生成する」のチェックボックスにチェックが付いていない場合
'FileSystemObjectのインスタンスを生成する
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(ThisWorkbook.Path & "\" & "data_output.csv") = False Then
MsgBox "フォルダ／ファイル一覧のCSVファイル「data_output.csv」が存在しないので処理を終了します。" & Chr(10) & _
"「フォルダ／ファイル一覧を生成する」にチェックを付けて再実行してください。"
'処理を終了する
Exit Sub
End If
End If
'Connectionインスタンスの生成
Set adoCON = New ADODB.Connection
'CSVへのコネクション
With adoCON
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=No;FMT=Delimited"
'コネクションを開く
.Open ThisWorkbook.Path & "\"
End With
'データを取得するSQL文を作成する(ファイルサイズでソート)
sqlStr = "select"
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & outPutCSVFileNM & "]"
If Sheets("top").OptionButtons("opb_allmatch").Value = 1 Then
'選択されたラジオボタンが「完全一致」の場合
'検索するフォルダ名／ファイル名を取得する(完全一致で取得)
sqlStr = sqlStr & " where F3 = '" & CStr(ws.Range("searchStr").Value) & "'"
ElseIf Sheets("top").OptionButtons("opb_partmatch").Value = 1 Then
'選択されたラジオボタンが「部分一致」の場合
'検索するフォルダ名／ファイル名を取得する(部分一致で取得)
sqlStr = sqlStr & " where F3 like '%" & CStr(ws.Range("searchStr").Value) & "%'"
ElseIf Sheets("top").OptionButtons("opb_fwdmatch").Value = 1 Then
'選択されたラジオボタンが「前方一致」の場合
'条件(前方一致で取得)
'検索するフォルダ名／ファイル名を取得する(前方一致で取得)
sqlStr = sqlStr & " where F3 like '" & CStr(ws.Range("searchStr").Value) & "%'"
ElseIf Sheets("top").OptionButtons("opb_rearmatch").Value = 1 Then
'選択されたラジオボタンが「後方一致」の場合
'条件(後方一致で取得)
'検索するフォルダ名／ファイル名を取得する(前方一致で取得)
sqlStr = sqlStr & " where F3 like '%" & CStr(ws.Range("searchStr").Value) & "'"
End If
sqlStr = sqlStr & " order by F4 desc"
'Recordsetオブジェクトのインスタンスを生成する
Set oRS = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
oRS.CursorType = adOpenDynamic
'SELECT文を実行してRecordsetを開く
oRS.Open sqlStr, adoCON, adOpenStatic
With Worksheets(dtSheetNM)
'シート「data」のセル全てをクリアする
.Cells.Clear
'listboxのヘッダに表示させる項目名をセルに設定する
.Range("A1").Value = "項番"
.Range("B1").Value = "種類"
.Range("C1").Value = "フォルダ・ファイルパス"
.Range("D1").Value = "フォルダ・ファイル名"
.Range("E1").Value = "ファイルサイズ(MB)"
.Range("F1").Value = "ファイル作成日時"
.Range("G1").Value = "ファイル更新日時"
End With
If oRS.RecordCount = 0 Then
'検索データが存在しない場合
'ListFillRangeにヘッダ部とその下の行のセルを参照させて、Listboxにヘッダ部分だけを表示させる
ListBox1.ListFillRange = dtSheetNM & "!$A$2:$D$2"
'後処理
oRS.Close
Set oShell = Nothing
Set adoCON = Nothing
Set oRS = Nothing
MsgBox "検索データがありません。"
'処理を終了する
Exit Sub
ElseIf oRS.RecordCount > rowMaxCnt Then
'検索データの件数がExcelの最大行数を超えている場合
'ListFillRangeにヘッダ部とその下の行のセルを参照させて、Listboxにヘッダ部分だけを表示させる
ListBox1.ListFillRange = dtSheetNM & "!$A$2:$D$2"
'後処理
oRS.Close
Set oShell = Nothing
Set adoCON = Nothing
Set oRS = Nothing
MsgBox "検索データの件数がExcelの最大行数を超えているので処理を終了します。"
'処理を終了する
Exit Sub
End If
With Worksheets(dtSheetNM)
'テーブルデータをシートに貼り付ける
.Range("B2").CopyFromRecordset oRS
'シート「data」を選択する
.Select
'表のA列の先頭行にROW関数を使って項番を振る
.Range("A" & 2).FormulaR1C1 = "=ROW()-1"
'ROW関数を使ったセルをコピーする
.Range("A2").Copy
'データが出力された行位置までA列のセルを選択する
.Range("A2:A" & (2 + CLng(oRS.RecordCount) - 1)).Select
'選択されたセルに、数式のみをセルに貼り付ける
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'表のA列の先頭のセルをを選択する
.Range("A1").Select
End With
'シート「top」に表示を切り替える
Sheets("top").Select
With ListBox1
'Listboxの表示するデータのセル範囲を指定する
.ListFillRange = dtSheetNM & "!$A$2:$G$" & CLng(oRS.RecordCount) + 1
'ヘッダを表示させる
.ColumnHeads = True
'Listboxの列数を設定する
.ColumnCount = 7
'Listboxの列の幅を設定する
.ColumnWidths = "40;50;300;150;120;130"
End With
'後処理
oRS.Close
Set oShell = Nothing
Set adoCON = Nothing
Set oRS = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは17行目です。
'出力するCSVファイル名
Const outPutCSVFileNM As String = "data_output.csv"
コードの説明
以上のコードは、PoerShellを実行してパソコン内のフォルダとファイルの検索した結果の一覧を出力・保存する先のCSVファイル名の定数宣言のコードです。
今回は、PowerShellのコマンドが実行されると、「data_output.csv」というCSVファイルにパソコン内のフォルダとファイルの検索した結果の一覧を出力・保存されます。
注目すべきコード②
次に見て頂きたいのは23行目です。
'Excelのシートの最大行数
Const rowMaxCnt As Long = 1048576
以上のコードは、Excelのシートの最大行数を格納する定数宣言のコードです。
今回のマクロは、Listboxがシート「data」に出力された値(パソコン内のフォルダとファイルの検索した結果の一覧)を参照するのですが、シートの最大行数が1048576なので、その最大行数まではListboxに表示させることができます。
ですが、もしパソコン内のフォルダとファイルの検索した結果が1048576件を超えた場合は、その超えた件数はシートには入り切りません。
なので今回のマクロは、1048576を超える場合は処理を強制終了させるよう制御させています。
その強制終了させるための制御に1048576の値を使いたいため定数宣言を行っています。
注目すべきコード③
次に見て頂きたいのは32行目です。
'フォルダ・ファイルが存在するのか検索するフォルダのパスを取得する
searchPath = ws.Range("searchpath").Value
コードの説明
以上のコードは、フォルダ・ファイルが存在するのか検索する検索文字列を取得するコードです。
今回は、Excelのシートにある「searchStr」の名前のセルに入力された値(パス)から取得します。
注目すべきコード④
次に見て頂きたいのは35行目から63行目です。
'フォルダ名とファイル名貼り付け用シート有無のチェック
For Each ws In Worksheets
If ws.Name = dtSheetNM Then
'シート「data」が存在している場合
'変数「shtExistFlg」にTrueを設定する
shtExistFlg = True
End If
Next ws
If shtExistFlg = False Then
'シート「data」が存在しない場合
'テーブルから取得するデータを列挙するためのシートを新規作成する
Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
.Name = dtSheetNM
Else
'シート「data」が存在する場合
'シート「data」のセル全てをクリアする
Worksheets(dtSheetNM).Cells.Clear
End If
コードの説明
以上のコードは、CSVファイルのデータを出力するシート「data」を生成する処理のコードです。
CSVファイルのデータを出力するシート「data」を生成するかどうかは、Excelファイルに存在しない場合に生成します。
なので、まずはシート「data」がExcelファイルに存在するかを判定し、ない場合に生成します。
コードの詳細
35行目のコードでは、Excelファイル内にあるシートを一つ一つ取得するためのFor文を用意しています。
もしExcelファイルに3つシートが存在していれば、For文内を3回ループします。
For文をループする中で、取得したシート名とシート名「data」(定数dtSheetNMの値)が一致するかを37行目で判定します。
もし一致すれば、Excelファイルの中にシート「data」が存在していることが分かったので、42行目で変数shtExistFlgにTrueを設定します。
48行目で変数shtExistFlgの値を確認し、Falseの場合(シート「data」が存在しない場合)は、53行目と54行目でシート「data」を生成します。
Trueの場合(56行目)はシート「data」が存在しているので、61行目でそのシート「data」をクリアします。(CSVファイルのデータを出力するため)
注目すべきコード⑤
次に見て頂きたいのは65行目から99行目です。
If Worksheets("top").CheckBoxes("cbx_dircmd_execornot").Value = 1 Then
'「フォルダ／ファイル一覧を生成する」のチェックボックスにチェックが付いている場合
'フォルダとファイルを検索する検索文字列を条件に、パソコン内のフォルダとファイルを検索・出力してその出力内容をCSVファイルに書き込むためのPowrShellコマンド文を取得する
strCmd = "powershell.exe -Command ""Get-ChildItem -Path '" & searchPath & "' -Recurse | " & _
"Select-Object @{Name='Type'; Expression={If ($_.PSIsContainer) {'Folder'} Else {'File'}}}, " & _
"@{Name='Path'; Expression={If ($_.PSIsContainer) {Split-Path $_.FullName} Else {Split-Path $_.FullName}}}, Name, " & _
"@{Name='FileSize (MB)'; Expression={($_.Length / 1MB)}}, CreationTime, LastWriteTime | " & _
"Export-Csv -Path '" & outPutCSVFile & "' -Encoding UTF8 -NoTypeInformation"""
'WshShellオブジェクト用インスタンスを生成する
Set oShell = CreateObject("WScript.Shell")
'PowerShellを呼び出してコマンドを実行する
oShell.Run strCmd, 0, True
Else
'「フォルダ／ファイル一覧を生成する」のチェックボックスにチェックが付いていない場合
'FileSystemObjectのインスタンスを生成する
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(ThisWorkbook.Path & "\" & "data_output.csv") = False Then
MsgBox "フォルダ／ファイル一覧のCSVファイル「data_output.csv」が存在しないので処理を終了します。" & Chr(10) & _
"「フォルダ／ファイル一覧を生成する」にチェックを付けて再実行してください。"
'処理を終了する
Exit Sub
End If
End If
以上のコードは、「フォルダ／ファイル一覧を生成する」のチェック有無を判定し、フォルダ／ファイル一覧のCSVファイルを生成する処理のコードです。
コードの詳細
65行目のコードでは、「フォルダ／ファイル一覧を生成する」のチェック有無を判定しています。
「フォルダ／ファイル一覧を生成する」にチェックが付いている場合はValueプロパティの値は1を返し、70行目から74行目のフォルダ／ファイル一覧のCSVファイルを生成するPowerShellのコマンドを生成する処理に遷移します。
どんなコマンドなのか簡単に説明すると、パソコン内のフォルダとファイルを検索する場所を指定して検索し、その検索結果をCSVファイルに書き込むコマンドです。
詳しくはここからご説明します。
PowerShellのコマンドを次に詳しく説明します。
①「powershell.exe」
「powershell.exe」は、PowerShellの実行ファイルを指します。
PowerShellの実行ファイルを呼び出し、「PowerShell」の文字列の後に続くコマンド文を実行します。
②「-command」
「-command」は、PowerShellのコマンドラインオプションの1つです。
この「-command」を使うことでPowerShellスクリプトを実行することができます。
③「”Get-ChildItem -Path ‘” & searchPath & “‘ -Recurse」
「Get-ChildItem」は、指定したパスのフォルダ内の情報を取得するコマンドレットです。
「-Recurse」は、「Get-ChildItem」で情報を取得するのにサブディレクトリ内のすべてのファイルとフォルダを取得するようにするオプションです。
④「Select-Object」
「Select-Object」とは、オブジェクトのプロパティを選択してCSVファイルからデータを抽出するために使います。
この「Select-Object」コマンドレットの引数に、どの項目の値を出力するのかを指定します。
今回は、種類(フォルダまたはファイル)、ファイルパス、ファイル名、ファイルサイズ(MB)、ファイル作成日時、ファイル更新日時を出力します。
⑤「@{Name=’Type’; Expression={If ($_.PSIsContainer) {‘Folder’} Else {‘File’}}}, @{Name=’Path’; Expression={If ($_.PSIsContainer) {Split-Path $_.FullName} Else {Split-Path $_.FullName}}}, Name, @{Name=’FileSize (MB)’; Expression={($_.Length / 1MB)}}, CreationTime, LastWriteTime」
本項目はかなり長い文字列ですが、この文字列は種類(フォルダまたはファイル)、ファイルパス、ファイル名、ファイルサイズ(MB)、ファイル作成日時、ファイル更新日時の6つの項目が並んだ文字列です。
長い文字列が並んでいて分かりにくいですが、この6つの項目の値を取得します。
分解してみると下の通りです。
- @{Name=’Type’; Expression={If ($_.PSIsContainer) {‘Folder’} Else {‘File’}}}・・・フォルダかファイルか
- @{Name=’Path’; Expression={If ($_.PSIsContainer) {Split-Path $_.FullName} Else {Split-Path $_.FullName}}}・・・フォルダまたはファイルのパス
- Name・・・フォルダ名またはファイル名
- @{Name=’FileSize (MB)’; Expression={($_.Length / 1MB)}}・・・ファイルサイズ(MB)
- CreationTime・・・フォルダまたはファイルの作成日時
- LastWriteTime・・・フォルダまたはファイルの更新日時
「@{Name=’Type’; Expression={If ($_.PSIsContainer) {‘Folder’} Else {‘File’}}}」は、フォルダなのかファイルなのかを判定し、フォルダなら「Folder」の文字列を、ファイルなら「File」の文字列を出力します。
「@{Name=’Path’; Expression={If ($_.PSIsContainer) {Split-Path $_.FullName} Else {Split-Path $_.FullName}}}」はフォルダまたはファイルのパスを取得します。
Nameプロパティは、フォルダ名またはファイル名を取得します。
「@{Name=’FileSize (MB)’; Expression={($_.Length / 1MB)}}」は、Lengthプロパティを1MB単位で出力したファイルサイズを取得します。
CreationTimeプロパティは、フォルダまたはファイルの作成日時を取得します。
LastWriteTimeプロパティは、フォルダまたはファイル名を取得します。
⑥Export-Csv -Path ‘” & outPutCSVFile & “‘
「Export-Csv」は出力したコマンドの結果をCSVファイルに出力・保存するコマンドレットです。
⑦「-Encoding UTF8」
「-Encoding UTF8」は、出力するCSVファイルのエンコーディングをUTF-8で指定します。
「-Encoding」の引数にUTF-8を指定することで、データが文字化けせずにUTF-8形式のCSVファイルを出力することができます。
⑧-NoTypeInformation
「NoTypeInformation」オプションを付けると、ディレクトリ内のファイルとフォルダをリスト形式で出力します(オブジェクトの型に関する情報は出力しない)
77行目のコードでは、WshShellオブジェクト用インスタンスを生成します。
WshShellオブジェクト用インスタンスを生成することで、「注目すべきコード⑤」で生成したPowerShellコマンドをマクロが実行させることができるようになります。
80行目のコードでは、Runメソッドの引数にコマンドを指定してRunメソッドを実行することで、PowerShellが呼び出されてコマンドが実行されます。
このコマンドが正常に実行されると、以下のようにCSVファイルが生成されます。
CSVファイルには、種類(フォルダまたはファイル)、ファイルパス、ファイル名、ファイルサイズ(MB)、ファイル作成日時、ファイル更新日時が出力されます。
82行目のElseで「フォルダ／ファイル一覧を生成する」のチェックボックスにチェックが付いていないかを判定します。
合致する場合は87行目でFileSystemObjectのインスタンスを生成し、89行目でフォルダ／ファイル一覧のCSVファイル「data_output.csv」が存在するかを判定します。
存在していなければ91行目で、フォルダ／ファイル一覧のCSVファイル「data_output.csv」が存在していない旨のメッセージを出力し、95行目でマクロを終了します。
存在していれば何もしないでそのまま処理を継続します。
注目すべきコード⑥
次に見て頂きたいのは102行目から113行目です。
'Connectionインスタンスの生成
Set adoCON = New ADODB.Connection
'CSVへのコネクション
With adoCON
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=No;FMT=Delimited"
'コネクションを開く
.Open ThisWorkbook.Path & "\"
End With
コードの説明
以上のコードは、マクロがCSVに接続するための接続情報を取得して接続するコードです。
コードの詳細
102行目のコードでは、Connectionインスタンスを生成します。
このインスタンスがないとマクロがCSVファイルに接続することができないので必ず生成しておきます。
107行目と108行目で接続情報を取得し、111行目でOpenメソッドを実行してマクロがCSVファイルに接続します。
注目すべきコード⑦
次に見て頂きたいのは116行目から164行目です。
'データを取得するSQL文を作成する(ファイルサイズでソート)
sqlStr = "select"
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & outPutCSVFileNM & "]"
If Sheets("top").OptionButtons("opb_allmatch").Value = 1 Then
'選択されたラジオボタンが「完全一致」の場合
'検索するフォルダ名／ファイル名を取得する(完全一致で取得)
sqlStr = sqlStr & " where F3 = '" & CStr(ws.Range("searchStr").Value) & "'"
ElseIf Sheets("top").OptionButtons("opb_partmatch").Value = 1 Then
'選択されたラジオボタンが「部分一致」の場合
'検索するフォルダ名／ファイル名を取得する(部分一致で取得)
sqlStr = sqlStr & " where F3 like '%" & CStr(ws.Range("searchStr").Value) & "%'"
ElseIf Sheets("top").OptionButtons("opb_fwdmatch").Value = 1 Then
'選択されたラジオボタンが「前方一致」の場合
'条件(前方一致で取得)
'検索するフォルダ名／ファイル名を取得する(前方一致で取得)
sqlStr = sqlStr & " where F3 like '" & CStr(ws.Range("searchStr").Value) & "%'"
ElseIf Sheets("top").OptionButtons("opb_rearmatch").Value = 1 Then
'選択されたラジオボタンが「後方一致」の場合
'条件(後方一致で取得)
'検索するフォルダ名／ファイル名を取得する(前方一致で取得)
sqlStr = sqlStr & " where F3 like '%" & CStr(ws.Range("searchStr").Value) & "'"
End If
sqlStr = sqlStr & " order by F4 desc"
'Recordsetオブジェクトのインスタンスを生成する
Set oRS = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
oRS.CursorType = adOpenDynamic
'SELECT文を実行してRecordsetを開く
oRS.Open sqlStr, adoCON, adOpenStatic
コードの説明
以上のコードは、フォルダ／ファイル一覧のCSVファイル「data_output.csv」から条件に合致したデータを取得するSQLのSELECT文を用意し実行する処理のコードです。
SELECT文は、「完全一致」「部分一致」「前方一致」「後方一致」のいずれかの条件を指定してデータを取得します。
コードの詳細
116行目から119行目はSELECT文のフィールド指定とFROM句の指定で、FROM句にはテーブル名ではなくフォルダ／ファイル一覧のCSVファイル「data_output.csv」を指定します。
121行目、128行目、135行目、144行目は、「完全一致」「部分一致」「前方一致」「後方一致」のうちどれが選択されたかを判定するのIF文とElseIf文です。
「完全一致」が選択された場合は121行目のIFの条件に合致するので、126行目で「where F3 like 」と検索文字列を結合して変数searchStrに格納します。
「部分一致」が選択された場合は128行目のElseIfの条件に合致するので、133行目で検索文字列の頭と末尾に「%」を結合させてさらに「where F3 like 」と結合させて変数searchStrに格納します。
「前方一致」が選択された場合は135行目のElseIfの条件に合致するので、142行目で検索文字列の末尾に「%」を結合させてさらに「where F3 like 」と結合させて変数searchStrに格納します。
「後方一致」が選択された場合は144行目のElseIfの条件に合致するので、151行目で検索文字列の頭に「%」を結合させてさらに「where F3 like 」と結合させて変数searchStrに格納します。
「F3」は3列目のフィールドName(フォルダ名またはファイル名)を指します。
今回はフォルダ名またはファイル名で検索するので、「F3」をWhere句に指定しています。
155行目では、ソート順の指定のOrder By句をsqlStrに格納しています。
今回はファイルのサイズ順を指定しています。(F4は3列目のフィールドFileSize (MB)を指します)
ソート順の指定はこの155行目で行っているので、別のフィールドでソート順を指定したい場合はお好みのフィールドに変更してみてくださいね。
以上155行目まででSELECT文が用意できました。
135行目のコードでは、Recordsetオブジェクトのインスタンスを生成し、138行目でカーソルタイプにキーセットカーソル使用します。
141行目のコードでは、Openメソッドに先ほど用意したSELECTを実行します。
ちなみに、コードだけではSELECT文が分かりにくいと思うので、実際に実行するSQLのSELECT文をサンプルで以下にお見せします。
条件は「temp」です。
select
*
from
[data_output.csv]
where
F3 = 'temp'
order by
F4 desc
select
*
from
[data_output.csv]
where
F3 like '%temp%'
order by
F4 desc
select
*
from
[data_output.csv]
where
F3 like 'temp%'
order by
F4 desc
select
*
from
[data_output.csv]
where
F3 like '%temp'
order by
F4 desc
注目すべきコード⑧
次に見て頂きたいのは166行目から180行目です。
With Worksheets(dtSheetNM)
'シート「data」のセル全てをクリアする
.Cells.Clear
'listboxのヘッダに表示させる項目名をセルに設定する
.Range("A1").Value = "項番"
.Range("B1").Value = "種類"
.Range("C1").Value = "フォルダ・ファイルパス"
.Range("D1").Value = "フォルダ・ファイル名"
.Range("E1").Value = "ファイルサイズ(MB)"
.Range("F1").Value = "ファイル作成日時"
.Range("G1").Value = "ファイル更新日時"
End With
コードの説明
以上のコードは、CSVのデータを「data」のシートに貼り付けるのに、「data」のシートをクリアしてヘッダ部分を1行目に出力するコードです。
コードの詳細
169行目のコードでは、シート「data」をクリアします。
172行目から178行目では、A列からG列の1行目に、「項番」「種類」「フォルダ・ファイルパス」「フォルダ・ファイル名」「ファイルサイズ(MB)」「ファイル作成日時」「ファイル更新日時」の文字列を出力します。
注目すべきコード⑨
次に見て頂きたいのは182行目から199行目です。
If oRS.RecordCount = 0 Then
'検索データが存在しない場合
'ListFillRangeにヘッダ部とその下の行のセルを参照させて、Listboxにヘッダ部分だけを表示させる
ListBox1.ListFillRange = dtSheetNM & "!$A$2:$D$2"
'後処理
oRS.Close
Set oShell = Nothing
Set adoCON = Nothing
Set oRS = Nothing
MsgBox "検索データがありません。"
'処理を終了する
Exit Sub
コードの説明
以上のコードは、データ件数が0件の場合のListboxの表示について設定を行う処理のコードです。
コードの詳細
182行目のコードは、SELECT文を実行した結果0件かどうかを判定するIF文です。
もし0件の場合は、187行目でListFillRangeにヘッダ部とその下の行のセルを参照させて、Listboxにヘッダ部分だけを表示させます。
データ件数が0件なので、196行目のコードでは「検索データがありません。」とメッセージ表示させて199行目で本サプルーチンを終了させます。
注目すべきコード⑩
次に見て頂きたいのは201行目から218行目です。
ElseIf oRS.RecordCount > rowMaxCnt Then
'検索データの件数がExcelの最大行数を超えている場合
'ListFillRangeにヘッダ部とその下の行のセルを参照させて、Listboxにヘッダ部分だけを表示させる
ListBox1.ListFillRange = dtSheetNM & "!$A$2:$D$2"
'後処理
oRS.Close
Set oShell = Nothing
Set adoCON = Nothing
Set oRS = Nothing
MsgBox "検索データの件数がExcelの最大行数を超えているので処理を終了します。"
'処理を終了する
Exit Sub
コードの説明
以上のコードは、データ件数がExcelのシートの行数を超えた場合のListboxの表示について設定を行う処理のコードです。
コードの詳細
201行目のコードは、SELECT文を実行した結果データ件数がExcelのシートの行数を超えているかどうかを判定するIF文です。
もしデータ件数がExcelのシートの行数(1048576)を超えている場合は、206行目でListFillRangeにヘッダ部とその下の行のセルを参照させて、Listboxにヘッダ部分だけを表示させます。
データ件数がExcelのシートの行数を超えているので、215行目のコードでは「検索データの件数がExcelの最大行数を超えているので処理を終了します。」とメッセージ表示させて218行目で本サプルーチンを終了させます。
注目すべきコード⑪
次に見て頂きたいのは222行目から246行目です。
With Worksheets(dtSheetNM)
'テーブルデータをシートに貼り付ける
.Range("B2").CopyFromRecordset oRS
'シート「data」を選択する
.Select
'表のA列の先頭行にROW関数を使って項番を振る
.Range("A" & 2).FormulaR1C1 = "=ROW()-1"
'ROW関数を使ったセルをコピーする
.Range("A2").Copy
'データが出力された行位置までA列のセルを選択する
.Range("A2:A" & (2 + CLng(oRS.RecordCount) - 1)).Select
'選択されたセルに、数式のみをセルに貼り付ける
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'表のA列の先頭のセルをを選択する
.Range("A1").Select
End With
コードの説明
以上のコードは、CSVファイルのデータをシート「data」に出力する処理のコードです。
コードの詳細
225行目のコードで、CSVファイルのデータをシート「data」に出力します。
CSVファイルのデータはrecordsetに格納されており、CopyFromRecordsetプロパティにoRSをセットすることでB2のセルにrecordsetに格納されているCSVファイルのデータが出力されます。
231行目のコードで、ROW関数を使ってセルの行位置を取得し取得してセルに出力しています。
234行目では、231行目で計算式を入れたセルをコピーしています。
このコピーしたセルを、データが入っている行まで選択して貼り付けます。
選択は237行目で、貼り付けは240行目と241行目で行っています。
以上の処理を行ったシート「data」の状態は次の通りになります。
注目すべきコード⑫
次に見て頂きたいのは251行目から265行目です。
With ListBox1
'Listboxの表示するデータのセル範囲を指定する
.ListFillRange = dtSheetNM & "!$A$2:$G$" & CLng(oRS.RecordCount) + 1
'ヘッダを表示させる
.ColumnHeads = True
'Listboxの列数を設定する
.ColumnCount = 7
'Listboxの列の幅を設定する
.ColumnWidths = "40;50;300;150;120;130"
End With
コードの説明
以上のコードは、シート「data」のデータをListboxに表示させるのに必要な設定を行う処理のコードです。
コードの詳細
254行目のコードでは、シート「data」のデータをListboxに表示させるためのプロパティListFillRangeに、データが出力されているセルの範囲を設定します。
257行目のコードでは、ColumnHeadsプロパティにTrueを設定することで、Listboxにヘッダが表示されるようになります。
260行目のコードでは、ColumnCountにデータの列数を指定します。
今回はデータが7列あるので7を指定します。
263行目のコードでは、Listboxにデータを表示させる列の幅をColumnWidthsに設定します。
【注意】CSVファイルのデータを正常にExcelのシートに読み込ませるには「schema.ini」が必要
今回のExcelのサンプルでは、CSVファイルのデータを正常にExcelのシートに読み込ませるには「schema.ini」が必要です。
なぜ「schema.ini」が必要
なぜ「schema.ini」が必要なのかその理由は次の通りです。
- utf-8のCSVファイルのデータをExcelのシートに出力する時に文字化けしないようにするため
【理由】utf-8のCSVファイルのデータをExcelのシートに出力する時に文字化けしないようにするため
「schema.ini」が必要な理由は、utf-8のCSVファイルのデータをExcelのシートに出力する時に文字化けしないようにするためです。
もし「schema.ini」がない場合にutf-8のCSVファイルのデータをExcelのシートに出力すると、次の通りに全角文字が文字化けします。
以上のように、utf-8のCSVファイルのデータをExcelのシートに出力する際の文字化け対策として「schema.ini」を利用しています。
「schema.ini」とはいったいどんなファイル？
「schema.ini」とはいったいどんなファイルなのかと言うと、テーブル定義情報を定義するファイルです。
テーブル定義というと「CSVファイルのデータで話を進めているのになんでテーブル？データベースじゃないのに？」と思われるかもしれませんが、CSVファイルをデータベースに、CSVファイルのデータをデータベースのテーブルデータに置き換えてイメージしてみてください。
この「schema.ini」に、CSVファイル内のデータに関するスキーマ情報を記述します。
なお、この「schema.ini」内で定義した情報はExcelやVBA側では定義することはできません。
スキーマ情報とは何かというと、CSVファイル名やファイル形式、文字コード(Shift-JISやUTF-8など)を設定する設定値です。
ではどのように設定するのかというと、次のように「schema.ini」にスキーマ情報を記述します。(サンプル)
[data_output.csv] ;【ファイル形式】CSVファイル形式 Format=CSVDelimited ;【文字コード】UTF-8(65001)に設定 CharacterSet=65001 ;【読み込む行数】0はファイル全体を対象 MaxScanRows=0 ;【ヘッダ有無】ヘッダなしのCSVファイルはFALSE ColNameHeader=True :【列のデータの形式】 Col1=F1 Text Width 1000 :【列のデータの形式】 Col2=F2 Text Width 1000 :【列のデータの形式】 Col3=F3 Text Width 1000 :【列のデータの形式】 Col4=F4 Decimal :【列のデータの形式】 Col5=F5 Text Width 1000 :【列のデータの形式】 Col6=F6 Text Width 1000
「;」は定義文をコメントアウトするのに使う文字です。
定義文の先頭に「;」を付けておくと、その行は無視されます。
1行目：Excelのシートに出力する元のCSVファイルの名称
1行目は、Excelのシートに出力する元のCSVファイルの名称の記述です。
[data_output.csv]
今回は「data_output.csv」というCSVファイルのデータをExcelのシートに出力するため、data_output.csvをかっこ([])で囲んで指定しています。
4行目：Formatの定義
4行目は、CSVファイルのデータがカンマ区切りであることを知らせるための定義です。
;【ファイル形式】CSVファイル形式 Format=CSVDelimited
7行目：CharacterSetの定義
7行目は、マクロが読み込むCSVファイルがUTF-8形式である場合に必要な定義です。
;【文字コード】UTF-8(65001)に設定 CharacterSet=65001
「65001」はUTF-8を指します。
CharacterSetに「65001」を指定することで、UTF-8形式のCSVファイルのデータをExcelのシートに出力する際、正常に(文字化けせずに)出力されます。
このCharacterSetの定義がないと、Excelのシートに出力する際に文字化けします。
10行目：MaxScanRowsの定義
10行目は、指定した数値だけレコードを読み取ってデータ型を判定するのに必要な定義です。
;【読み込む行数】0はファイル全体を対象 MaxScanRows=0
0を指定すると、全てのレコードを読み取ります。
13行目：ColNameHeaderの定義
13行目は、テキストファイルの最初の行を列名として扱うかどうかを指定する定義です。
;【ヘッダ有無】ヘッダなしのCSVファイルはFALSE ColNameHeader=TRUE
FALSEを設定すると、最初の行をフィールド名として扱いません。
TRUEなら、最初の行をフィールド名として扱います。
今回のサンプルで扱うCSVファイルの最初の行は列名から始まっているのでTRUEを設定しています。
16行目から22行目：データの定義
16行目から22行目は、データの定義を行っています。
:【列のデータの形式】 Col1=F1 Text Width 1000 :【列のデータの形式】 Col2=F2 Text Width 1000 :【列のデータの形式】 Col3=F3 Text Width 1000 :【列のデータの形式】 Col4=F4 Decimal :【列のデータの形式】 Col5=F5 Text Width 1000 :【列のデータの形式】 Col6=F6 Text Width 1000
Col1、Col2、Col3、Col4、Col5、Col6の6つの定義は、今回のサンプルで扱う、抜き出されたデータのCSVファイルが6列あるので6つ定義しています。
この6つの列の定義の中身は、1、2、3、5、6列目がテキスト型(「種類」「ファイルパス」「ファイル名」「ファイル作成日時」「ファイル更新日時」)で、4列目が数値型(「ファイルサイズ(MB)」)です。
テキスト型
1つ目の値
1つ目の値には列名を指定します。
ただし、厳密に値を指定しなければならないというわけではなく、任意の文字列で構いません。
今回のサンプルでは、適当に分かりやすく「F1」という文字列を指定しています。(FはFieldの頭文字のF)
2つ目の値
2つ目の値には、データの型を指定しています。
今回は文字列で扱いたいので「Text」の文字列を指定しています。
3つ目と4つ目の値
3つ目と4つ目の値には、文字列の長さを指定します。
文字列の長さは4つ目の値で、3つ目の「Width」は固定値です。
つまり3つ目に「Width」と記述してその後に文字列の長さの値を指定します。
数値型
1つ目の値
1つ目の値には列名を指定します。
ただし、厳密に値を指定しなければならないというわけではなく、任意の文字列で構いません。
今回のサンプルでは、適当に分かりやすく「F1」という文字列を指定しています。(FはFieldの頭文字のF)
2つ目の値
2つ目の値には、データの型を指定しています。
データの型が数値型の場合は「Decimal」の文字列を指定しています。
「schema.ini」はどこに置くか
「schema.ini」はどこに置くかというと、読み込むCSVファイルと同じ場所に置きます。
「schema.ini」ってどうやって使うの？
「schema.ini」は、読み込むCSVファイルと同じ場所に置いておけば、勝手に「schema.ini」の中身をマクロが読みこんでくれます。
マクロを実行するだけで特に何も操作する必要はありません。
ここまでで説明した以下の2点を正しく行うことでマクロが「schema.ini」の中身を読み取って動作します。
- 「schema.ini」の設定(文字コードの設定、CSVファイル名の指定など)を正しく行い作成・保存する
- 読み込むCSVファイルと同じ場所に置くこと
「schema.ini」の設定・配置を正しく行いCSVファイルのデータをRecordsetで取得してExcelのシートに出力するマクロを実行すると、文字化けせずデータが正しくExcelのシートに出力されます。
動作確認
今回は以下のExcelファイルを用意しました。
【ケース①：完全一致】マクロ実行前
以下の通りに設定しました。
- 「完全一致」を選択
- 検索対象パスに「C:\work\10_勉強\10_VBA関連\0239\file」を入力
- 検索対象のフォルダ／ファイルに「temp.txt」を入力
- 「フォルダ／ファイル一覧を生成する」にチェックを付ける
「C:\work\10_勉強\10_VBA関連\0234\file」の中身は下の通りです。
【ケース①：完全一致】マクロ実行後
「temp.txt」のファイル名がListboxに出力されました。
【ケース②：部分一致】マクロ実行前
以下の通りに設定しました。
- 「部分一致」を選択
- 検索対象パスに「C:\work\10_勉強\10_VBA関連\0239\file」を入力
- 検索対象のフォルダ／ファイルに「temp」を入力
- 「フォルダ／ファイル一覧を生成する」にチェックを付ける
「C:\work\10_勉強\10_VBA関連\0234\file」の中身は下の通りです。
【ケース②：部分一致】マクロ実行後
「temp」の文字列が含まれるファイル名がListboxに出力されました。
【ケース③：前方一致】マクロ実行前
以下の通りに設定しました。
- 「前方一致」を選択
- 検索対象パスに「C:\work\10_勉強\10_VBA関連\0234\file」を入力
- 検索対象のフォルダ／ファイルに「temp」を入力
- 「フォルダ／ファイル一覧を生成する」にチェックを付ける
「C:\work\10_勉強\10_VBA関連\0234\file」の中身は下の通りです。
【ケース③：前方一致】マクロ実行後
「temp」の文字列が先頭に含まれるファイルがListboxに出力されました。
【ケース④：後方一致】マクロ実行前
以下の通りに設定しました。
- 「後方一致」を選択
- 検索対象パスに「C:\work\10_勉強\10_VBA関連\0234\file」を入力
- 検索対象のフォルダ／ファイルに「temp.txt」を入力
- 「フォルダ／ファイル一覧を生成する」にチェックを付ける
「C:\work\10_勉強\10_VBA関連\0234\file」の中身は下の通りです。
【ケース④：後方一致】マクロ実行後
「temp.txt」の文字列が後方に含まれるファイルがListboxに出力されました。
「フォルダ／ファイル一覧を生成する」にチェックを付けないでマクロを実行する
本マクロは、フォルダ／ファイル一覧のCSVファイル「data_output.csv」を参照・抽出してそのデータをListboxに表示するので、フォルダ／ファイル一覧のCSVファイル「data_output.csv」がない状態で「フォルダ／ファイル一覧を生成する」にチェックを付けないでマクロを実行してもListboxにフォルダ名やファイル名を表示させることはできません。
以下のようにメッセージボックスを表示させて、マクロを終了させます。
一度「フォルダ／ファイル一覧を生成する」にチェックを付けてフォルダ／ファイル一覧のCSVファイル「data_output.csv」を生成してから「フォルダ／ファイル一覧を生成する」にチェックを付けないでマクロを実行します。
【ケース①：完全一致】マクロ実行前
以下の通りに設定しました。
- 「完全一致」を選択
- 検索対象パスは適当で(検索対象パスは参照しません※フォルダ／ファイル一覧のCSVファイル「data_output.csv」を参照するため)
- 検索対象のフォルダ／ファイルに「temp.txt」を入力
- 「フォルダ／ファイル一覧を生成する」にチェックを付けない
【ケース①：完全一致】マクロ実行後
「temp.txt」のファイル名がListboxに出力されました。
【ケース②：部分一致】マクロ実行前
以下の通りに設定しました。
- 「部分一致」を選択
- 検索対象パスは適当で(検索対象パスは参照しません※フォルダ／ファイル一覧のCSVファイル「data_output.csv」を参照するため)
- 検索対象のフォルダ／ファイルに「temp」を入力
- 「フォルダ／ファイル一覧を生成する」にチェックを付ける
【ケース②：部分一致】マクロ実行後
「temp」の文字列が含まれるファイル名がListboxに出力されました。
【ケース③：前方一致】マクロ実行前
以下の通りに設定しました。
- 「前方一致」を選択
- 検索対象パスは適当で(検索対象パスは参照しません※フォルダ／ファイル一覧のCSVファイル「data_output.csv」を参照するため)
- 検索対象のフォルダ／ファイルに「temp」を入力
- 「フォルダ／ファイル一覧を生成する」にチェックを付ける
【ケース③：前方一致】マクロ実行後
「temp」の文字列が先頭に含まれるファイルがListboxに出力されました。
【ケース④：後方一致】マクロ実行前
以下の通りに設定しました。
- 「後方一致」を選択
- 検索対象パスは適当で(検索対象パスは参照しません※フォルダ／ファイル一覧のCSVファイル「data_output.csv」を参照するため)
- 検索対象のフォルダ／ファイルに「temp.txt」を入力
- 「フォルダ／ファイル一覧を生成する」にチェックを付ける
【ケース④：後方一致】マクロ実行後
「temp.txt」の文字列が後方に含まれるファイルがListboxに出力されました。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの12行目の「ADODB.Connection」と13行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim adoCON As ADODB.Connection 'Connection用変数
Dim oRS As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、Excelのマクロ・PowerShellを使ってパソコン内のフォルダとファイルを検索する方法についてご説明しました。
パソコン内のフォルダやファイルがどこにあるのか探したいときは本記事を参考にしてみてくださいね。
なお、似たようなマクロを下の記事でも紹介しているのでそちらも参考にしてみてくださいね。
【ExcelVBA】Excelのマクロ・PowerShellを使ってパソコン内のフォルダとファイルを検索する(DBは使用しない)
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
