はじめに
VBAにはConstキーワードを用いて定数を定義することができる。
定数の中には、参照する他ファイルのファイルパス・ファイル名を定義することもあるだろう。
定数を利用するエクセルファイルが1つであればよいが、たくさんのエクセルファイルから同じ定数を参照される場合どうだろう。
定数に格納されたフォルダの名前が変わった場合、全てのエクセルファイルを開いて定数を変更しなければならない。
もちろん規模にもよるだろうが、私の職場では、1か月ごとにフォルダを新しく切るという風習があり、そのたびに定数の変更が発生する。
以下の例だと簡単すぎるため、2024の箇所をYear(Now())で置き換えれば解決はしてしまうわけだが、実際には、SやTの番号なども頻繁に変わりうるため、
エクセル側で動的にパスを組み立てるのが難しいとする。
具体例
Const filePath1 = “C:\Works\2024-S001”
Const Process1 = “C:\Works\2024-S001\Process1\output.xlsx”
Const Process2 = “C:\Works\2024-S001\Process2\output.xlsx”
Const Process3 = “C:\Works\2024-S001\Process3\output.xlsx”
やりたいこと
愚直にコンフィグファイルに定義するのであれば、以下のとおりである。
しかし、実際には「C:\Works\2024-S001」の部分が重複している。
より複雑なフォルダ構成の場合、数十か所に対してこのパスを頭に付けなければならない。
filePath1=C:\Works\2024-S001
Process1=C:\Works\2024-S001\Process1\output.xlsx
Process2=C:\Works\2024-S001\Process2\output.xlsx
Process3=C:\Works\2024-S001\Process3\output.xlsx
そのため、以下のように、変数置換できるように対応する。
個人的に「@@」で囲う方が視覚的に分かりやすいのだが、ファイル名にも利用でき、処理が煩雑になってしまうため、「<>」を利用することにした。
filePath1=C:\Works\2024-S001
Process1=<filePath1>\Process1\output.xlsx
Process2=<filePath1>\Process2\output.xlsx
Process3=<filePath1>\Process3\output.xlsx
前提条件
- コンフィグファイルは文字コードが「UTF-8」であること。
- コンフィグファイルの名前は「env.conf」であること。
- 実行するVBAファイル、またはアドイン化したVBAファイルの同階層に「env.conf」が配置されていること。
ソースコード
Option Explicit
' 参照設定
' Windows Script Host Object Model
'
Private params As Dictionary
' コンストラクタ
Private Sub Class_Initialize()
End Sub
' デストラクタ
Private Sub Class_Terminate()
End Sub
'
'【処理】
' コンフィグファイルを読み込む
'
'【引数】
' fullPath : csvファイルのファイルパス
'【返り値】
' コンフィグパラメータ
'
Public Function LoadFile(wb As Workbook) As Variant
Set params = New Dictionary
Dim file_path As String
file_path = wb.Path & "\env.conf"
Const CHARSET As String = "UTF-8"
' ファイル読み込み
Dim buf As String
Dim ado As New ADODB.Stream
With ado
.CHARSET = CHARSET
.Type = adTypeText
.Open
.LoadFromFile (file_path)
buf = .ReadText
.Close
End With
Set ado = Nothing
Dim lines() As String
lines = Split(buf, vbCrLf)
Dim i
For i = 0 To UBound(lines)
Dim line As String
line = lines(i)
line = Trim(line)
' コメント行
If (Left(line, 1) = "#") Then
' スキップ
' パラメータ追加
ElseIf (InStr(line, "=") > 1) Then
Dim kv() As String
kv = Split(line, "=")
Call params.Add(kv(0), kv(1))
End If
Next
End Function
'
'【処理】
' Keyに対応するValueを取得する
'【引数】
' Key
'【返り値】
' 対応するValue
'
Public Function ResolveValue(key As String) As String
If (params.Exists(key) = False) Then
Debug.Print ("コンフィグ定義エラー" & vbCrLf & "キー:[" & key & "]が設定ファイルに定義されていません。")
End If
Dim val As String
val = params.Item(key)
' Valueに含まれる置換変数を列挙する
Dim reg As RegExp
Set reg = New RegExp
reg.Pattern = ("<.+?>")
reg.Global = True
Dim matches As MatchCollection
Set matches = reg.Execute(val)
Set reg = Nothing
Dim m As Match
For Each m In matches
Dim replace_key As String
Dim replace_val As String
' 置換変数を取り除く
replace_key = Mid(m.Value, 2, Len(m.Value) - 2)
replace_val = ResolveValue(replace_key)
val = Replace(val, m.Value, replace_val)
Next
' 置換変数から定数への置き換えが完了したらValueを返却する
ResolveValue = val
End Function
' コンフィグファイルからキーに対応する値を取得します。
Public Function GetEnv(key As String) As String
Dim c As ConfigAccessor
Set c = New ConfigAccessor
Call c.LoadFile(ThisWorkbook)
Dim val As String
val = c.ResolveValue(key)
Set c = Nothing
GetEnv = val
End Function
'
' コンフィグファイルからパス/ファイル名を指定し、合致するファイルパスのコレクションを取得する。
' ファイル名には正規表現を指定することも出来る。
' [config]
' template_file_path=C:\Works\
' template_file_name=Dog*.xlsx
' [vba]
' GetSpecificFileName("template_file_path", "template_file_name")
' ⇒ ["C:\Works\Dog123.xlsx", "C:\Works\Dog456.xlsx", "C:\Works\Dog789.xlsx"]
'
Public Function GetFilePathList(key_file_path As String, key_file_name As String) As Collection
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim file_path As String
Dim file_name As String
file_path = GetEnv(key_file_path)
file_name = GetEnv(key_file_name)
Dim c As Collection
Set c = New Collection
Dim match_count As Long
Dim f As Scripting.File
With fso
For Each f In .GetFolder(file_path).Files
If (f.Name Like file_name) Then
c.Add (f.Path)
End If
Next
End With
Set fso = Nothing
Set GetFilePathList = c
End Function
解説
ConfigAccessor
- LoadFile
「=」の前後をkeyとvalueに分けてDictionaryに追加しているだけである。
「#」から始まる行はコメントとして、Dictionaryへの追加は行わない。 - ResolveValue
①keyに対応するvalueをDictionaryから取得する。
value内に置換変数「<>」が含まれていたら、再帰的にvalueを検索する。# ルートフォルダ RootDir=総務部/ Category=ゲーム/ # 子フォルダ SubFolder1=<RootDir><Category>
紅魔郷/ SubFolder2=<RootDir><Category> 風神録/ SubFolder3=<RootDir><Category> 妖々夢/ # 孫フォルダ Character1=<SubFolder1> 博麗霊夢/ Character2=<SubFolder1> 霧雨魔理沙/ 例えば、
GetEnv("Character1")
を呼び出した場合、
- <SubFolder1>博麗霊夢/を取得。置換変数「SubFolder1」があるため、再帰検索。
- <RootDir><Category>紅魔郷/を取得。置換変数「RootDir」があるため、再帰検索。
- 総務部/を取得。置換変数がないため、総務部/を返却。
- ゲーム/を取得。置換変数がないため、ゲーム/を返却。
- 総務部/ゲーム/紅魔郷を返却。
- 総務部/ゲーム/紅魔郷/博麗霊夢/を返却。
Config
- GetEnv
呼び出すためにファイルを開いているため、パフォーマンスはよろしくない。
1度のファイルオープンで済ますことを考えると、どこかにデータを保持しなければならず、利用する側のコードが汚れてしまう。
呼び出す回数は少ないことを前提に、楽に呼び出せるように、毎回クラスは破棄する作りを取っている。
また、このConfigはアドインとして既存ブックに追加することを想定しており、クラスを利用するためにはクラスのスコープを広げなければいけないといけず面倒、といった理由もある。 - GetFilePathList
よく末尾にタイムスタンプのついたファイルを見かける。その場合、コンフィグ内でファイル名を固定値で定義することができない。
かつ、ファイル名の接頭語だけ各ブックに定数として持たせるというのも、結局Configを導入した意味がなくなってしまう。
そのため、正規表現で一致するファイルパスを一覧で取得するという処理を追加した。
Configというプロシージャの処理としては合致しない気がしないのだが、横着してそのまま同じところに定義した。
終わりに
エラー処理を全くしていないので、良きようにカスタマイズしましょう。