Determine if Workbook is Open or Closed

[jurgenC!]

hi all,

this macro is executed from the Workbook "Master", which posts data to
the Workbook "Minor". before posting, the macro needs to determine if
the Workbook "Minor" is open or not. i created following routing:

If Workbooks.Open("Minor") = False Then
Workbooks.Open("Minor"). _
RunAutoMacros Which:=xlAutoOpen
End If

however it will asttempt to open the Workbook "Minor" regardless of its
status. not to sure where i am going wrong.

alternatively, is there a method for posting data to an other workbook,
without the destination workbook to be open?

any help or advise is mostly appreciated.

cheers...


...jurgenC!

 

[Don Guillett]

Here are various solutions that work

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
'Workbooks.Open("" & workbookname & ".xls").RunAutoMacros xlAutoOpen
Exit Sub
End Sub
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

Sub SeeIfOpen()
On Error Resume Next
Workbooks("junk.xls").Activate
If Err.Number Then MsgBox ("Not open!")
On Error GoTo 0
End Sub
Function IsOpen_MS(FileName As String) As Boolean
Dim wb As Workbook
For Each wb In Application.Workbooks
If UCase(wb.Name) = UCase(FileName) Then
IsOpen = True
Exit Function
End If
Next wb
IsOpen = False
End Function

 

[jurgenC!]

hi Don,

thanks for your reply - it was very helpful and this is what i ended up
using:


Dim wBook As Workbook
Dim bOpen As Boolean
Dim sTestcase As String
Dim sFile As String




'Determine if Workbook Open
For Each wBook In Application.Workbooks
If wBook.Name = sTestcase Then
bOpen = True
Exit For
Else
bOpen = False
End If
Next wBook

If bOpen = False Then
Workbooks.Open(sFile).RunAutoMacros Which:=xlAutoOpen
End If


cheers....


....jurgenC!