Open workbook if not already open, if already, then get that reference

Question

Ive a scenario to do some changes in a workbook in another workbook path. But the question is I need to check whether the workbook already open or not. If not I need to get that opened instance to a workbook variable.

Here is the code Im using for checking whether workbook open or not and then the code for opening

Function IsFileOpen(fileFullName As String)
    Dim FileNumber As Integer
    Dim errorNum As Integer

    On Error Resume Next
    FileNumber = FreeFile()   ' Assign a free file number.
    ' Attempt to open the file and lock it.
    Open fileFullName For Input Lock Read As #FileNumber
    Close FileNumber       ' Close the file.
    errorNum = Err         ' Assign the Error Number which occured
    On Error GoTo 0        ' Turn error checking on.
    ' Now Check and see which error occurred and based
    ' on that you can decide whether file is already
    ' open
    Select Case errorNum
        ' No error occurred so ErroNum is Zero (0)
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied." is 70
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' For any other Error occurred
        Case Else
            Error errorNum
    End Select

End Function
Public Function getConsolidatedDataFile() As Workbook
    Dim p As String
    p = ActiveWorkbook.Path
    Dim cf As String
    cf = printf("{0}\ConsolidatedData.xlsx", p)
    Dim wb As Workbook
    Dim fo As Boolean
    fo = IsFileOpen(cf)
    If fo = False Then wb = Workbooks.Open(filename:=cf)
    ''I need to get the code for this place of fo is true
    getConsolidatedDataFile wb

End Function

So if file open I need to get that workbook in to that wb variable.

Answer 1

Here is a quick function that will open the workbook if it's not already open:

Function GetWorkBook(ByVal sFullName As String, Optional ReadOnly As Boolean) As Workbook
    Dim sFile As String: sFile = Dir(sFullName)
    On Error Resume Next
        Set GetWorkBook = Workbooks(sFile)
        If GetWorkBook Is Nothing Then Set GetWorkBook = Workbooks.Open(sFullName, ReadOnly:=ReadOnly)
    On Error GoTo 0
End Function

Answer 2

To reference a workbook to the workbook collection it should be opened -

The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel.

MSDN Wrokbook Object

Thus, if your workbook is in the same Excel instance, then try like this:

Public Sub TestMe()        
    Dim wb As Workbook
    Set wb = Workbooks("12.xlsx")    
End Sub

If it is not in the same instance, then GetObject should work:

Public Sub TestMe()        
    Dim wb As Workbook
    Set wb = GetObject("C:\path\12.xlsx")    
    Debug.Print wb.Worksheets(1).Name
End Sub

GetObject MSDN

---

This is how 3 workbooks in the same instance look like:

This is how 2 workbooks look like in 2 different instances:

Pros and Cons for using multiple instances (Source answers.microsoft.com):

Pros

• If you have 32-bit Excel, each instance can use up to 3 GB memory. If you have a powerful computer, very heavy files, and 32-bit Excel, each instance of Excel can use 3 GB. So with e.g. 2 instances of Excel.exe, you could say that the total memory Excel could use triples. (Please note that this is not needed with 64-bit Excel as it is not limited by 3 GB memory per instance)

• If you want to have a separate Undo chain, so that each Undo only undos in the currently active workbook, then separate instances will indeed achieve this.

Cons

• If you want to have a common Undo chain shared by all open files, then using multiple instances will not achieve this.

• If you want to be able to e.g. press Ctrl+F6 to jump between your open files quickly, then using multiple instances will not achieve this.

• Paste Special will not work between instances. See this for more info.

• Making workbook links between 2 files in separate running instances cannot be made by clicking, and will not update in real-time.

---

The code looks ok, simply use the Set keyword:

If fo = False Then set wb = Workbooks.Open(filename:=cf)

Answer 3

Ive got a solution

If fo = False Then
    Set wb = Workbooks.Open(filename:=cf)
Else
    Dim w As Workbook
    For Each w In Workbooks
        If w.FullName = cf Then
            Set wb = w
        End If
    Next
End If

Here is in the loop its traversing through all workbook and if its there take that reference..

Answer 4

I hope this help

Dim dict As Dictionary

Function OpenFile(fileFullName As String) As Workbook


If (dict.Exists(fileFullName)) Then

OpenFile = dict.Item(fileFullName)

End If

dict.Add "fileFullName", Workbooks.Open(Filename:=fileFullName)

OpenFile = dict.Item(fileFullName)

End Function

Application.ActiveWorkbook = OpenFile(fileFullName)