この記事では、マクロ・コマンドプロンプト・SQL Serverのストアドプロシージャを使ってパソコン内のフォルダとファイルを検索する方法についてご説明します。
検索した結果は、シート上に設置されたListboxに表示します。
【動画】マクロ・コマンドプロンプト・SQL Serverのストアドプロシージャを使ってパソコン内のフォルダとファイルを検索する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
指定したフォルダからファイルとフォルダのフルパスをCSVに出力してストアドプロシージャでSQL Serverのテーブルに追加。
追加したファイルとフォルダをマクロが取得してListboxに表示しています。
各名称について
今回の説明で使用するSQL Serverのデータベースやテーブル、ストアドプロシージャなどの名称は次の通りです。
- データベース：projDB
- テーブル：tbl_folderfile_list
- ストアドプロシージャ：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のストアドプロシージャを使ってパソコン内のフォルダとファイルを検索する処理の流れは次の通りです。
処理の流れ
取得したフォルダ・ファイル一覧はCSVに出力します。
マクロを実行する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のマクロの作成の流れ
すでにシートが存在する場合は何もしません。
ストアドプロシージャの作成の流れ
コードの例
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
- ①のコマンドは、文字コードをUTF-8に設定するコマンドで、文字化けしないように文字コードをUTF-8に設定しています。
(文字コードをUTF-8に設定すると文字化けしない) - ②のコマンドは、dirコマンドを実行して、サブフォルダ含めて全ファイルと全フォルダの一覧をファイル「data.csv」に出力しています。
以上、上記のコマンドが実行されると、フォルダ名とファイル名が出力された「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文の内容は次の通りです。
- 通番：ROW_NUMBER() OVER(ORDER BY dirPath ASC) infoNo
- パス：LEFT(dirPath, LEN(dirPath) – CHARINDEX(”\”, REVERSE(dirPath))) dirPath
- フォルダ名・ファイル名：RIGHT(dirPath, CHARINDEX(”\”, REVERSE(dirPath)) – 1) fName
通番
データの通番です。
パス
フォルダ・ファイルのフルパスから取得したパスです。
フォルダ名・ファイル名
フォルダ・ファイルのフルパスから取得したフォルダ名・ファイル名です。
動作確認
マクロを実行した実行結果は記事内の次のコントロールの内容をご覧ください(クリックすると記事内の対象の説明に飛びます。)
- 「完全一致」(のオプションボタン(opb_allmatch))が選択された状態でマクロを実行した場合
- 「部分一致」(のオプションボタン(opb_partmatch))が選択された状態でマクロを実行した場合
- 「前方一致」(のオプションボタン(opb_fwdmatch))が選択された状態でマクロを実行した場合
- 「後方一致」(のオプションボタン(opb_rearmatch))が選択された状態でマクロを実行した場合
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Windows Script Host Object Model(wshom.ocx)
- 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のストアドプロシージャを使ってパソコン内のフォルダとファイルを検索する方法についてご説明しました。
処理の流れをおさらいしておくと次の通りです。
取得したフォルダ・ファイル一覧はCSVに出力します。
フォルダ名・ファイル名を検索する対象の、サブフォルダ含めた格納先配下のフォルダ・ファイル全てを、dirコマンドをコマンドプロンプト上で実行して取得します。
取得したフルパスの一覧からフォルダ名・ファイル名を取り出してテーブルに追加します。
追加したフォルダ名・ファイル名の中から、マクロが検索するフォルダ名・ファイル名をselect文で取り出しListboxに表示させます。
以上で、検索したパソコン内のフォルダとファイルを見つけることができます。
パソコン内のフォルダとファイルを検索したい時は参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
