I have a macro that is opening and closing several workbooks for my Month
End reports. One of the workbooks treats and places data slightly differently
than all the other ones, and needs different formatting instructions for that
data.
I want to check if that specific filename is open. Is there a specific
command for that???

(I could create a flag that is on or off when the file is opened or closed,
and check that flag for true/false, but I wanted to know if there is a
command to do this)

Try

Function IsFileOpen(Filename As String) As Boolean
Dim WB As Workbook
For Each WB In Workbooks
If WB.Name = Filename Or WB.FullName = Filename Then
IsFileOpen = True
Exit For
End If
Next WB
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Bill" wrote in message
...
I have a macro that is opening and closing several workbooks for
my Month
End reports. One of the workbooks treats and places data
slightly differently
than all the other ones, and needs different formatting
instructions for that
data.
I want to check if that specific filename is open. Is there a
specific
command for that???

(I could create a flag that is on or off when the file is
opened or closed,
and check that flag for true/false, but I wanted to know if
there is a
command to do this)

Hi Bill

You can use this function in a normal module

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

And use it like this in your code
If bIsBookOpen("test.xls") Then


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bill" wrote in message ...
I have a macro that is opening and closing several workbooks for my Month
End reports. One of the workbooks treats and places data slightly differently
than all the other ones, and needs different formatting instructions for that
data.
I want to check if that specific filename is open. Is there a specific
command for that???

(I could create a flag that is on or off when the file is opened or closed,
and check that flag for true/false, but I wanted to know if there is a
command to do this)

Here is a simple function you can use. It is adapted from some code that I
got from Chip Pearson.

Public Function BookOpen(SName As String) As Boolean
On Error Resume Next
BookOpen = CBool(Len(Workbooks(SName).Name))
End Function

You would use it like this...

msgbox worbookopen("MyBook.xls")
--
HTH...

Jim Thomlinson


"Bill" wrote:

I have a macro that is opening and closing several workbooks for my Month
End reports. One of the workbooks treats and places data slightly differently
than all the other ones, and needs different formatting instructions for that
data.
I want to check if that specific filename is open. Is there a specific
command for that???

(I could create a flag that is on or off when the file is opened or closed,
and check that flag for true/false, but I wanted to know if there is a
command to do this)

Sorry you would use it like...

msgbox bookopen("MyBook.xls")
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Here is a simple function you can use. It is adapted from some code that I
got from Chip Pearson.

Public Function BookOpen(SName As String) As Boolean
On Error Resume Next
BookOpen = CBool(Len(Workbooks(SName).Name))
End Function

You would use it like this...

msgbox worbookopen("MyBook.xls")
--
HTH...

Jim Thomlinson


"Bill" wrote:

I have a macro that is opening and closing several workbooks for my Month
End reports. One of the workbooks treats and places data slightly differently
than all the other ones, and needs different formatting instructions for that
data.
I want to check if that specific filename is open. Is there a specific
command for that???

(I could create a flag that is on or off when the file is opened or closed,
and check that flag for true/false, but I wanted to know if there is a
command to do this)

The suggested approach/solution(s) will work only if the workbook is open in
the CURRENT Excel session; they will fail if the file is open in another
session of Excel, such as an automation session.

This will return TRUE is the fil is open in ANY excel session.

Function WBOpen(ByVal Filename As String) As Boolean
On Error Resume Next
Set wb = GetObject(Filename)
If Not IsEmpty(wb) Then
WBOpen = True
Set wb = Nothing
End If
End Function

Thank you all. It now works.

"Bill" wrote:

I have a macro that is opening and closing several workbooks for my Month
End reports. One of the workbooks treats and places data slightly differently
than all the other ones, and needs different formatting instructions for that
data.
I want to check if that specific filename is open. Is there a specific
command for that???

(I could create a flag that is on or off when the file is opened or closed,
and check that flag for true/false, but I wanted to know if there is a
command to do this)

<FWIW

While the suggested approache(s)/solution(s) do address the OP's situation
explicitly (ie. his current session), your suggestion doesn't work because
it returns TRUE if the file exists, whether it's open or not, or in use or
not.

You might also want to specify that this test requires that wb be Variant
type. Even though it appears that it is (implicit in your code), someone
reading this may spent a lot of frustrating time trying to figure that out
using Option Explicit.


Regards,
Garry