Just to add to Per's response.
His function doesn't need and can't use the path.
This kind of thing would fail to do what you want:
workbooks("C:\somepath\somename.xls")
The workbooks collection only cares about the name of the workbook.
You could enhance Per's function to look to see if there is any book1.xls open.
And if there is, look to see if it's the one from the folder you want.
Option Explicit
Sub testme()
MsgBox IsWorkBookOpen(myPath:="C:\my documents\excel", _
WkbkName:="Book1.xls")
End Sub
Function IsWorkBookOpen(myPath As String, WkbkName As String) As String
Dim TestWkbk As Workbook
Dim myMsg As String
Set TestWkbk = Nothing
On Error Resume Next
Set TestWkbk = Workbooks(WkbkName)
On Error GoTo 0
If TestWkbk Is Nothing Then
myMsg = "Not Open"
Else
'remove the trailing slash if there
If Right(myPath, 1) = "\" Then
myPath = Left(myPath, Len(myPath) - 1)
End If
If LCase(myPath) = LCase(TestWkbk.Path) Then
myMsg = "It's Open!"
Else
myMsg = "A file with the same name is open!"
End If
End If
IsWorkBookOpen = myMsg
End Function
You could just pass it the fullname (path and name) and do some parsing in the
function if you wanted.
Robert Crandal wrote:
>
> Hi Per Jessen
>
> Does your function need to have the path along with
> the filename itself?? I'm just curious if it would better
> to use "C:\Mydata\Book1.xls" as opposed to just
> "Book1.xls" as the parameter to your function.
>
> thank u
>
> "Per Jessen" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Hi
> >
> > Look at this:
> >
> > Sub Test()
> > If Not IsWbOpen("Book1.xls") then
> > Workbooks.Open("Book1.xls")
> > End If
> > End Sub
> >
> > Function IsWbOpen(WbName As String) As Boolean
> > For Each wb In Application.Workbooks
> > If wb.Name = WbName Then
> > IsWbOpen = True
> > Exit For
> > End If
> > Next
> > End Function
> >
--
Dave Peterson
|
Similar Threads
