Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if Excel file is already open
I need to check if an Excel file is already open.
How do i do this? thank u |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if Excel file is already open
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 Regards, Per "Robert Crandal" skrev i meddelelsen ... I need to check if an Excel file is already open. How do i do this? thank u |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if Excel file is already open
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if Excel file is already open
Try this
Sub nn() MsgBox IsBKOpen("MyBook.xls") End Sub Function IsBKOpen(ByRef BName As String) As Boolean On Error Resume Next IsBKOpen = Not (Application.Workbooks(BName) Is Nothing) End Function Mike "Robert Crandal" wrote: I need to check if an Excel file is already open. How do i do this? thank u . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if Excel file is already open
Hi Robert
No path needed in my function. Regards, Per "Robert Crandal" skrev i meddelelsen ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if Excel file is already open
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How check if file to open is Excel or if Excel file is corrupt? | Excel Programming | |||
How to check for Open File! | Excel Discussion (Misc queries) | |||
how to check if more than one excel file is open? | Excel Programming | |||
execl to check if another excel file is open... | Excel Programming | |||
How to check Excel file already Open | Excel Programming |