この記事では、指定したファイルサイズに合致するファイルを検索してListboxに一覧表示する方法についてご説明します。
【動画】指定したファイルサイズに合致するファイルを検索してListboxに一覧表示する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
フォルダ配下すべてのファイルに対し、指定したファイルサイズのファイル一覧をCSVファイルに出力・保存するPowerShellのコマンドをPowerShell側で実行させます。
生成されたCSVファイルをマクロが読み込んで、Listboxに一覧表示させています。
なぜPowerShellを利用するのか？
今回のマクロではPowerShellを利用しています。なぜPowerShellを利用するのかというと、マクロだけでは処理が完了するのに時間がかかるため、他の代替方法としてPowerShellを採用しています。
PowerShellを使うと、大量のファイルを処理するのに処理を完了するのに時間を抑えることができます。
参考までに、扱うファイルの数(とフォルダの数)ごとのマクロの処理時間を以下に紹介します。
| パス | パス内のファイルとフォルダ数 | 処理時間 | |
| 1 | C:\work\10_勉強\10_VBA関連\0227\file | ファイル数: 9、フォルダー数: 3 | 0:00:01 |
| 2 | C:\work\10_勉強\10_VBA関連\0227 | ファイル数: 73、フォルダー数: 7 | 0:00:02 |
| 3 | C:\work\10_勉強\10_VBA関連 | ファイル数: 10,548、フォルダー数: 103,638 | 0:00:45 |
| 4 | C:\work\10_勉強 | ファイル数: 15,512、フォルダー数: 106,174 | 0:00:46 |
| 5 | C:\work | ファイル数: 81,968、フォルダー数: 141,082 | 0:01:00 |
| 6 | C:\ | ファイル数: 2,638,851、フォルダー数: 612,882 | 0:08:56 |
上記の結果はあくまで参考値として見て頂きたいのですが、高速化に対応しているほうが、データ件数が多くても処理時間をかなり抑えることができます。
マクロ作成の流れ
Excelファイルの例
今回は次のExcelファイルを用意しました。
searchpath
フォルダやファイルを探す対象のフォルダパスを指定するセルに、「searchpath」という名前を付けています。
例えば、Cドライブ配下全てでフォルダやファイルを検索する場合は「c:\」または「c:」と入力します。
minFSize
検索するファイルの最小サイズを指定するセルに、「minFSize」という名前を付けています。
たとえば20メガバイトよりも大きいファイルを検索したい場合は「20」と入力します。
maxFSize
検索するファイルの最大サイズを指定するセルに、「maxFSize」という名前を付けています。
たとえば500メガバイトよりも小さいファイルを検索したい場合は「500」と入力します。
listbox1(ActiveXコントロール)
listboxに検索結果を表示させます。
コードの例
Excelのマクロのコード(例)
Option Explicit
Private Sub btn_exec_Click()
Dim ws As Worksheet 'Worksheet用変数
Dim outPutCSVFile As String '出力するCSVファイルのフルパス用変数
Dim checkDir As String '確認したいファイルが存在するフォルダのパス
Dim maxFSize As Long '最大ファイルサイズ
Dim minFSize As Long '最小ファイルサイズ
Dim strCmd As String 'コマンド用変数
Dim sqlStr As String 'SQL文
Dim oShell As Object 'WshShellオブジェクト用変数
Dim adoCON As ADODB.Connection 'Connection用変数
Dim oRS As ADODB.Recordset 'レコードセット用変数
Dim rcdCnt As Long 'レコード件数
Dim shtExistFlg As Boolean 'シート存在確認フラグ
'読み込んだCSVファイルのデータを出力する行の先頭位置
Const bgnRowPos As Long = 3
'SQL Serverのテーブルから取得したデータを貼り付けるシート名
Const dtSheetNM As String = "data"
'Excelの最大行数
Const rowMaxCnt As Long = 1048575
'出力するCSVファイル名
Const outPutCSVFileNM As String = "data_output.csv"
'出力するCSVファイル名を取得する(保存先はマクロのExcelファイルと同じ場所とする)
outPutCSVFile = ThisWorkbook.Path & "\" & outPutCSVFileNM
'本マクロのブックのシート名を取得する
Set ws = Worksheets("top")
'確認したいファイルが存在するフォルダのパス
checkDir = ws.Range("searchpath").Value
'検索するファイルサイズの最大値
maxFSize = ws.Range("maxFSize").Value
'検索するファイルサイズの最小値
minFSize = ws.Range("minFSize").Value
'フォルダ名とファイル名貼り付け用シート有無のチェック
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ファイルに書き込むためのPowrShellコマンド文を取得する
strCmd = "powershell.exe -Command ""Get-ChildItem -Path '" & checkDir & "' -Recurse | " & _
"Where-Object {!$_.PSIsContainer -and $_.Length -ge " & _
minFSize & "MB -and $_.Length -le " & maxFSize & "MB} | " & _
"Select-Object @{Name='FilePath'; Expression={$_.DirectoryName}}, Name, " & _
"@{Name='FileSize (MB)'; Expression={($_.Length / 1MB)}} | " & _
"Export-Csv -Path '" & outPutCSVFile & "' -Encoding UTF8 -NoTypeInformation"""
'WshShellオブジェクト用インスタンスを生成する
Set oShell = CreateObject("WScript.Shell")
'PowerShellを呼び出してコマンドを実行する
oShell.Run strCmd, 0, True
'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 & " F1, F2, F3"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & outPutCSVFileNM & "]"
sqlStr = sqlStr & " order by F3 desc"
'Recordsetオブジェクトのインスタンスを生成する
Set oRS = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
oRS.CursorType = adOpenDynamic
'SELECT文を実行してRecordsetを開く
oRS.Open sqlStr, adoCON, adOpenStatic
If oRS.RecordCount = 0 Then
'検索データが存在しない場合
With Worksheets(dtSheetNM)
'シート「data」のセル全てをクリアする
.Cells.Clear
'listboxのヘッダに表示させる項目名をセルに設定する
.Range("A1").Value = "項番"
.Range("B1").Value = "ファイルパス"
.Range("C1").Value = "ファイル名"
.Range("D1").Value = "ファイルサイズ"
'Listboxの表示(ListFillRangeプロパティ)をクリアする
ListBox1.ListFillRange = dtSheetNM & "!$A$2:$D$2"
End With
'後処理
oRS.Close
Set oShell = Nothing
Set adoCON = Nothing
Set oRS = Nothing
MsgBox "検索データがありません。"
'処理を終了する
Exit Sub
ElseIf oRS.RecordCount > rowMaxCnt Then
'検索データの件数がExcelの最大行数を超えている場合
With Worksheets(dtSheetNM)
'シート「data」のセル全てをクリアする
.Cells.Clear
'listboxのヘッダに表示させる項目名をセルに設定する
.Range("A1").Value = "項番"
.Range("B1").Value = "ファイルパス"
.Range("C1").Value = "ファイル名"
.Range("D1").Value = "ファイルサイズ"
'Listboxの表示(ListFillRangeプロパティ)をクリアする
ListBox1.ListFillRange = dtSheetNM & "!$A$2:$D$2"
End With
'後処理
oRS.Close
Set oShell = Nothing
Set adoCON = Nothing
Set oRS = Nothing
MsgBox "検索データの件数がExcelの最大行数を超えているので処理を終了します。"
'処理を終了する
Exit Sub
End If
With Worksheets(dtSheetNM)
'listboxのヘッダに表示させる項目名をセルに設定する
.Range("A1").Value = "項番"
.Range("B1").Value = "ファイルパス"
.Range("C1").Value = "ファイル名"
.Range("D1").Value = "ファイルサイズ"
'テーブルデータをシートに貼り付ける
.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:$D$" & CLng(oRS.RecordCount) + 1
'ヘッダを表示させる
.ColumnHeads = True
'Listboxの列数を設定する
.ColumnCount = 4
'Listboxの列の幅を設定する
.ColumnWidths = "50;300;200"
End With
'後処理
oRS.Close
Set oShell = Nothing
Set adoCON = Nothing
Set oRS = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは29行目から32行目です。
'出力するCSVファイル名
Const outPutCSVFileNM As String = "data_output.csv"
'出力するCSVファイル名を取得する(保存先はマクロのExcelファイルと同じ場所とする)
outPutCSVFile = ThisWorkbook.Path & "\" & outPutCSVFileNM
コードの説明
以上のコードは、フォルダ配下すべてのファイルに対し、指定したファイルサイズのファイル一覧をPowerShellが出力して保存するCSVファイルの名前を取得するコードです。
今回のサンプルではCSVファイルのファイル名は「data_output.csv」としています。
生成する「data_output.csv」の生成先はThisWorkbook.Pathを使って、本マクロの置き場と同じで指定しています。
注目すべきコード②
次に見て頂きたいのは38行目から44行目です。
'確認したいファイルが存在するフォルダのパス
checkDir = ws.Range("searchpath").Value
'検索するファイルサイズの最大値
maxFSize = ws.Range("maxFSize").Value
'検索するファイルサイズの最小値
minFSize = ws.Range("minFSize").Value
コードの説明
以上のコードは、確認したいファイルが存在するフォルダのパス、検索するファイルサイズの最大値、検索するファイルサイズの最小値のそれぞれの値を取得する処理のコードです。
コードの詳細
38行目のコードでは、確認したいファイルが存在するフォルダのパスを取得して変数checkDirに格納します。
確認したいファイルが存在するフォルダのパスは下の画像の通りに入力された値です。
41行目のコードでは、検索するファイルサイズの最大値を取得して変数maxFSizeに格納します。
検索するファイルサイズの最大値は、下の画像の通りに入力された値です。
44行目のコードでは、検索するファイルサイズの最小値を取得して変数minFSizeに格納します。
検索するファイルサイズの最小値は、下の画像の通りに入力された値です。
注目すべきコード③
次に見て頂きたいのは47行目から75行目です。
'フォルダ名とファイル名貼り付け用シート有無のチェック
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ファイルに存在するかを判定し、無い場合に生成します。
コードの詳細
47行目のコードでは、Excelファイル内にあるシートを一つ一つ取得するためのFor文を用意しています。
もしExcelファイルに3つシートが存在していれば、For文内を3回ループします。
For文をループする中で、取得したシート名とシート名「data」(定数dtSheetNMの値)が一致するかを49行目で判定します。
もし一致すれば、Excelファイルの中にシート「data」が存在していることが分かったので、一旦54行目で変数shtExistFlgにTrueを設定します。
60行目で変数shtExistFlgの値を確認し、Falseの場合(シート「data」が存在しない場合)は、65行目と66行目でシート「data」を生成します。
Trueの場合はシート「data」が存在しているので、そのシート「data」をクリアします。(CSVファイルのデータを出力するため)
注目すべきコード④
次に見て頂きたいのは78行目から83行目です。
'サイズの指定を条件にパソコン内のファイルを検索・出力してその出力内容をCSVファイルに書き込むためのPowrShellコマンド文を取得する
strCmd = "powershell.exe -Command ""Get-ChildItem -Path '" & checkDir & "' -Recurse | " & _
"Where-Object {!$_.PSIsContainer -and $_.Length -ge " & _
minFSize & "MB -and $_.Length -le " & maxFSize & "MB} | " & _
"Select-Object @{Name='FilePath'; Expression={$_.DirectoryName}}, Name, " & _
"@{Name='FileSize (MB)'; Expression={($_.Length / 1MB)}} | " & _
"Export-Csv -Path '" & outPutCSVFile & "' -Encoding UTF8 -NoTypeInformation"""
以上は、サイズの指定を条件にパソコン内のファイルを検索・出力してその出力内容をCSVファイルに書き込むためのPowerShellのコマンド文です。
PowerShellのコマンドの説明
PowerShellのコマンドを次に詳しく説明します。
①「powershell.exe」
「powershell.exe」は、PowerShellの実行ファイルを指します。
PowerShellの実行ファイルを呼び出し、「PowerShell」の文字列の後に続くコマンド文を実行します。
②「-command」
「-command」は、PowerShellのコマンドラインオプションの1つです。
この「-command」を使うことでPowerShellスクリプトを実行することができます。
③「”Get-ChildItem -Path ‘” & checkDir & “’ -Recurse」
「Get-ChildItem」は、指定したパスのフォルダ内の情報を取得するコマンドレットです。
「-Recurse」は、「Get-ChildItem」で情報を取得するのにサブディレクトリ内のすべてのファイルとフォルダを取得するようにするオプションです。
④「Where-Object {!$_.PSIsContainer -and $_.Length -ge ” & minFSize & “MB -and $_.Length -le ” & maxFSize & “MB}」
「Where-Object」は、指定された条件に一致するオブジェクトのみを取得するコマンドレットです。
「PSIsContainer」はフォルダのことを指し、「!$_.PSIsContainer」と表記することでフォルダ(コンテナ)は除外することを意味します。
今回はファイルのサイズが欲しいのでフォルダを除外するため「!$_.PSIsContainer」と記述しています。
「$_.Length -ge ” & minFSize & “MB」はファイルサイズがminFSize以上であること、「$_.Length -le ” & maxFSize & “MB」はファイルサイズがmaxFSize以下であることの条件文です。
⑤「Select-Object」
「Select-Object」とは、オブジェクトのプロパティを選択してCSVファイルからデータを抽出するために使います。
この「Select-Object」コマンドレットの引数に、どの項目の値を出力するのかを指定します。
今回は、ファイルのパス、ファイル名、ファイルサイズ(MB単位)を出力します。
⑥「DirectoryName, Name, @{Name=’FileSize (MB)’; Expression={($_.Length / 1MB)}}」
DirectoryNameプロパティは、ファイルのパスを取得します。
Nameプロパティは、ファイル名を取得します。
「@{Name=’FileSize (MB)’; Expression={($_.Length / 1MB)}}」は、Lengthプロパティを1MB単位で出力したファイルサイズを取得します。
「FileSize (MB)」という文字列をNameで指定することで、この「FileSize (MB)」という文字列がCSVファイル内でヘッダとして出力されます。
⑦Export-Csv -Path ‘” & outPutCSVFile & “‘
「Export-Csv」は出力したコマンドの結果をCSVファイルに出力・保存するコマンドレットです。
⑧「-Encoding UTF8」
「-Encoding UTF8」は、出力するCSVファイルのエンコーディングをUTF-8で指定します。
「-Encoding」の引数にUTF-8を指定することで、データが文字化けせずにUTF-8形式のCSVファイルを出力することができます。
⑨-NoTypeInformation
「NoTypeInformation」オプションを付けると、ディレクトリ内のファイルとフォルダをリスト形式で出力します(オブジェクトの型に関する情報は出力しない)
注目すべきコード⑤
次に見て頂きたいのは86行目から89行目です。
'WshShellオブジェクト用インスタンスを生成する
Set oShell = CreateObject("WScript.Shell")
'PowerShellを呼び出してコマンドを実行する
oShell.Run strCmd, 0, True
コードの説明
以上のコードは、WshShellオブジェクト用インスタンスを生成し、「注目すべきコード④」で生成したPowerShellコマンドをPowerShell側で実行させます。(マクロがPowerShellを呼びだしてコマンドを実行)
Runメソッドの引数にコマンドを指定してRunメソッドを実行することで、PowerShellが呼び出されてコマンドが実行されます。
このコマンドが正常に実行されると、以下のようにCSVファイルが生成されます。
CSVファイルには、ファイルのパス、ファイル名、ファイルサイズ(MB単位)が出力されます。
注目すべきコード⑥
次に見て頂きたいのは92行目から103行目です。
'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に接続するための接続情報を取得して接続するコードです。
コードの詳細
92行目のコードでは、Connectionインスタンスを生成します。
このインスタンスがないとマクロがCSVファイルに接続することができないので必ず生成しておきます。
97行目と98行目で接続情報を取得し、101行目でOpenメソッドを実行してマクロがCSVファイルに接続します。
注目すべきコード⑦
次に見て頂きたいのは105行目から119行目です。
'データを取得するSQL文を作成する
sqlStr = "select"
sqlStr = sqlStr & " F1, F2, F3"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & outPutCSVFileNM & "]"
sqlStr = sqlStr & " order by F3 desc"
'Recordsetオブジェクトのインスタンスを生成する
Set oRS = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
oRS.CursorType = adOpenDynamic
'SELECT文を実行してRecordsetを開く
oRS.Open sqlStr, adoCON, adOpenStatic
コードの説明
以上のコードは、CSVファイルのデータを取得するSELECT文を用意し実行する処理のコードです。
コードの詳細
106行目から110行目までのコードは、CSVファイルのデータを取得するSELECT文を用意するコードです。
From句にはCSVファイルのファイル名を指定します。(outPutCSVFileNMには、29行目で取得したCSVファイル名が格納されています)
'出力するCSVファイル名
Const outPutCSVFileNM As String = "data_output.csv"
また、今回用意したSELECT文では、ファイルのサイズ順で降順(大きいもの順)に並ぶようにしています。(order by F3 desc)
なお、コードだけではSelect文が分かりにくいと思うので、実際に実行するSQLをサンプルで以下にお見せします。
select
F1, F2, F3
from
[data_output.csv]
order by F3 desc
なお、列名はそれぞれ「F1」「F2」「F3」と指定しています。
「F」の文字と何列目(1から始める)を結合させます。
1列目ならF1を、3列目ならF3と指定します。
113行目のコードでは、Recordsetオブジェクトのインスタンスを生成し、116行目でカーソルタイプにキーセットカーソル使用します。
119行目のコードでは、Openメソッドに先ほど用意したSELECTを実行します。
注目すべきコード⑧
最初に見て頂きたいのは121行目から151行目です。
If oRS.RecordCount = 0 Then
'検索データが存在しない場合
With Worksheets(dtSheetNM)
'シート「data」のセル全てをクリアする
.Cells.Clear
'listboxのヘッダに表示させる項目名をセルに設定する
.Range("A1").Value = "項番"
.Range("B1").Value = "ファイルパス"
.Range("C1").Value = "ファイル名"
.Range("D1").Value = "ファイルサイズ"
'Listboxの表示(ListFillRangeプロパティ)をクリアする
ListBox1.ListFillRange = dtSheetNM & "!$A$2:$D$2"
End With
'後処理
oRS.Close
Set oShell = Nothing
Set adoCON = Nothing
Set oRS = Nothing
MsgBox "検索データがありません。"
'処理を終了する
Exit Sub
コードの説明
以上のコードは、データ件数が0件の場合のListboxの表示について設定を行う処理のコードです。
コードの詳細
121行目のコードは、SELECT文を実行した結果0件かどうかを判定するIF文です。
もし0件の場合は、128行目でシート「data」のセル全てをクリアします。
131行目から134行目のコードで、「項番」「ファイルパス」「ファイル名」「ファイルサイズ」の文字列を1行目のAからD列に出力します。
137行目では、Listboxがシートの値を参照するListFillRangeプロパティにセルの範囲を設定します。
データの件数が0件なので、148行目で「検索データがありません。」とメッセージを出力し、151行目で本サブルーチンを終了します。(Exit Sub)
注目すべきコード⑨
次に見て頂きたいのは153行目から183行目です。
ElseIf oRS.RecordCount > rowMaxCnt Then
'検索データの件数がExcelの最大行数を超えている場合
With Worksheets(dtSheetNM)
'シート「data」のセル全てをクリアする
.Cells.Clear
'listboxのヘッダに表示させる項目名をセルに設定する
.Range("A1").Value = "項番"
.Range("B1").Value = "ファイルパス"
.Range("C1").Value = "ファイル名"
.Range("D1").Value = "ファイルサイズ"
'Listboxの表示(ListFillRangeプロパティ)をクリアする
ListBox1.ListFillRange = dtSheetNM & "!$A$2:$D$2"
End With
'後処理
oRS.Close
Set oShell = Nothing
Set adoCON = Nothing
Set oRS = Nothing
MsgBox "検索データの件数がExcelの最大行数を超えているので処理を終了します。"
'処理を終了する
Exit Sub
コードの説明
以上のコードは、データ件数がExcelのシートの行数を超えた場合のListboxの表示について設定を行う処理のコードです。
コードの説明
153行目のコードは、SELECT文を実行した結果データ件数がExcelのシートの行数を超えているかどうかを判定するIF文です。
もしデータ件数がExcelのシートの行数を超えている場合は、160行目でシート「data」のセル全てをクリアします。
163行目から166行目のコードで、「項番」「ファイルパス」「ファイル名」「ファイルサイズ」の文字列を1行目のAからD列に出力します。
169行目では、Listboxがシートの値を参照するListFillRangeプロパティにセルの範囲を設定します。
データ件数がExcelのシートの行数を超えているので、180行目で「検索データの件数がExcelの最大行数を超えているので処理を終了します。」とメッセージを出力し、183行目で本サブルーチンを終了します。
注目すべきコード⑩
次に見て頂きたいのは187行目から217行目です。
With Worksheets(dtSheetNM)
'listboxのヘッダに表示させる項目名をセルに設定する
.Range("A1").Value = "項番"
.Range("B1").Value = "ファイルパス"
.Range("C1").Value = "ファイル名"
.Range("D1").Value = "ファイルサイズ"
'テーブルデータをシートに貼り付ける
.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」に出力する処理のコードです。
コードの詳細
190行目から193行目のコードで、「項番」「ファイルパス」「ファイル名」「ファイルサイズ」の文字列を1行目のAからD列に出力します。
196行目のコードで、CSVファイルのデータをシート「data」に出力します。
202行目のコードで、ROW関数を使ってセルの行位置を取得し取得してセルに出力しています。
205行目では、202行目で計算式を入れたセルをコピーしています。
このコピーしたセルをデータが入っている行まで選択して貼り付けます。
選択は208行目で、貼り付けは211行目と212行目で行っています。
以上の処理を行ったシート「data」の状態は次の通りになります。
注目すべきコード⑪
次に見て頂きたいのは222行目から236行目です。
With ListBox1
'Listboxの表示するデータのセル範囲を指定する
.ListFillRange = dtSheetNM & "!$A$2:$D$" & CLng(oRS.RecordCount) + 1
'ヘッダを表示させる
.ColumnHeads = True
'Listboxの列数を設定する
.ColumnCount = 4
'Listboxの列の幅を設定する
.ColumnWidths = "50;300;200"
End With
コードの説明
以上のコードは、シート「data」のデータをListboxに表示させるのに必要な設定を行う処理のコードです。
コードの詳細
225行目のコードでは、シート「data」のデータをListboxに表示させるためのプロパティListFillRangeに、データが出力されているセルの範囲を設定します。
228行目のコードでは、ColumnHeadsプロパティにTrueを設定することで、Listboxにヘッダが表示されるようになります。
231行目のコードでは、ColumnCountにデータの列数を指定します。
データが4列ある場合は4を指定します。
234行目のコードでは、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 Decimal
「;」は定義文をコメントアウトするのに使う文字です。
定義文の先頭に「;」を付けておくと、その行は無視されます。
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 Decimal
Col1、Col2、Col3の3つの定義は、今回のサンプルで扱う、抜き出されたデータのCSVファイルが3列あるので3つ定義しています。
この3つの列の定義の中身は、1列目と2列目がテキスト型(「ファイルパス」と「ファイル名」)で、3列目が数値型(ファイルサイズ)です。
テキスト型
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関連\0227\file」を指定し、最小ファイルサイズに1を、最大ファイルサイズに500を指定しています。
ファイルサイズの単位はMB(メガバイト)です。
「C:\work\10_勉強\10_VBA関連\0227\file」の配下には「1」「2」「3」というフォルダが存在しており、その3つのフォルダの中身は次の通りです。
また、1MB以上500MB以下のファイルには赤枠で囲ってあります。
マクロ実行後
マクロを実行すると、1MB以上500MB以下のファイルがListboxに表示されています。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの13行目の「ADODB.Connection」と14行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim adoCON As ADODB.Connection 'Connection用変数
Dim oRS As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、行の位置を指定してCSVファイルからデータを抜き出しExcelのシートに出力する方法についてご説明しました。
ファイルサイズを指定してファイルを検索・特定したい場合は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
