Prevent Any Workbook from Opening in Current Excel Application

[bdmangum]

All,

For a workbook I created it is important that it is the only workbook loaded in a single instance of Excel. i have already coded it so the workbook will always load in a new application of Excel. I'm now trying to prevent another workbook from being opened in that instance of Excel.

The current workbook hides the Excel application while the userforms are running, thus I need to prevent other workbooks from loading into this application. Is there a way to detect a workbook being opened and to direct it to another instance of Excel?

Thanks!
BD

 

[bdmangum]

Well after some more effort I think I found a way to make it work. I'll post the code so people can check and make sure it is a sound approach, there's a good chance I overlooked something important.

I used the workbook deactivate event to try and trap new workbooks when they open. My thought was that as a new workbook opens it takes the focus away from the current workbook, thus presenting a chance to catch the load.

Private Sub Workbook_Deactivate()

    For Each w In Application.Workbooks
        If w.Name <> ThisWorkbook.Name Then
            Set newApp = CreateObject("Excel.Application")
            tempWork = w.FullName
            w.Close False
            Set newWork = newApp.Workbooks.Open(tempWork)
            newApp.Visible = True
            newWork.Activate
        End If
    Next

End Sub

 

[bdmangum]

I discovered a problem. The workbook_open of the new workbook event fires before the above code catches and closes the workbook. Thus any workbook with macros will run and perform its on open commands and then get transfered to the new Application. Anybody have ideas regarding work arounds?

 

[combo]

You could try (ThisWorkbook module):

Private WithEvents xlApp As Application, xlApp2 As Application

Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
Dim FilePath As String
If Not Wb Is ThisWorkbook Then
    FilePath = Wb.FullName
    Wb.Close SaveChanges:=False
    Set xlApp2 = New Application
    xlApp2.Visible = True
    xlApp2.Workbooks.Open FilePath
    Set xlApp2 = Nothing
End If
End Sub

combo

 

[bdmangum]

combo,

Thanks for the response. The code you supplied runs into the same problem as the code I provided, it doesn't prevent workbook_open macros in the new workbook from running until after they are in the new application. Thus all workbook_open macros are run before the switch, which may be fine in some cases, but not all.