Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using file name as argument in function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using file name as argument in function
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using file name as argument in function
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 "Gary''s Student" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using file name as argument in function
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" -- Gary's Student gsnu200704 "jille" wrote: 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 "Gary''s Student" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using file name as argument in function
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. "Gary''s Student" wrote: 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" -- Gary's Student gsnu200704 "jille" wrote: 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 "Gary''s Student" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using file name as argument in function
What happens ? Error, wrong result ?
NickHK "jille" wrote in message ... 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. "Gary''s Student" wrote: 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" -- Gary's Student gsnu200704 "jille" wrote: 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 "Gary''s Student" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using file name as argument in function
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Function Argument | Excel Worksheet Functions | |||
text as an argument of SUM function | Excel Worksheet Functions | |||
Function (array argument, range argument, string argument) vba | Excel Programming | |||
Function Argument | Excel Programming | |||
Range as argument in function | Excel Programming |