Is open.

[Tom]

Hello,

how is most easy way for finding workbook (for example data.xls) is open?

Thanks Tom

 

[Dave Hawley]

Hi Tom

Push Alt+F11 and go to Insert>Module and paste in this code

Function Workbook_Open(WbookName As String) As Boolean
Dim wBookCheck As Workbook
Application.Volatile
On Error Resume Next
Set wBookCheck = Workbooks(WbookName)
Workbook_Open = Not wBookCheck Is Nothing
On Error GoTo 0
End Function

Go back to Excel and in any cell Enter

=Workbook_Open("Book1.xls")

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Tom]

I need something for macro like this:

Is data.xls open then myvar = True

Is it possible?

 

[Dave Hawley]

Sub IsWorkbook_Open()
Dim wBookCheck As Workbook
Application.Volatile
On Error Resume Next
Set wBookCheck = Workbooks("Data.xls")
myvar = Not wBookCheck Is Nothing
MsgBox myvar
On Error GoTo 0
End Sub

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Don Guillett]

Here is one I use, from a wb name typed in a cell, to open if closed or
activate if open

Sub GetWorkbook()
If ActiveCell.Value = "" Then Exit Sub
workbookname = ActiveCell.Value
On Error GoTo OpenWorkbook
Windows(workbookname & ".xls").Activate
Exit Sub
OpenWorkbook:
Workbooks.Open(workbookname & ".xls").RunAutoMacros xlAutoOpen
End Sub

And one from Dave
Sub GetWorkbookA() 'Dave Hawley
Dim wBook As Workbook
On Error Resume Next
Set wBook = Workbooks("Book1.xls")
If wBook Is Nothing Then
Workbooks.Open ' <File and path>
Else
wBook.Activate
End If
On Error GoTo 0
End Sub

 

[JON JON]

Another way.

Function IsFileOpen(myfilename As String) As Boolean
Dim wb As Workbook
IsFileOpen = False
For Each wb In Application.Workbooks
If wb.Name = myfilename Then
IsFileOpen = True
Exit For
End If
Next wb
End Function