VBA How to Know if a Workbook is opened?

[jamiguel77]

hi all

i have this VBA code:


Dim wbk As Workbook
Dim wbk2 As Workbook

Set wbk2 = Workbooks.Open(Filename:=F2)
Set wbk = Workbooks.Open(Filename:=F1)

of course F1 and F2 is a Correct filenames with path.....

My question is how to know if workbook: wbk is already opened? (for not reopen....) and of course assign to variable something like this:

if (Workbooks.Open(Filename:=F1) = opened ) then

else
Workbooks.Open(Filename:=F1)
endif

thanks

 

[Trebor76]

Did you ever look at this udf I wrote for you yesterday?

 

[jamiguel77]

yes, i see, and know but this time i need know if workbook was opened before..

how to?

Thanks

 

[Trebor76]

You could have adapted my solution there to this query?

Anyway, try this where I've included code to make sure the file exists first:

Option Explicit
Sub Macro2()

    Dim blnFileExists As Boolean
    Dim objFSO As Object
    Dim strFullPath As String
    Dim wbk As Workbook
    
    Application.ScreenUpdating = False
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Full path (directory + filename) to be checked for existence. Change to suit.
    strFullPath = "C:\Users\Documents\Test File.xlsx"
    blnFileExists = objFSO.FileExists(strFullPath)
    
    'If the file exists, then...
    If blnFileExists = True Then
        On Error Resume Next
            '...see if it's open in the current Excel session
            Set wbk = Workbooks(objFSO.GetFileName(strFullPath))
            'If the file is not open, then...
            If Err.Number <> 0 Then
                '...open it
                Set wbk = Workbooks.Open(strFullPath)
            End If
        On Error GoTo 0
    End If
    
    Application.ScreenUpdating = True

End Sub

You could put each filename in an array and then check each item in that array with my code above.

Regards,

Robert

 

[Jaafar Tribak]

@jamiguel77

Is the workbook opened in a seperate excel instance or in the same excel instance ?

 

[jamiguel77]

Thanks.

Solved my Problem:

Declared the variables wbk and wbk2 as public... and i can use the variable in my program.

Thanks