Ensure Workbooks in the same Excel Application

[Andy B via OfficeKB.com]

I have developed a Microsoft Excel tool that automatically creates separate
Excel Workbooks that are intended to be used for data storage, such that they
effectively act as databases.

The function I have used to check whether these data storage Workbooks are
open in the active window, do not necessarily recognise that the Workbooks
are open, if the Workbooks are open in another Excel application on my
desktop. I really need to be able to ensure that when the data storage
Workbooks are opened, they appear in the same Excel application where the
controlling Excel tool is active.

Does anyone have any suggestions for achieving this?

The function I have used to check that the Workbooks are open is:

Function IsWorkbookOpen(WorkbookName As String) As Boolean
'Checks to see if a Workbook, with name 'WorkbookName' is currently open

Dim Wkb As Workbook

On Error Resume Next
Set Wkb = Workbooks(WorkbookName)

If Not Wkb Is Nothing Then
IsWorkbookOpen = True
End If

Set Wkb = Nothing

End Function

And, if required, I have used the following line to create the data storage
Workbook

ThisWorkbook.Workbooks.Add

 

[Dave Peterson]

I'd check to see if it was open in the running instance of excel (with code like
yours).

If it wasn't open there, then I'd check to see if the file was open elsewhere:
http://support.microsoft.com?kbid=138621
Microsoft's IsFileOpen function.