解析エンジニアの自動化 blog

コツコツと自動化した方法を残す blog

VBA でユーザーインターフェースを高速作成




こんにちは。
仕事の自動化にやりがいと達成感を感じるガッくんです。




この記事の目次




目的


VBA を始めようとして、とりあえず『 VBA 』でインターネット検索をすると、『集計作業』、『報告書の作成作業』から始めると良いですよ！と書いてある Web サイトが多々ありました。（筆者調べ）

そこで、報告書を作成する VBA は既にあるのですが、ソースコードもエクセルも整理されていないので、ここでちょっと整理して１つにまとめてみようと思います。

まとめ方としては、こんな感じでしょうかね。
①ファイル入力
②テキスト処理
③ファイル出力

先ずは、ファイル入力から！！




関数

ファイル入力方法

どんな集計作業も報告書作成も作るために必要な作業を経てから作るわけです。

私は解析エンジニアなので、解析の報告書を作る事がほぼメインなのですが、解析結果のファイルから報告書を作ります。

なので、やはり解析結果のファイルを入力する VBA が必要になってきます。

しかし、問題があります。

解析といっても、何パターンも行うことだってあります。すると、解析結果のファイルは山のように出来ます。(ﾟoﾟ;;

１つの解決策としては１つのフォルダの中に山のような解析結果ファイルを全部入れてしまい、フォルダ内のファイルを全て集計して、報告書を作成するというフローです。

インターフェース

Excel のシートにインターフェースを作成します。
今回は下の図１の様なインターフェースを作成しました。

図１ インターフェース素案


これに機能とボタンを作成していきます。

ソースコード①
〜フォルダ選択〜

先ずはフォルダ選択ボタンのためにフォルダ選択ダイアログを表示して、フォルダのパスを取得するサブルーチンを作成しましょう。

Public Const ShName = "ファイル入力"
Public Const fld行 = 8
Public Const fil行 = 11
Public Const Name列 = 3
Public Const Path列 = 9

Sub フォルダ選択()
   
   Dim dlg As FileDialog
   Dim fPath As String
   
   Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
    
   ' キャンセルボタンクリック時にマクロを終了
   If dlg.Show = False Then
      Exit Sub
   Else
      ' フォルダーのフルパスを変数に格納
      fPath = dlg.SelectedItems(1)
      ThisWorkbook.Worksheets(ShName).Cells(fld行, Name列).Value = 名称抽出(fPath)
      ThisWorkbook.Worksheets(ShName).Cells(fld行, Path列).Value = fPath
   End If
   
   ' ソースコード②参照
   Call フォルダ内ファイル一覧(fPath)
   
End Sub

Function 名称抽出(ByVal fPath)
   
   pos = InStrRev(fPath, "¥")
   fName = Mid(fPath, pos + 1, 100)
   名称抽出 = fName
   
End Function

ソースコード②
〜フォルダ内ファイル取得〜

次に選択したフォルダの中にあるファイルを取得しましょう。 

Sub フォルダ内ファイル一覧(ByVal fPath)
    
    Dim f As Object
    
    With CreateObject("Scripting.FileSystemObject")
        cnt = 0
        For Each f In .GetFolder(fPath).Files
            ThisWorkbook.Worksheets(ShName).Cells(fil行 + cnt, Name列) = 名称抽出(f.Path)
            ThisWorkbook.Worksheets(ShName).Cells(fil行 + cnt, Path列) = f.Path
            cnt = cnt + 1
        Next f
    End With
    
End Sub

フォルダ選択ボタン作成

フォルダ選択ボタンを作成しましょう。
今回は下の図２の様なボタンを作成しました。
ボタンを作ったときにボタンに関連付ける関数選択ダイアログが表示されるので、『フォルダ選択』を選択します。

図２ フォルダ選択ボタン


ソースコード③
〜ファイル選択〜

フォルダからのファイル入力機能はこれでオッケーですが、結局はファイルダイアログからファイルを１個ずつ選択する機能も必要になるでしょう。

Sub ファイル選択(ByVal 行)

    fPath = Application.GetOpenFileName()
   
    If fPath = False Then
        ThisWorkbook.ActiveSheet.Cells(行, Name列).Value = ""
        ThisWorkbook.ActiveSheet.Cells(行, Path列).Value = ""
    Else
        ThisWorkbook.ActiveSheet.Cells(行, Name列).Value = 名称抽出(fPath)
        ThisWorkbook.ActiveSheet.Cells(行, Path列).Value = fPath
    End If
   
End Sub

ファイル選択ボタン作成

ファイル選択ボタンを作成しましょう。
今回は下の図３の様なボタンを作成しました。
ボタンを作ったときにボタンに関連付ける関数選択ダイアログが表示されるので、『ファイル選択』を選択します。
しかし、ただ選択するだけでは動作しません。
ファイル選択関数には引数があります。ボタンに関連付ける関数に引数を渡すときには関数名の後ろに半角スペースを空けて引数を書き、シングルクォーテーションで囲みます。『'ファイル選択 1'』

図３ ファイル選択ボタン


コーディングのプチ技①

図３みたいにボタンを複数作るとき、２、３個作るのも面倒なのにボタンに関連付ける関数に引数が必要な時はやる気をなくします。
そこで、ボタンの作成も VBA にやってもらいましょう。

Sub フォームボタン作成()
    Btn出力行 = 11
    Btn出力列 = 20
    Btn作成数 = 9
    For i = Btn出力行 To Btn出力行 + Btn作成数 - 1
        With ThisWorkbook.Worksheets(ShName)
            Set NewBtn = .Buttons.Add(.Cells(i, Btn出力列).Left + 1, _
                                     .Cells(i, Btn出力列).Top + 1, _
                                     .Range(.Cells(i, Btn出力列), .Cells(i, Btn出力列 + 2)).Width - 2, _
                                     .Range(.Cells(i, Btn出力列), .Cells(i, Btn出力列 + 2)).Height - 2)
            OnAct = "'ファイル選択 " & i & "'"
            NewBtn.OnAction = OnAct
            NewBtn.Characters.text = "ファイル選択"
        End With
    Next i
End Sub

コーディングのプチ技②

コーディングのプチ技①でフォームボタンを複数作成出来ますが、もし、間違って１００個も２００個もフォームボタンを作成してしまった時のために、フォームボタンを削除する VBA も載せておきます。
ただこのコードだとフォルダ選択ボタンも全部消えちゃいますけどね。

Sub フォームボタン削除()
    For Each Btn In ThisWorkbook.Worksheets(ShName).Shapes
        If Btn.Name Like "Button *" Then
            Btn.Delete
        End If
    Next
End Sub




コメント

あとは Excel の３行目と４行目に処理ボタン用のスペースがあるので、メーンの処理コードを追加していきましょう。




以上