この記事では、条件付き書式のデータバーを使ってサブフォルダ含めて全フォルダ内のファイルのサイズを横棒グラフに表示させる方法についてご説明します。
なお今回の記事は以下のマクロと似ています。
【ExcelVBA】サブフォルダ含めて全フォルダ内のファイルのサイズを横棒グラフに表示させるには(条件付き書式のデータバーを使用)
何が違うのかというと、ファイル名の取得を再帰処理を使うかPowerShellを使うのかの違いです。
上記の記事ではファイル名を取得するのに再帰処理を使っています。
それに対して今回のマクロではPowerShellを使っています。
ちなみに、今回扱う横棒グラフとは、条件付き書式のデータバーのことを指します。
下の画像が条件付き書式のデータバーが出力された結果です。
見た目は横棒グラフのように見えて、ファイルサイズの値の大小が確認しやすくなっているのが特徴です。
なお、今回のマクロは「schema.ini」というファイルが必要です。
「schema.ini」については「【注意】CSVファイルのデータを正常にExcelのシートに読み込ませるには「schema.ini」が必要」を参照してください。
【動画】条件付き書式のデータバーを使ってサブフォルダ含めて全フォルダ内のファイルのサイズを横棒グラフに表示させる実際の動き
本題に入る前に、まずは次の動画をご覧ください。
まずは指定されたトップディレクトリ配下にあるすべてのフォルダ内のファイルを、PowerShellを使ってすべて取得します。
次に、取得したファイルの名前とファイルサイズをExcelのシートに出力します。
ファイルサイズが出力されたセルの値を参照し、条件付き書式のデータバーを使ってセルの値を横棒グラフでE列に表示させています。
マクロ作成の流れ
今回のマクロでは次の設定を行います。
・データバーが表示されているセルには値を表示させない
・データバーの最大値(今回は100)と最小値(今回は0)を設定する
・データバーに色を設定する(今回はオレンジ色)
・データバーを塗りつぶし設定にする
Excelファイルの例
今回は次のExcelファイルを用意しました。
A2の黄色いセルに、ファイルサイズを取得したいファイルのトップフォルダのパスを入力して実行ボタンをクリックすると、下のとおりにフォルダにあるファイルサイズが横棒グラフで表示されます。
ちなみに、トップフォルダ配下にあるフォルダ内のファイルは下のとおりです。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim rowMax As Long 'Excelのシートの最大行数
Dim aryCnt As Integer '配列用カウンタ
Dim ws As Worksheet 'ワークシート用変数
Dim fso As Object 'FileSystemObjectのインスタンス用変数
Dim filePath As String 'ファイルのパスを格納する変数
Dim cnt As Integer 'カウンタ用変数
Dim outPutCSVFile As String '出力するCSVファイルのフルパス用変数
Dim checkDir As String '確認したいファイルが存在するフォルダのパス
Dim strcmd As String 'コマンド用変数
Dim oShell As Object 'WshShellオブジェクト用変数
Dim adoCON As ADODB.Connection 'Connection用変数
Dim oRS As ADODB.Recordset 'レコードセット用変数
Dim sqlStr As String 'SQL文
Dim valRng As range 'Rangeオブジェクト格納用変数(ファイルのサイズと項番用)
Dim maxVal As Double 'データバーの最小値用変数
'SQL Serverのテーブルから取得したデータを貼り付けるシート名
Const dtSheetNM As String = "data"
'データバーの最小値を設定
Const minVal As Double = 0
'データを出力する開始行位置を設定
Const bgnLPos As Long = 8
'出力するCSVファイル名
Const outPutCSVFileNM As String = "data_output.csv"
'Excelのシートの最大行数を取得する
rowMax = ActiveSheet.Rows.Count
'カウンタを初期化する
aryCnt = 0
'本マクロのブックのシート名を取得する
Set ws = Worksheets("top")
'セルをクリアする
ws.range("A" & bgnLPos & ":E" & rowMax).ClearContents
'FileSystemObjectのインスタンスを生成する
Set fso = CreateObject("Scripting.FileSystemObject")
If Right(ws.range("dirPath").Value, 1) = "\" Then
'入力された「ファイルの置き場」のパスの末尾に「\」が付いている場合
'「ファイルの置き場」のパスを変数filePathに格納する
filePath = ws.range("dirPath").Value
Else
'入力された「ファイルの置き場」のフルパスの末尾に「\」が付いていない場合
'「ファイルの置き場」のパスの末尾に「\」を付けて、パスを変数filePathに格納する
filePath = ws.range("dirPath").Value & "\"
End If
'データを出力する開始行位置の値でカウンタを初期化する
cnt = bgnLPos
'出力するCSVファイル名を取得する(保存先はマクロのExcelファイルと同じ場所とする)
outPutCSVFile = ThisWorkbook.Path & "\" & outPutCSVFileNM
'確認したいファイルが存在するフォルダのパス
checkDir = ws.range("dirPath").Value
'サイズの指定を条件にパソコン内のファイルを検索・出力してその出力内容をCSVファイルに書き込むためのPowrShellコマンド文を取得する
strcmd = "powershell.exe -Command Get-ChildItem -Path '" & checkDir & "' -Recurse | " & _
"Where-Object {!$_.PSIsContainer} | " & _
"Select-Object DirectoryName, Name, @{Name='FileSize (MB)'; Expression={($_.Length / 1MB)}} | " & _
"Sort-Object 'FileSize (MB)' -Descending | " & _
"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=YES;FMT=Delimited"
'コネクションを開く
.Open ThisWorkbook.Path & "\"
End With
'データを取得するSQL文を作成する
sqlStr = "select"
sqlStr = sqlStr & " TOP " & rowMax - (bgnLPos - 1) & " "
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & outPutCSVFileNM & "]"
sqlStr = sqlStr & " order by 3 desc"
'Recordsetオブジェクトのインスタンスを生成する
Set oRS = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
oRS.CursorType = adOpenDynamic
'SELECT文を実行してRecordsetを開く
oRS.Open sqlStr, adoCON, adOpenStatic
'CSVファイルのデータをシートに貼り付ける
ws.range("B" & bgnLPos).CopyFromRecordset oRS
'データバーを表示させる(E列の)セルの範囲を取得する
Set valRng = ws.range("E" & bgnLPos & ":E" & bgnLPos + oRS.RecordCount - 1)
'セルに計算式を設定する(D列のセルの値を参照する数式)
valRng.Formula = "=D" & bgnLPos
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
'既存の条件付き書式設定を削除する
valRng.FormatConditions.Delete
With valRng.FormatConditions.AddDatabar
'データバー内に値を表示しないようにする(Trueなら表示させる)
.ShowValue = False
'データバーの最小値を0に設定する
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=minVal
'データバーの最大値を設定する
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=maxVal
'データバーの色を設定する(オレンジ色を設定)
.BarColor.Color = RGB(255, 153, 0)
'データバーを塗りつぶし状態に設定する(xlDataBarFillSolidは塗りつぶし)
.BarFillType = xlDataBarFillSolid
End With
'A列の項番を表示させるセルの範囲を取得する
Set valRng = ws.range("A" & bgnLPos & ":A" & bgnLPos + oRS.RecordCount - 1)
'セルに計算式を設定する(A列のセルに項番を出力させる数式)
valRng.Formula = "=row()-" & (bgnLPos - 1)
'後処理
Set adoCON = Nothing
Set oRS = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは49行目から63行目です。
If Right(ws.range("dirPath").Value, 1) = "\" Then
'入力された「ファイルの置き場」のパスの末尾に「\」が付いている場合
'「ファイルの置き場」のパスを変数filePathに格納する
filePath = ws.range("dirPath").Value
Else
'入力された「ファイルの置き場」のフルパスの末尾に「\」が付いていない場合
'「ファイルの置き場」のパスの末尾に「\」を付けて、パスを変数filePathに格納する
filePath = ws.range("dirPath").Value & "\"
End If
コードの説明
以上のコードは、ファイルの置き場のパスを取得するコードです。
トップフォルダのパスは、A2のセル(dirPathという名前付きセル)に入力されたパスから取得しています。
※A2の黄色のセルには「dirPath」という名前を付けています。
コードの詳細
49行目のコードは、A2セルに入力されたパスの末尾に「¥」の文字が含まれているか判定するIFです。
もし「¥」の文字が含まれている場合は、54行目で変数filePathにそのままパスを格納します。
「¥」の文字が含まれていない場合は、61行目でパスの末尾に「¥」を付けて変数filePathに格納します。
注目すべきコード②
次に見て頂きたいのは69行目です。
'出力するCSVファイル名を取得する(保存先はマクロのExcelファイルと同じ場所とする)
outPutCSVFile = ThisWorkbook.Path & "\" & outPutCSVFileNM
コードの説明
以上のコードは、PowerShell実行後に出力される、サブフォルダ含めて全フォルダ内のファイルの一覧を書き出したCSVファイルのフルパスを取得するコードです。
なお、CSVファイルの名前は「data_output.csv」です。
32行目で変数outPutCSVFileNMに格納しています。
'出力するCSVファイル名
Const outPutCSVFileNM As String = "data_output.csv"
注目すべきコード③
次に見て頂きたいのは72行目から79行目です。
'確認したいファイルが存在するフォルダのパス
checkDir = ws.range("dirPath").Value
'サイズの指定を条件にパソコン内のファイルを検索・出力してその出力内容をCSVファイルに書き込むためのPowrShellコマンド文を取得する
strcmd = "powershell.exe -Command Get-ChildItem -Path '" & checkDir & "' -Recurse | " & _
"Where-Object {!$_.PSIsContainer} | " & _
"Select-Object DirectoryName, Name, @{Name='FileSize (MB)'; Expression={($_.Length / 1MB)}} | " & _
"Sort-Object 'FileSize (MB)' -Descending | " & _
"Export-Csv -Path '" & outPutCSVFile & "' -Encoding UTF8 -NoTypeInformation """
コードの説明
以上のコードは、サブフォルダ含めて全フォルダ内のファイル名を取得するトップディレクトリのパスを取得し、PowerShellを使って全ファイルのパス・ファイル名・ファイルサイズを取得するコマンドを取得するコードです。
コードの詳細
72行目のコードは、ファイルサイズを取得したいファイルのトップフォルダのパスを取得するコードです。
以下の黄色のセルの値を取得します。(変数checkDirに格納されます)
75行目から80行目のコードは、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} 」
「Where-Object」は、指定された条件に一致するオブジェクトのみを取得するコマンドレットです。
「PSIsContainer」はフォルダのことを指し、「!$_.PSIsContainer」と表記することでフォルダ(コンテナ)は除外することを意味します。
今回はファイルのサイズが欲しいのでフォルダを除外するため「!$_.PSIsContainer」と記述しています。
⑤Select-Object DirectoryName, Name, @{Name=’FileSize (MB)’; Expression={($_.Length / 1MB)}}
「Select-Object」とは、オブジェクトのプロパティを選択してCSVファイルからデータを抽出するために使います。
この「Select-Object」コマンドレットの引数に、どの項目の値を出力するのかを指定します。
今回は、「DirectoryName」「Name」「@{Name=’FileSize (MB)’; Expression={($_.Length / 1MB)}}」の3つの項目を出力します。
「DirectoryName」はファイルのパスです。
「Name」はファイル名です。
「@{Name=’FileSize (MB)’; Expression={($_.Length / 1MB)}}」はファイルサイズです。
「@{Name=’FileSize (MB)’」の部分は、フィールド名に「FileSize (MB)」の文字列を付けていることを意味します。
後述する⑥で、ファイルサイズでソートするのに何かしらフィールド名が必要なので、今回はフィールド名を「FileSize (MB)」としています。
⑥Sort-Object ‘FileSize (MB)’ -Descending
「Sort-Object ‘FileSize (MB)’ -Descending」とは、「FileSize (MB)」の項目で降順にソートすることを意味します。
今回はファイルサイズで降順に並べたいので、Descendingを指定しています。
⑦Export-Csv -Path ‘” & outPutCSVFile “‘
「Export-Csv」は出力したコマンドの結果をCSVファイルに出力・保存するコマンドレットです。
⑧「-Encoding UTF8」
「-Encoding UTF8」は、出力するCSVファイルのエンコーディングをUTF-8で指定します。
「-Encoding」の引数にUTF-8を指定することで、データが文字化けせずにUTF-8形式のCSVファイルを出力することができます。
⑨-NoTypeInformation
「NoTypeInformation」オプションを付けると、ディレクトリ内のファイルをリスト形式で出力します。
注目すべきコード④
次に見て頂きたいのは82行目から85行目です。
'WshShellオブジェクト用インスタンスを生成する
Set oShell = CreateObject("WScript.Shell")
'PowerShellを呼び出してコマンドを実行する
oShell.Run strcmd, 0, True
コードの説明
以上のコードは、WshShellオブジェクト用インスタンスを生成し、PowerShellを呼び出してコマンドを実行する処理のコードです。
PowerShell実行後は、実行結果(サブフォルダ含めて全フォルダ内のファイル名・パス・ファイルサイズの一覧)がCSVファイルに出力されます。
出力されたCSVファイルの中身は下のとおりです。
ファイルのパス、ファイル名、ファイルのサイズが出力されています。
注目すべきコード⑤
次に見て頂きたいのは88行目から99行目です。
'Connectionインスタンスの生成
Set adoCON = New ADODB.Connection
'CSVへのコネクション
With adoCON
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=Yes;FMT=Delimited"
'コネクションを開く
.Open ThisWorkbook.Path & "\"
End With
コードの説明
以上のコードは、マクロがCSVに接続するための接続情報を取得して接続するコードです。
コードの詳細
88行目のコードでは、Connectionインスタンスを生成します。
このインスタンスがないとマクロがCSVファイルに接続することができないので必ず生成しておきます。
93行目と94行目で接続情報を取得し、97行目でOpenメソッドを実行してマクロがCSVファイルに接続します。
注目すべきコード⑥
次に見て頂きたいのは102行目から116行目です。
'データを取得するSQL文を作成する
sqlStr = "select"
sqlStr = sqlStr & " TOP " & rowMax - (bgnLPos - 1) & " "
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & outPutCSVFileNM & "]"
sqlStr = sqlStr & " order by 3 desc"
'Recordsetオブジェクトのインスタンスを生成する
Set oRS = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
oRS.CursorType = adOpenDynamic
'SELECT文を実行してRecordsetを開く
oRS.Open sqlStr, adoCON, adOpenStatic
コードの説明
以上のコードは、CSVファイルのデータを取得するSQLのSELECT文を用意して取得を実行する処理のコードです。
コードの詳細
102行目から107行目では、SELECT文はCSVファイルのデータを取得するSQLのSELECT文を用意しています。
103行目でTOP句(データの先頭n行を対象とする)を使っているのは、Excelのシートの最大行数が1,048,576行と上限が決まっているので、CSVファイルのデータの取得件数をExcelのシートの最大行数以下に制限する必要があるからです。
rowMax(1,048,576)の値から「bgnLPos – 1」の値をマイナスしているのは、データを出力する開始行位置が8行目(bgnLPos)だからです。
106行目では、CSVファイルの名前を指定しています。
107行目では、3列目の項目であるファイルデータを対象に降順でソートするよう指定しています。
ちなみに、102行目から107行目のコードだけではSELECT文が分かりにくいと思うので、実際に実行するSELECT文を以下にお見せします。
SELECT文(CSVファイルのデータの先頭から1048569件(Excelのシートの最大行数からデータを出力する開始行位置を引いた値)を取得する)
select
TOP 1048569 *
from
[data_output.csv]
order by
3 desc
110行目のコードでは、Recordsetオブジェクトのインスタンスを生成し、113行目でカーソルタイプにキーセットカーソル使用します。
116行目のコードでは、Openメソッドに先ほど用意したSELECTを実行します。
注目すべきコード⑦
次に見て頂きたいのは119行目です。
'CSVファイルのデータをシートに貼り付ける
ws.range("B" & bgnLPos).CopyFromRecordset oRS
コードの説明
以上のコードは、SQLのSELECT文を実行して取得したCSVファイルのデータをシートに出力する処理のコードです。
CopyFromRecordsetメソッドの引数にレコードセットのインスタンスを指定して実行すると、SQLのSELECT文を実行して取得したCSVファイルのデータをシートに出力されます。
注目すべきコード⑧
次に見て頂きたいのは122行目から125行目です。
'データバーを表示させる(E列の)セルの範囲を取得する
Set valRng = ws.range("E" & bgnLPos & ":E" & bgnLPos + oRS.RecordCount - 1)
'セルに計算式を設定する(D列のセルの値を参照する数式)
valRng.Formula = "=D" & bgnLPos
コードの説明
以上のコードは、データバーを表示させる(E列の)セルの範囲を取得するコードです。
データバーを表示させるのは、列はE列で先頭行はbgnLPosの値(今回は8行目)を行位置とし、その行から下にデータ件数の値の行位置まで(データの最終行)としています。
また、上の画像のとおりD列の値を、E列のセルにグラフ表示させたいので、125行目でファイルサイズが出力されているD列のセルに対して「”=D” & bgnLPos」の数式を設定しています。(bgnLPosはデータの開始行の行位置。(今回のマクロでは「=D8」))
この数式の設定は、左隣のセルを参照するために行います。
D列のセルはファイルサイズの値を、E列は隣のD列のファイルサイズの値をグラフ表示したいので左隣のセルを参照するよう「”=D” & bgnLPos」と数式を設定しています。
ちなみに、データの最終行までのセルに「”=D” & bgnLPos」の数式を設定すると、D列データが存在している行までのセルすべてに「”=D” & bgnLPos」が設定されてしまい、すべてD列のファイルサイズが入っている先頭行の値を参照するのでは？と思われるかもしれませんが、そうはならずに例えばE8のセルなら「=D8」、E19のセルなら「=D19」と設定されます。(相対参照により、E列それぞれの左隣のセルを参照する)
注目すべきコード⑨
次に見て頂きたいのは128行目です。
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
コードの説明
以上のコードは、D列に出力されたファイルサイズから最大値を取得するコードです。
WorksheetFunction.Maxメソッドに、ファイルサイズが入ったセルの範囲を引数に指定することで、最大のファイルサイズを取得することができます。
最大のファイルサイズはデータバーの表示も最大にするため、最大値を取得します。
ちなみに、最大値のファイルサイズのグラフは、セルいっぱいに表示されます。
注目すべきコード⑩
次に見て頂きたいのは131行目です。
'既存の条件付き書式設定を削除する
valRng.FormatConditions.Delete
コードの説明
以上のコードは、既存の条件付き書式設定を削除する処理のコードです。
条件付き書式のデータバーを表示させるセルに対して、既存の条件付き書式設定を削除しておきます。
注目すべきコード⑪
次に見て頂きたいのは133行目から150行目です。
With valRng.FormatConditions.AddDatabar
'データバー内に値を表示しないようにする(Trueなら表示させる)
.ShowValue = False
'データバーの最小値を0に設定する
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=minVal
'データバーの最大値を設定する
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=maxVal
'データバーの色を設定する(オレンジ色を設定)
.BarColor.Color = RGB(255, 153, 0)
'データバーを塗りつぶし状態に設定する(xlDataBarFillSolidは塗りつぶし)
.BarFillType = xlDataBarFillSolid
End With
コードの説明
以上のコードは、条件付き書式のデータバーの表示とデータバーの各設定を行う処理のコードです。
コードの詳細
133行目のコードは、条件付き書式のデータバーを表示させるコードです。
136行目のコードでは、データバー内に値を表示しないよう設定します。
なお、Trueならデータバー内に値を表示させます。
139行目のコードではデータバーの最小値を、142行目のコードではデータバーの最大値を設定します。
今回のコードでは、最小値と最大値を以下のコードで設定しています。(最小値は0、最大値はファイルサイズの最大値)
'データバーの最小値を設定
Const minVal As Double = 0
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
145行目のコードでは、データバーの色オレンジに設定します。
148行目のコードでは、データバーを塗りつぶし状態に設定します。
注目すべきコード⑫
次に見て頂きたいのは153行目から156行目です。
'A列の項番を表示させるセルの範囲を取得する
Set valRng = ws.range("A" & bgnLPos & ":A" & bgnLPos + oRS.RecordCount - 1)
'セルに計算式を設定する(A列のセルに項番を出力させる数式)
valRng.Formula = "=row()-" & (bgnLPos - 1)
コードの説明
以上のコードは、A列のセルに項番を設定する処理のコードです。
コードの詳細
153行目のコードでは、項番を出力したいセルの範囲を取得します。
156行目のコードでは、153行目のコードで取得した項番を出力したいセルの範囲に対して数式row()を設定しています。
数式row()は、数式row()が入力された行位置を取得します。
例えば8行目に数式row()が入力されると、8の値を返します。
今回は8行目がCSVファイルのデータを出力する先頭行が8行目なので、数式row()の返す値からbgnLPos(8)の値を1つマイナスした値(7)をマイナスします。
これでA列の8行目は1が表示されます。9行目以降も項番が表示されるようになります。
【注意】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 Double
「;」は定義文をコメントアウトするのに使う文字です。
定義文の先頭に「;」を付けておくと、その行は無視されます。
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 Double
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つ目の値には、データの型を指定しています。
今回はファイルサイズを扱うのに小数点以下の値を持つので、データの型に倍精度浮動小数点数型である「Double」の文字列を指定しています。
「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ファイルの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの15行目の「ADODB.Connection」と16行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim adoCON As ADODB.Connection 'Connection用変数
Dim oRS As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、条件付き書式のデータバーを使ってサブフォルダ含めて全フォルダ内のファイルのサイズを横棒グラフに表示させる方法についてご説明しました。
ファイルサイズを指定してファイルを検索・特定したい場合は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
