Open workbook already open

[Geordie]

hi

i have the following problem

i have a userform which opens a file

the problem is that the userform can opt to open the file again

i can either close the file after use
or
enter some code which will determine whether the file is already open

i can do the first bit but it isnt ideal

the second part is the bit im stuff on

any help would be appreciated

thanks in advance

[Bob Phillips]

On Error Resume Next
Set wb = Workbooks("C:\files\myworkbook.xls")
On Error Goto 0

If wb Is Nothing Then

Set wb = Workbooks.Open "C:\files\myworkbook.xls"
End If

[DonkeyOte]

Bob, given full file name would that work if the file were already open ?

Slight variation on Bob's post:

Sub Example()
Dim wbEx As Workbook
Const cPath = "C:\files\"
Const cFileName = "myworkbook.xls"
On Error Resume Next
Set wbEx = Workbooks(cFileName)
On Error GoTo 0
If wbEx Is Nothing Then
    Workbooks.Open cPath & cFileName
End If
Set wbEx = Nothing
End Sub

[Marcol]

Another approach might be.

Option Explicit

Sub Example()
    Dim wbEx As Workbook
    Dim i As Integer
    
    Const cPath = "C:\files\"
    Const cFileName = "myworkbook.xls"
       
    For i = 1 To Application.Workbooks.Count
        If Application.Workbooks(i).Name = cFileName Then
            Set wbEx = Workbooks(cFileName)
            Exit For
        End If
    Next
    If wbEx Is Nothing Then
        If Dir(cPath & cFileName) <> "" Then
            Workbooks.Open cPath & cFileName
            Set wbEx = Workbooks(cFileName)
        Else
            MsgBox cPath & cFileName & Chr(10) & Chr(10) & "Could not be found"
            Exit Sub
        End If
    End If
    ' begin your code here
    
End Sub