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

 

[MikeG]

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

Actually I am wrong - no quotes in original either, so I must have mistakenly added.

 

[MikeG]

I still can't get it to work. If anyone has the patience(!) could you try opening a Test.xlsm workbook. Then in another workbook type Test1.xlsm in A1 and use the udf referring to A1.

Thanks!

Mike

 

[Trebor76]

This works for me:

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()

    Range("B2").Value = WorkbookIsOpen(CStr(Range("A1")))

End Sub

HTH

Robert

 

[MikeG]

This works for me:

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()

    Range("B2").Value = WorkbookIsOpen(CStr(Range("A1")))

End Sub

HTH

Robert

Thanks Robert - that also work for me when I run your Macro 1. But I want to use the function in the workbook itself.

What happens if you type this in say Cell C2:

=WorkbookIsOpen(A1)

Thanks,

Mike

 

[Trebor76]

Hi Mike,

Try this in cell C2:

=WorkbookIsOpen(T(A1))

Regards,

Robert

 

[MikeG]

Hi Mike,

Try this in cell C2:

=WorkbookIsOpen(T(A1))

Regards,

Robert

It works!

Thanks.

I was at my wits end!

Could you tell me what T is and does?

Mike

 

[Trebor76]

The WorkbookIsOpen udf needs the workbook name passed to it as a string, which in VBA we use the SCtr function to do the job while in a worksheet it's simply called the T function (as long as the cell is not entirely numeric).

The other way would be to change the WorkbookIsOpen udf to convert whatever is passed to it to a string, but that may not help others trying to use it.

 

[MikeG]

The WorkbookIsOpen udf needs the workbook name passed to it as a string, which in VBA we use the SCtr function to do the job while in a worksheet it's simply called the T function (as long as the cell is not entirely numeric).

The other way would be to change the WorkbookIsOpen udf to convert whatever is passed to it to a string, but that may not help others trying to use it.

Thanks for the explanation.

Mike

 

[Trebor76]

You're welcome. I'm glad we got it sorted for you

BTW SCtr should read CStr

Regards,

Robert