LoginSignup
0
0

More than 1 year has passed since last update.

VBA閉じる際に空のエクセルを残さない

Last updated at Posted at 2023-01-18

VBAから自BOOKを閉じる際に
ThisWorkbook.Closeを実行すると空のエクセルが残り、
Application.Quitを実行すると他の開いているエクセルも閉じてしまう事への対策

vba
Option Explicit

' 終了処理
Private Sub UserForm_Terminate()

    Dim word As Variant
    Dim task As Variant
    Dim excelProcessCount As Integer
    ' Wordを起動
    Set word = CreateObject("Word.Application")
    ' Word VBAのTasksコレクションを調べる
    For Each task In word.Tasks
        If task.Visible And InStr(task.name, " - Excel") > 0 Then 
            ' エクセルが実行中だったらカウントする
            excelProcessCount = excelProcessCount + 1
        End If
    Next task
    word.Quit
    Set word = Nothing

    ' TASKから他のエクセルがないか調べ2つ以上なら自身BOOKを閉じる。以下ならエクセルを閉じる
    If excelProcessCount > 1 Then
        ThisWorkbook.Saved = True
        ThisWorkbook.Close
    Else
        Application.Quit
    End If
    
End Sub
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0