Determine if Workbook is Open or Closed
 

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!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Determine if Workbook is Open or Closed
 

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

--
Don Guillett
SalesAid Software

"jurgenC!" <jurgen1967ATyahoo.com.au wrote in message
...
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!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Determine if Workbook is Open or Closed
 

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!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!