• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

code to verify if workbook is open

A

asafraz81

Member
hi,

i have macro to open a workbook.

i want to add single statement before to check if the workbook is already open

please help.
 
Dear asafraz81


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
[/pre]
 
Or you can have a dedicated function that you call. So your code would go:


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 Function
[/pre]
 
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.
 
question to vijay.vizzu

its not working.

can you check it.

the fot each loop seems to be very simple but not work.

thanks again...:)
 
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.
 
Hi asafraz81,

another way with my simplest function :​
Code: 
Function IsOpen(Name$) As Boolean
On Error Resume Next
IsOpen = IsObject(Workbooks(Name))
End Function
To 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 …
​
 
Same purpose with another collection :​
Code: 
Function ExistWindow(Name$) As Boolean
On Error Resume Next
ExistWindow = IsObject(Application.Windows(Name))
End Function
 
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!!!
 
Hi, azafraz81!

When you say "I created 2 workbooks: workbook1 and workbook2", have you saved them?

Regards!
 
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
 
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?
 
Hi, asafraz81!

Read yet the green sticky posts? If you did, then you should have noticed this:

http://chandoo.org/forums/topic/posting-a-sample-workbook

Regards!
 
hi,

i upload the workbooks to rapidshare.

https://www.rapidshare.com/#myrs_filemanager/file/0


notice that the two workbooksare empty i just want the macro to work.

notce in my code that from workbook2 i search for if workbook1 is open.


thanks again for your patience.

asaf
 
You must log in or register to reply here.
Back
Top