Public Function IsWorkbookOpen(WBName As String) As Boolean
IsWorkbookOpen = False
For Each w In Workbooks
MsgBox (w.Name)
If w.Name = WBName Then
IsWorkbookOpen = True
Exit Function
End If
Next
End Function

to be used:


Dim s as String
s = "Book1"
result = IsWorkbookOpen(s)
--
Gary's Student
gsnu200704


"jille" wrote:

This is probably a really easy question.

I'm trying to reference an open, unsaved file. I have created a function to
test for whether it's open but it's not working with my example. The problem,
I am sure, relates to how to represent the quotation marks required around
the file name. If I test it with a real name (instead of a variable) it seems
to work.

Here is the function:
Public Function IsWorkbookOpen(WBName As String) As Boolean
On Error Resume Next
IsWorkbookOpen = CBool(Len(Excel.Application.Workbooks(WBName).Name ))
End Function

Here is the code (where vBulkFileName contains the name of thefile) in my
procedu
With Excel.Application
Result = IsWorkbookOpen(""""" & vBulkFileName & """"")
MsgBox (Result)
End With

So...how do I represent the filename to include the quotation marks. As I
said, if I simply type in the name of the file, i.e. "book1", it works
perfectly.

Help & thanks!

jille