エクセルVBAで一段上の階層にあるフォルダのアドインファイルがクローズされず残ってしまいます

Question

VBAビジナーで、勉強中の身です。

複数の端末で使用するマクロがたくさんあるので
アドインファイルにして、一つ上の階層にある「管理者用ファイル」という
フォルダに格納しています。

それぞれ派生したファイルのコマンドボタンをクリックすると
マクロファイルを呼び出して、マクロを実行し、
完了したら、自動的にマクロを閉じておきたいのですが、
マクロが閉じられず、残ってしまいます。

CLOSEメソッドのどこかに問題があるのかと思いますが
ご教示頂けますでしょうか

    Dim パス名, マクロファイル As String
    
    If ActiveSheet.Name = ("N") Then
    パス名 = ThisWorkbook.Path
    
    Else
    パス名 = Left(ThisWorkbook.Path, InStrRev(ActiveWorkbook.Path, "\") - 1)
    End If

    マクロファイル = "\管理者用ファイル\消さないで夜間工程作成マスター2017.xlam"
            
    On Error Resume Next
    Workbooks.Open パス名 & マクロファイル, ReadOnly:=True

    Application.ScreenUpdating = False '画面更新不要
    Application.Run "'消さないで夜間工程作成マスター2017.xlam'!選択されたテキストボックスを表示"

    Workbooks(マクロファイル).Close
    Exit Sub


End Sub


コード

Answer 1

Excelアドインファイル

普通のExcelマクロ有効ファイル(xlsm)ではなく敢えてExcelアドインファイル(xlam)として保存してあるのですから、エクセルにアドイン登録して使う方が自然な気がするのですが。。
そこはいろいろなお家事情があるものと思いますので、スルーしてアドバイスさせていただきます。

エラー原因

まず提示いただいたコードからOnErrorの部分をコメントアウトして動かしてみてください。
するとWorkbooks(マクロファイル).Closeの部分で「インデックスが有効範囲にありません」のエラーが発生すると思います。

マクロファイルという変数にはファイル名だけでなく一つ上のフォルダ名も含まれている為、そのままブック名として利用してもそんなブックは見つからないためです。

現状でファイルが閉じない原因はまさにこれだと思います。

ではどうすればよいか？ということで、２つほど対応案をご紹介します。
対応案① マクロファイル変数にはファイル名しか設定しない。
対応案② マクロファイル名でxlamブックを検索せず、開いたブックを閉じる。

対応案①

マクロファイル変数にはファイル名しか設定せず、一つ上のフォルダはパス側に含めることで対応できます。

修正すると以下のような感じになります。

    Dim パス名, マクロファイル As String
    
    If ActiveSheet.Name = ("N") Then
        パス名 = ThisWorkbook.Path
    Else
        パス名 = Left(ThisWorkbook.Path, InStrRev(ActiveWorkbook.Path, "\") - 1) 
    End If
    
    'マクロファイル = "\管理者用ファイル\消さないで夜間工程作成マスター2017.xlam"
    パス名 = パス名 & "\管理者用ファイル\
    マクロファイル = "消さないで夜間工程作成マスター2017.xlam"
    
    On Error Resume Next
    Workbooks.Open パス名 & マクロファイル, ReadOnly:=True
    
    Application.ScreenUpdating = False '画面更新不要
    Application.Run "'" & マクロファイル & "'!選択されたテキストボックスを表示"
    
    Workbooks(マクロファイル).Close
    Exit Sub

対応案②

こちらは開いているブックの中からファイル名で閉じるブックを探すのではなく、自分で開いたxlamブックを覚えておく方法です。

修正すると以下のような感じになります。

    Dim パス名, マクロファイル As String
    
    If ActiveSheet.Name = ("N") Then
        パス名 = ThisWorkbook.Path
    Else
        パス名 = Left(ThisWorkbook.Path, InStrRev(ActiveWorkbook.Path, "\") - 1) 
    End If
    
    マクロファイル = "\管理者用ファイル\消さないで夜間工程作成マスター2017.xlam"
    
    On Error Resume Next
    'Workbooks.Open パス名 & マクロファイル, ReadOnly:=True
    Dim ブック As WorkBook
    Set ブック = Workbooks.Open(パス名 & マクロファイル, ReadOnly:=True)
    
    Application.ScreenUpdating = False '画面更新不要
    Application.Run "'" & ブック.Name  & "'!選択されたテキストボックスを表示"
    
    ブック.Close
    Exit Sub

---

どちらの案を選択するかは検討していただければいいと思いますが、Close以外の操作もしやすくなるので自分なら案②を選択すると思います。

参考になれば幸いです。

Comments

[rainbow_trip]

とてもわかりやすい解説、また対応策①、対応策②のコード記述をありがとうございます。
おかげさまで対応策②にて、解決しました。
おっしゃるとおりで、ブック名が原因のようでした。

コードもとてもシンプルで見やすくなり、他のコードも同様に修正させていただきました。

ベストアンサーに選ばせていただきます
ありがとうございました