Closed workbook is not nothing

[George J]

I have a workbook that creates another workbook on opening using

Set Freshwkbk = Workbooks.Add

I have this as a Public Variable.

If I close the Freshwkbk workbook, when code is run on the original workbook I am unable to tell that the Freshwkbk has been closed. If I put

?Freshwkbk is nothing

in the immediate window (after closing the Freshwkbk), it returns False.
If I put ?Freshwkbk.name in the immediate window, I get an "Automation error" stating "Run-time error '-2147221080 (800401a8)'.

I thought that checking

If Freshwkbk is Nothing

that this would tell me if the workbook was available. Should I be using something else to test for this?

 

[ranman256]

how about:
Workbooks.Add
Set Freshwkbk = ActiveWorkbook

 

[George J]

Thanks, but I get the same error.

I tested it on a blank workbook. For the 'ThisWorkbook' code I have

Private Sub Workbook_BeforeClose(Cancel As Boolean)


If Refreshwkbk Is Nothing Then
    MsgBox "other wkbk closed"
Else
    MsgBox "other wkbk open"
End If
End Sub


Private Sub Workbook_Open()
Call myRefresh
End Sub

And in a module I have

Public Refreshwkbk As Workbook
Sub myRefresh()
Set Refreshwkbk = Workbooks.Add
End Sub

How would I test that the newly created workbook is still open? It may be that the problem is because the new workbook is not saved.

 

[John_w]

I came across this exact problem recently with a module-level workbook object variable, so a Private variable instead of a Public one, but the principle is the same and maybe you could apply the same solution.

Instead of storing the 2nd workbook in a global Workbook variable, I store its full name in a global String variable, and handle the creation of the 2nd workbook with the help of the GetWorkbook function like so:

Private Wb2FullName As String

Public Sub Main()

    Dim Wb2 As Workbook

    Set Wb2 = GetWorkbook(Wb2FullName)
    If Wb2 Is Nothing Then
        Set Wb2 = Workbooks.Add(xlWBATWorksheet)
        MsgBox "2nd workbook created"
    Else
        MsgBox "2nd workbook, " & Wb2FullName & ", is already open"
    End If
    
    'Save name/full name of 2nd workbook in global variable.  If workbook hasn't been saved then Wb2.FullName is same as Wb2.Name
    Wb2FullName = Wb2.FullName
    
    'More code....

    'If Wb2 is saved by code:
    'Wb2FullName = Wb2.FullName
    
    'If Wb2 is closed by code:
    'Wb2FullName = ""

End Sub


Private Function GetWorkbook(ByVal workbookNameOrFullName As String) As Workbook

    Dim file As String

    Set GetWorkbook = Nothing
    On Error Resume Next
    Set GetWorkbook = Workbooks(workbookNameOrFullName)
    On Error GoTo 0
    If GetWorkbook Is Nothing Then
        file = Dir(workbookNameOrFullName)
        On Error Resume Next
        Set GetWorkbook = Workbooks(file)
        If GetWorkbook Is Nothing Then
            Set GetWorkbook = Workbooks.Open(workbookNameOrFullName)
        End If
        On Error GoTo 0
    End If

End Function

 

[George J]

Thanks for that John.

I was sure someone else would have come across this before so was suprised when I couldn't find a solution. I ended up just managing the error (on error goto) as there wasn't much happening in the BeforeClose event.