【Excel/VBA】連想配列×ファイルシステムオブジェクトで複数ファイルを自由に操作する方法

こんにちは！あっきー (IwswAkht) です！

Excel の VBA を使う機会がありましたので今日は忘備録もかねて作ったサンプル公開しようと思います。

VBAは環境構築の必要がなくExcelがPCに入ってさえすればすぐにでも始められる言語です。
なのでプログラミングに興味がある人がお試しでやってみるのにはちょうどいい言語なのではないかと思ってます。

僕も初めてプログラミングをしてみたときは VBA をやりました。(挫折してそれ以降やってませんでしたがw)

それに少なくとも、日本で働いていれば Excel を使う頻度は極めて高いと思います。
エンジニアでない人でも VBA を覚えて日々の記録作業などを自動化したら社内で魔法使い扱いされるかもです。

タイトルでは連想配列とファイルシステムオブジェクトについてと書いてますが、サンプルとしてその他にもよく使いそうな機能を幅広く網羅したつもりですのでご参考にしてください。

はじめに
設定ファイル読込→ファイル操作
1. 連想配列
ファイルシステムオブジェクト
さいごに

はじめに

動画で行っている手作業を VBA で自動化します。

概要：社員データを参照し別ファイルに所属部署ごとに社員データをまとめる

よくありそうな退屈なコピペ作業をシチュエーションにしてみました。

※画面切り替えなどしないためにシートをまとめてますが、実際はブックが分かれてます。

今回で主に以下の項目が学習できます。

1.別ブックを参照し値を書き込む
→ExecuteExcel4Macro
2.ファイルチェックなどのバリデーション
→Dir
3.連想配列を使用したパラメータの扱い
→Dictionary
4.ファイル操作
→ファイルシステムオブジェクト
5.引数を別のプロシージャへ渡す
→Call
6.複数の戻り値を配列で返す
→Function
7.その他、基本メソッドやセルの操作
→IF, For, For Each, etc...

設定ファイル読込→ファイル操作

ネットで「ブックを開く」や「ファイル名の取得」などで調べると大体以下のようなコードが出るかと思います。

' ブックを開く
Workbooks.Open "C:\Users\test1.xlsx"

'ファイル名を取得
myFile = "C:\Users\test1.xlsx"
fileName = Dir(myFile)

' ブックを開く

Workbooks.Open "C:\Users\test1.xlsx"

'ファイル名を取得

myFile = "C:\Users\test1.xlsx"

fileName = Dir(myFile)

このような場合フォルダ構成が変わったり、ファイル名が変わった場合などその都度コードを編集しないといけませんよね。

自分が使うだけなら別にいいですが、VBA に馴染みのない人でしたらコードからパスを変えるだけでも大変なのではないかと思います。

なので、以下の画像のように設定ファイルを用意してそのファイルを編集する作りの方が汎用性は高いでしょう。

・C:\Users\{username}\excel_sample\AppConfig.xlsx

連想配列

VBA の連想配列は Dictionaryオブジェクト で使用可能です。

配列はインデックスに対して値が格納されるの対し、連想配列は Key と Item をセットで使用します。

画像では「ParamName」列に書いた値が Key になり「value」列に書いた値が Item になります。

使うときはオブジェクト変数を定義し(dic という名前で仮定)、dic("Key")と記述することで Key に紐づいた Item を使うことができます。

こうすることでフォルダパス、ファイル名はもちろん、シート名やその他の運用の中で変わりそうな値は全部設定ファイルに記入しておけば管理はぐっと楽になりますよね。

実際のコードはいかになります。

・Common モジュール

'----------------------------------------------------------------------
'処理：AppConfigファイル記載の設定を読込＆書き込み
'----------------------------------------------------------------------
Sub initializeMacro()

    Dim i As Long, j As Long
    Dim RowIndex As Long
    Dim FolderPath As String   
 
    ' AppConfigのパスを取得する
    FolderPath = Range("Q3").Value   

    ' ファイルチェック
    If Dir(FolderPath & "\AppConfig.xlsx") <> "" Then  
 
        ' AppConfig 2,3列目の最終行までを[config]シートに書き込み
        For i = 2 To Range("Q2").Value  
         
            For j = 2 To 3
                Cells(i, j) = _
                    ExecuteExcel4Macro("'" & FolderPath & "\[AppConfig.xlsx]Sheet1'!R" & i & "C" & j)                    
            Next j
        Next i
    Else    
        ' Exception:
        MsgBox "AppConfig.xlsxが見つかりません。" & vbLf & "作業フォルダのパスを確認してください。"
        End        
    End If    
End Sub

'----------------------------------------------------------------------
'処理：ConfigシートのKeyとvalueでDictionaryの作成
'----------------------------------------------------------------------
Public Function Fnc_SetConfig(objMacroBook As Workbook) As Object

    ' 連想配列要のオブジェクト変数
    Dim dic As Object

    ' dictionary作成
    Set dic = CreateObject("Scripting.Dictionary")

    'Configシートの値を変数に格納
    With objMacroBook.Sheets("Config")
        For i = 2 To .UsedRange.Rows.Count + 1
            dic.Add .Cells(i, 2).Value, .Cells(i, 3).Value
        Next i
    End With

    ' 連想配列をModule1に返す
    Set Fnc_SetConfig = dic

End Function

'----------------------------------------------------------------------

'処理：AppConfigファイル記載の設定を読込＆書き込み

'----------------------------------------------------------------------

Sub initializeMacro()

Dim i As Long, j As Long

Dim RowIndex As Long

Dim FolderPath As String

' AppConfigのパスを取得する

FolderPath = Range("Q3").Value

' ファイルチェック

If Dir(FolderPath & "\AppConfig.xlsx") <> "" Then

' AppConfig 2,3列目の最終行までを[config]シートに書き込み

For i = 2 To Range("Q2").Value

For j = 2 To 3

Cells(i, j) = _

ExecuteExcel4Macro("'" & FolderPath & "\[AppConfig.xlsx]Sheet1'!R" & i & "C" & j)

Next j

Next i

Else

' Exception:

MsgBox "AppConfig.xlsxが見つかりません。" & vbLf & "作業フォルダのパスを確認してください。"

End

End If

End Sub

'----------------------------------------------------------------------

'処理：ConfigシートのKeyとvalueでDictionaryの作成

'----------------------------------------------------------------------

Public Function Fnc_SetConfig(objMacroBook As Workbook) As Object

' 連想配列要のオブジェクト変数

Dim dic As Object

' dictionary作成

Set dic = CreateObject("Scripting.Dictionary")

'Configシートの値を変数に格納

With objMacroBook.Sheets("Config")

For i = 2 To .UsedRange.Rows.Count + 1

dic.Add .Cells(i, 2).Value, .Cells(i, 3).Value

Next i

End With

' 連想配列をModule1に返す

Set Fnc_SetConfig = dic

End Function

・Module1

Option Explicit
'===============
' 変数宣言
'===============
Dim dic As Object
Dim wb As Object

Sub Main()
    ' 保存、閉じる時にメッセージを出力しない
    Application.DisplayAlerts = False

    ' Commonモジュールの「Fnc_SetConfigConfig」プロシージャ実行
    Set dic = Fnc_SetConfig(ThisWorkbook)

　　' ここから各々好きにメイン処理を記述していく。
End Sub

Option Explicit

'===============

' 変数宣言

'===============

Dim dic As Object

Dim wb As Object

Sub Main()

' 保存、閉じる時にメッセージを出力しない

Application.DisplayAlerts = False

' Commonモジュールの「Fnc_SetConfigConfig」プロシージャ実行

Set dic = Fnc_SetConfig(ThisWorkbook)

　　' ここから各々好きにメイン処理を記述していく。

End Sub

「sampleMacro.xlsm」のマクロ実行ボタンを押して読込を行うと以下の画像のように設定ファイルに記載した情報が書き込まれます。

ファイルシステムオブジェクト

連想配列に設定を格納したらファイルシステムオブジェクトでサンプルのファイルを開いてみましょう。

コードは以下になります。

・module1

Sub Main()

    ' 保存、閉じる時にメッセージを出力しない
    Application.DisplayAlerts = False

    ' Configの値を取得
    Set dic = Fnc_SetConfig(ThisWorkbook)

    '----------------------------------------------------------------------
    '処理：必要な各ファイルを設定
    '----------------------------------------------------------------------  

    Dim fso As Object               ' ファイルシステムオブジェクト
    Dim editFilePath As Object      ' 編集ファイルのオブジェクト
    Dim dbFilePath As Object        ' 参照ファイルのオブジェクト
    Dim editFileExist As Boolean    ' 編集ファイルチェック
    Dim dbFileExist As Boolean      '参照ファイルのチェック

    Set fso = CreateObject("Scripting.FileSystemObject")  
 
    With fso    
        ' パスの生成
        editFileExist = .FileExists(dic("templateFolder") & "\" & dic("editFileName"))
        dbFileExist = .FileExists(dic("templateFolder") & "\" & dic("dbFileName"))  
         
            ' ファイルチェック
            If editFileExist And dbFileExist Then
                Set editFilePath = .GetFile(dic("templateFolder") & "\" & dic("editFileName"))
                Set dbFilePath = .GetFile(dic("templateFolder") & "\" & dic("dbFileName"))
            Else            
                ' Exception
                MsgBox "ファイルが存在しません。" & vbLf & "AppConfig.xlsxの設定を確認してください。"
                End                
            End If        
    End With

    ' 保存、閉じる時にメッセージを出力する
    Application.DisplayAlerts = True    

    'ファイルシステムオブジェクトを破棄
    Set fso = Nothing
    Set editFilePath = Nothing
    Set dbFilePath = Nothing

End Sub

Sub Main()

' 保存、閉じる時にメッセージを出力しない

Application.DisplayAlerts = False

' Configの値を取得

Set dic = Fnc_SetConfig(ThisWorkbook)

'----------------------------------------------------------------------

'処理：必要な各ファイルを設定

'----------------------------------------------------------------------

Dim fso As Object ' ファイルシステムオブジェクト

Dim editFilePath As Object ' 編集ファイルのオブジェクト

Dim dbFilePath As Object ' 参照ファイルのオブジェクト

Dim editFileExist As Boolean ' 編集ファイルチェック

Dim dbFileExist As Boolean '参照ファイルのチェック

Set fso = CreateObject("Scripting.FileSystemObject")

With fso

' パスの生成

editFileExist = .FileExists(dic("templateFolder") & "\" & dic("editFileName"))

dbFileExist = .FileExists(dic("templateFolder") & "\" & dic("dbFileName"))

' ファイルチェック

If editFileExist And dbFileExist Then

Set editFilePath = .GetFile(dic("templateFolder") & "\" & dic("editFileName"))

Set dbFilePath = .GetFile(dic("templateFolder") & "\" & dic("dbFileName"))

Else

' Exception

MsgBox "ファイルが存在しません。" & vbLf & "AppConfig.xlsxの設定を確認してください。"

End

End If

End With

' 保存、閉じる時にメッセージを出力する

Application.DisplayAlerts = True

'ファイルシステムオブジェクトを破棄

Set fso = Nothing

Set editFilePath = Nothing

Set dbFilePath = Nothing

End Sub

CreateObject("Scripting.FileSystemObject") でファイルシステムオブジェクトを使えます。

あとは連想配列から必要な Key を呼び出し、対象ファイルが開かれることが確認できます。

こんな感じでファイルが開かれたことが確認できます。

あとは実際に動画で行っている作業のコードを書いていけばオッケーです。

長くなるので説明は割愛しますが、ファイル一式下記のリンクからダウンロードできますので実際に動かしたり、いじったりしながら確認してください。

↓ファイルをダウンロード↓

excel_sample

さいごに

最後まで読んでいただきありがとうございます。

今回で VBA は慣れれば便利だなって思ったのでエンジニアだけでなく事務の人とかも使えるようになったら業務がすごいはかどるようになると思います。

基本リテラシーとして持っといて悪くないスキルだと思いますのでぜひ実践してみていただけたらと思います！

この記事を気に入っていただけましたらTwitterdでもプログラミングに関してのツイートをリアルタイムでしていますので
ご一緒にフォローもお願いします。

あっきーをフォローする