Function to Say if File is Open

[MikeG]

In a cell D4 in my worksheet, I have the filepath and name of a file I wish to open, e.g, C:\Mike1\abc.xlsm

Could anyone give me VBA for a user defined function that I could use in say, cell E4, that would return TRUE if the file is already open in the current instance of Excel?

Thanks,

Mike

 

[njimack]

Take a look at the last function here: Spreadsheet Page Excel Tips: Some Useful VBA Functions

 

[MikeG]

Thanks Neil - looks just like what I want.

However, when I use it, it currently working - I open a workbook, and then do the test, but it returns false. At the moment my name to check has the full filepath and then the workbook name - no quote marks.

Do you know what I should have so that the right value of wbName is submitted?

Thanks,

Mike

 

[MikeG]

Sorry, line 2 should say "it is currently not working"

 

[MikeG]

Sorry, line 2 should say "it is currently not working"

 

[njimack]

The function I pointed out doesn't consider the path. It tries to set a variable (X) to a workbook with the name you pass to it. If this returns an error, it means the workbook isn't open (or doesn't exist). If it doesn't return an error, then it's already open.

 

[MikeG]

The function I pointed out doesn't consider the path. It tries to set a variable (X) to a workbook with the name you pass to it. If this returns an error, it means the workbook isn't open (or doesn't exist). If it doesn't return an error, then it's already open.

Thanks,

I just created a workbook called Test.xlsm - I saved it and left it open. I then created another workbook and included the function and referred to cell A1 as the input. But no matter what I put in cell A1, the function still says FALSE. In A1 I have tried Test, Test.xlsm, "Test", and "Test.xlsm" with no luck.

I must be doing something wrong.

 

[MikeG]

If I change the code from:

Set x = Workbooks("wbname")

to: Set x = Workbooks("Test.xlsm")

it works.

Hmmmm

 

[Trebor76]

You shouldn't change the function, just create a macro to call it i.e. Macro1 will test if the workbook "Test.xslm" is open in the same session (True) or not (False):

Option Explicit
Private Function WorkbookIsOpen(wbname) As Boolean
'   Returns TRUE if the workbook is open
    Dim x As Workbook
    On Error Resume Next
        Set x = Workbooks(wbname)
        If Err = 0 Then WorkbookIsOpen = True _
            Else WorkbookIsOpen = False
    On Error GoTo 0
End Function
Sub Macro1()

    WorkbookIsOpen ("Test.xlsm")

End Sub

HTH

Robert

 

[MikeG]

You shouldn't change the function, just create a macro to call it i.e. Macro1 will test if the workbook "Test.xslm" is open in the same session (True) or not (False):

Option Explicit
Private Function WorkbookIsOpen(wbname) As Boolean
'   Returns TRUE if the workbook is open
    Dim x As Workbook
    On Error Resume Next
        Set x = Workbooks(wbname)
        If Err = 0 Then WorkbookIsOpen = True _
            Else WorkbookIsOpen = False
    On Error GoTo 0
End Function
Sub Macro1()

    WorkbookIsOpen ("Test.xlsm")

End Sub

HTH

Robert

Thanks Robert,

I notice in your version, you have Set x = Workbooks(wbname)

whereas in the original, the code was Set x = Workbooks("wbname"). I will check, but maybe that's the issue.

MikeG