Using file name as argument in function

[Guest]

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
procedure:
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

 

[Guest]

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)

 

[Guest]

I'm missing something...I think your function does the same thing as mine. I
think my prblem is the quotation marks...you hardcoded the name of the
file...mine is in a variable. When you hardcode the name, it works but not
when I include it as a variable.

Am I missing something?

Thx

 

[Guest]

Should work either way. I had a little trouble getting it work at first.
What I DID find (the reason there's a MSGBOX in the function) is that the
string has to be something like "Book1" and not "Book1.xls"

 

[Guest]

Hi,

Thanks, I was aware that the filename was "book1" without the '.xls'.

I haven't had any success passing the file name from a property (ie
workbook.name)to a variable and then using the variable. I tested the
variable and it does contain the string but then I can't seem to use the
variable in the context of the function.

 

[NickHK]

What happens ? Error, wrong result ?

NickHK

Hi,

Thanks, I was aware that the filename was "book1" without the '.xls'.

I haven't had any success passing the file name from a property (ie
workbook.name)to a variable and then using the variable. I tested the
variable and it does contain the string but then I can't seem to use the
variable in the context of the function.

 

[Chip Pearson]

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)