excel - VBA stops running if user closes VBA created (temp) workbook - Stack Overflow

admin管理员组

文章数量:1287612

What I'm Trying To Do: I would like to (all during the VBA code's runtime) be able to create a temporary workbook, potentially populate it will some data, have the user add/remove/edit the data, close (& save) the workbook to commit their work, and then have the code use this updated data in the rest of the run.

The Issue: I can get the code to create the temp file, and go into a holding pattern that lasts as long as it's open, but as soon as the user closes it the code ceases it's execution.

Code:

Sub Test()   

    ' Set up the filename & path for the temporary workbook.
    Dim Temp_Dir As String, Temp_WB_Name As String, Temp_WB_Path As String

    Temp_Dir = IIf(Environ$("tmp") <> "", Environ$("tmp"), Environ$("temp"))

    Temp_WB_Name = "temp_input_wb.xlsx"
    Temp_WB_Path = Temp_Dir & "\" & Temp_WB_Name   

    ' Create the temporary workbook
    Dim Temp_WB As Workbook

    Set Temp_WB = Workbooks.Add    

    ' Then save it to make it easier to find when the user is done withe it.
    Application.DisplayAlerts = False
    Temp_WB.SaveAs Temp_WB_Path
    Application.DisplayAlerts = True    

    ' Display a message to the user
    MsgBox ("Free to add/remove/edit data via the temp workbook; simply close when done.")

    Dim Counter As Long

    ' Wait until the user closes the workbook.
    ' N.b. The code works up to (and including) this loop; it looks indefinitely whilst the workbook is open, but then once the user closes it the code stops running instead of moving on.

    Do While WorkbookIsOpen(Temp_WB_Name)
        Counter = Counter + 1
        debug.print Counter
        DoEvents

    Loop

 

    Debug.Print "Workbook closed"

   

    ' Open the workbook back up for the code to extract the data from.

    Set Temp_WB = Workbooks.Open(Temp_WB_Path)

   

    

    ' Other code would follow.

   

End Sub

 

 

' A function that just checks if a workbook is currently open with a provided name.

Function WorkbookIsOpen(WB_Name As String) As Boolean

    Dim WB As Variant, WB_Found as Boolean
    For Each WB in Workbooks
        WB_Found = WB_Found Or (WB.Name = WB_Name)
    Next WB
    
    WorkbookIsOpen = WB_Found

End Function

What I'm Trying To Do: I would like to (all during the VBA code's runtime) be able to create a temporary workbook, potentially populate it will some data, have the user add/remove/edit the data, close (& save) the workbook to commit their work, and then have the code use this updated data in the rest of the run.

The Issue: I can get the code to create the temp file, and go into a holding pattern that lasts as long as it's open, but as soon as the user closes it the code ceases it's execution.

Code:

Sub Test()   

    ' Set up the filename & path for the temporary workbook.
    Dim Temp_Dir As String, Temp_WB_Name As String, Temp_WB_Path As String

    Temp_Dir = IIf(Environ$("tmp") <> "", Environ$("tmp"), Environ$("temp"))

    Temp_WB_Name = "temp_input_wb.xlsx"
    Temp_WB_Path = Temp_Dir & "\" & Temp_WB_Name   

    ' Create the temporary workbook
    Dim Temp_WB As Workbook

    Set Temp_WB = Workbooks.Add    

    ' Then save it to make it easier to find when the user is done withe it.
    Application.DisplayAlerts = False
    Temp_WB.SaveAs Temp_WB_Path
    Application.DisplayAlerts = True    

    ' Display a message to the user
    MsgBox ("Free to add/remove/edit data via the temp workbook; simply close when done.")

    Dim Counter As Long

    ' Wait until the user closes the workbook.
    ' N.b. The code works up to (and including) this loop; it looks indefinitely whilst the workbook is open, but then once the user closes it the code stops running instead of moving on.

    Do While WorkbookIsOpen(Temp_WB_Name)
        Counter = Counter + 1
        debug.print Counter
        DoEvents

    Loop

 

    Debug.Print "Workbook closed"

   

    ' Open the workbook back up for the code to extract the data from.

    Set Temp_WB = Workbooks.Open(Temp_WB_Path)

   

    

    ' Other code would follow.

   

End Sub

 

 

' A function that just checks if a workbook is currently open with a provided name.

Function WorkbookIsOpen(WB_Name As String) As Boolean

    Dim WB As Variant, WB_Found as Boolean
    For Each WB in Workbooks
        WB_Found = WB_Found Or (WB.Name = WB_Name)
    Next WB
    
    WorkbookIsOpen = WB_Found

End Function

• excel
• vba

Share Improve this question Follow edited Feb 23 at 20:53 Steve asked Feb 23 at 19:31 SteveSteve 12711 gold badge11 silver badge1212 bronze badges 4

• [stackoverflow/questions/52015792/… this work?) – wahwahwah Commented Feb 23 at 19:36
• @wahwahwah not sure that's quite what I'm after? (Unless I'm misunderstanding it!). That seems focused on controlling which workbook is active, but that's not been an issue with my code. My issue is after the VBA created temp wb is closed by the user the VBA just stops running. So nothing after the loop (e.g. ` Debug.Print "Workbook closed"`) gets carried out (but notably the loop does indefinitely run prior to the temp wb being closed). – Steve Commented Feb 23 at 19:48
• What's a "temp workbook?" Im not trying to be insulting, but there's a million sites for VBA. Whats the issue with the "temp file?" – wahwahwah Commented Feb 23 at 19:55
• Bit confused as I feel like this is all explained in my post? However, if there's an aspect I've not been clear enough on then please let me know and I'd be happy to edit my post to provide clarification. – Steve Commented Feb 23 at 20:22

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 5

I can get the code to create the temp file, and go into a holding pattern that lasts as long as it's open

I doubt this: Your WorkbookIsOpen code is wrong.

WorkbookIsOpen = (WB Is Nothing)

This line will return True when the workbook is closed, and False when it is open — the complete opposite of what you wanted. As such, your code is completely skipping the Do While loop.

WorkbookIsOpen = Not (WB Is Nothing)

is the code you want. However, more importantly: while the macro is running you can't edit the workbook. Excel is "locked". If you try to close a Workbook, it will interrupt and end the Macro to do so.

As such, I would recommend splitting your macro into two or more parts, and using Application.OnTime to check periodically. Because this frees up the execution thread until the specified time, it will allow you to edit the workbook.

Private GlobalWbName AS String 

Sub Test()
    ' Set up the filename & path for the temporary workbook.
    Dim Temp_Dir As String, Temp_WB_Name As String, Temp_WB_Path As String
    Temp_Dir = IIf(Environ$("tmp") <> "", Environ$("tmp"), Environ$("temp"))
    Temp_WB_Name = "temp_input_wb.xlsx"
    Temp_WB_Path = Temp_Dir & "\" & Temp_WB_Name

    ' Create the temporary workbook
    Dim Temp_WB As Workbook
    Set Temp_WB = Workbooks.Add

    ' Then save it to make it easier to find when the user is done withe it.
    Application.DisplayAlerts = False
    Temp_WB.SaveAs Temp_WB_Path
    Application.DisplayAlerts = True

    ' Display a message to the user
    MsgBox ("Free to add/remove/edit data via the temp workbook; simply close when done.", vbInformation)

    ' Wait until the user closes the workbook.
    ' N.b. The code works up to (and including) this loop; it looks indefinitely whilst the workbook is open, but then once the user closes it the code stops running instead of moving on.
    'Checks every 5 seconds to see if the workbook is open
    GlobalWbName = Temp_WB.Name
    Debug.Print Format(Now(), "yyyy-mm-dd hh:mm:ss") & " | Workbook opened"
    WaitUntilWorkbookCloses
End Sub

Public Sub WaitUntilWorkbookCloses()
    If WorkbookIsOpen(GlobalWbName) Then
        'Check every 5 seconds
        Application.OnTime Now()+TimeSerial(0,0,5), "WaitUntilWorkbookCloses"
        Exit Sub
    End If
    
    Debug.Print Format(Now(), "yyyy-mm-dd hh:mm:ss") & " | Workbook closed"

    ' Open the workbook back up for the code to extract the data from.
    Set Temp_WB = Workbooks.Open(Temp_WB_Name)
    'Set Temp_WB = Workbooks.Open(GlobalWBName, ReadOnly:=True) 'Open Workbook as "ReadOnly" instead of "Editable"
    GlobalWbName = ""

    ' Other code would follow.

End Sub

' A function that just checks if a workbook is currently open with a provided name.
Function WorkbookIsOpen(WB_Name As String) As Boolean

    Dim WB As Workbook

    On Error Resume Next

    Set WB = Workbooks(WB_Name)

    On Error GoTo 0
    WorkbookIsOpen = Not (WB Is Nothing) 'Fixed

End Function

本文标签： excelVBA stops running if user closes VBA created (temp) workbookStack Overflow