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
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