code to verify if workbook is open
- Thread starter asafraz81
- Start date
vijay.vizzu
Member
Dear asafraz81
Please find below URL: http://www.ozgrid.com/forum/showthread.php?t=72190
change as required
[pre]
[/pre]
Please find below URL: http://www.ozgrid.com/forum/showthread.php?t=72190
change as required
[pre]
Code:
Sub IsWorkbookOpen()
Dim wbName As String
Dim wb As Workbook
' First you pass the sub the filename somehow, inputbox or cell
' or Sub parameter or whatever. Note: Include ".xls" if the file
' has been saved
wbName = "Phred.xls"
' check all open workbooks
For Each wb In Workbooks
If wb.Name = wbName Then
' bail
Exit Sub
' or something else
MsgBox (wbName & " is open")
Goto endo
End If
Next wb
' Wasn't found by 'If' so open the workbook
' with your code here
endo:
End Sub
jeffreyweir
Active Member
Or you can have a dedicated function that you call. So your code would go:
If not WorkBookOpen "SomeWorkbook" then ...
[pre]
[/pre]
If not WorkBookOpen "SomeWorkbook" then ...
[pre]
Code:
Function WorkbookOpen(WorkbookName As String, _
Optional WorkbookObject As Workbook = Nothing) As Boolean
' Description:Determine if a Workbook is open
' Parameters: WorkbookName Name to find in the Workbooks collection
' WorksheetObject If requested, Workbook will be put here
' Example: If WorkBookOpen("Book1") then ...
' Date Ini Modification
' 7/03/12 JSW Initial Programming
On Error GoTo ErrHandler
WorkbookOpen = False 'Assume not found
Dim Object As Workbook
For Each Object In Application.Workbooks
If Object.Name = WorkbookName Then
WorkbookOpen = True
Set WorkbookObject = Object
Exit For
End If
Next
On Error GoTo ErrHandler
ErrHandler:
If Err.Number <> 0 Then MsgBox _
"WorkbookOpen - Error#" & Err.Number & vbCrLf & _
Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
On Error GoTo 0
End Functionjeffreyweir
Active Member
This won't work if the book was open in another instance of excel, though. Some good stuff at http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open-using-vba on this.
jeffreyweir
Active Member
asafraz81 - note Vijay's comment change as required
The code he has posted is a guide only. You need to make changes to it to suit your situation, and if it is not working then post your code - including changes - here so that others can see it.
The code he has posted is a guide only. You need to make changes to it to suit your situation, and if it is not working then post your code - including changes - here so that others can see it.
Marc L
Excel Ninja
Hi asafraz81,
another way with my simplest function :
another way with my simplest function :
Code:
Function IsOpen(Name$) As Boolean
On Error Resume Next
IsOpen = IsObject(Workbooks(Name))
End FunctionTo work, don't forget to put the right file extension at the end of the name like .xls, .xlsx, .xlsb, .xlsm …
Example :
If IsOpen("Customer accounts .xlsx") then …
Example :
If IsOpen("Customer accounts .xlsx") then …
jeffreyweir
Active Member
Short and sweet. Only problem is that again it only counts books open in the same instance of Excel.
hi again
i tried this code again and its not finding the workbook name.
for instance i created 2 workbooks: workbook1 and workbook2.
then i try thi code below:
Sub IsWorkbookOpen1()
Dim wbName As String
Dim wb As Workbook
wbName = "worksheet1.xlsm"
For Each wb In Workbooks
If wb.Name = wbName Then
Exit Sub
Else
MsgBox ("file closed")
End If
Next wb
End Sub
the code always goes to the msgbox and tell me thatthe file is closed.
please help!!!
i tried this code again and its not finding the workbook name.
for instance i created 2 workbooks: workbook1 and workbook2.
then i try thi code below:
Sub IsWorkbookOpen1()
Dim wbName As String
Dim wb As Workbook
wbName = "worksheet1.xlsm"
For Each wb In Workbooks
If wb.Name = wbName Then
Exit Sub
Else
MsgBox ("file closed")
End If
Next wb
End Sub
the code always goes to the msgbox and tell me thatthe file is closed.
please help!!!
hi
probably i am doing something wrong the function is not working as well.
i tried the function and procedure below like marc suggessted:
Function IsOpen(Name$) As Boolean
On Error Resume Next
IsOpen = IsObject(Workbooks("workbook1 .xlsm"))
End Function
Sub find1()
If IsOpen("workbook1 .xlsm") Then
MsgBox ("file open")
Else
MsgBox ("file closed")
End If
End Sub
probably i am doing something wrong the function is not working as well.
i tried the function and procedure below like marc suggessted:
Function IsOpen(Name$) As Boolean
On Error Resume Next
IsOpen = IsObject(Workbooks("workbook1 .xlsm"))
End Function
Sub find1()
If IsOpen("workbook1 .xlsm") Then
MsgBox ("file open")
Else
MsgBox ("file closed")
End If
End Sub
SirJB7
Excel Rōnin
Hi, asafraz81!
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.
Regards!
PS: And I ask again, have you previously saved your 2 workbooks?
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.
Regards!
PS: And I ask again, have you previously saved your 2 workbooks?