If you are using a variable to pass the workbook name to the function, you
don't use quotes at all. If you are passing a literal string to the
function, use quotes. E.g.,
Public Function IsWorkbookOpen(WBName As String) As Boolean
On Error Resume Next
IsWorkbookOpen = CBool(Len(Excel.Application.Workbooks(WBName).Name ))
End Function
Sub AAA()
Dim WBName As String
Debug.Print IsWorkbookOpen("Book1.xls") 'Literal Name, use quotes
WBName = "Book1.xls"
Debug.Print IsWorkbookOpen(WBName) ' Variable, no quotes
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"jille" wrote in message
...
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