Check if a document is open
 

Hi,

I have a lot of Excel (Microsoft office XP) files that are all linked to a
main source document. I need to open that source document each time I open
one of the files so they can update. I hide the source document when it
opens. I have a functional code in VBA, but I am not the real user of the
documents and I need it to be more user-friendly. I need to prevent the
alerts asking if I want to re-open the the source document each time and
warning that any unsaved information will be discarded. I always click "no"
because I never want to reopen the source document if it is already open, and
then it gives an error '1004' and asks to end or debug.

Is there a way to check if the source document is already open and take no
action if it is, but then if it is not already open, open it?

Here is the code:

'In the "This Workbook" object of each VBA Project

Private Sub Workbook_Open()

Application.ScreenUpdating = False
'Opening the main document:
Workbooks.Open "Workbook.xls"
'Hiding it:
ActiveWindow.Visible = False
Application.ScreenUpdating = True
End Sub


Can someone help me out?

Thanks
--
AnthonyJ

Check if a document is open
 

Private Sub Workbook_Open()

Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("Workbook.xls")
If Not wb Is Nothing Then Exit Sub
On Error GoTo 0

Application.ScreenUpdating = False
'Opening the main document:
Workbooks.Open "Workbook.xls"
'Hiding it:
ActiveWindow.Visible = False
Application.ScreenUpdating = True
End Sub

--
Charles Chickering

"A good example is twice the value of good advice."


"AnthonyJ" wrote:

Hi,

I have a lot of Excel (Microsoft office XP) files that are all linked to a
main source document. I need to open that source document each time I open
one of the files so they can update. I hide the source document when it
opens. I have a functional code in VBA, but I am not the real user of the
documents and I need it to be more user-friendly. I need to prevent the
alerts asking if I want to re-open the the source document each time and
warning that any unsaved information will be discarded. I always click "no"
because I never want to reopen the source document if it is already open, and
then it gives an error '1004' and asks to end or debug.

Is there a way to check if the source document is already open and take no
action if it is, but then if it is not already open, open it?

Here is the code:

'In the "This Workbook" object of each VBA Project

Private Sub Workbook_Open()

Application.ScreenUpdating = False
'Opening the main document:
Workbooks.Open "Workbook.xls"
'Hiding it:
ActiveWindow.Visible = False
Application.ScreenUpdating = True
End Sub


Can someone help me out?

Thanks
--
AnthonyJ

Check if a document is open
 

This will return TRUE if the workbook name in s is open:

Function IsOpen(s As String) As Boolean
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name = s Then
IsOpen = True
Exit Function
End If
Next
IsOpen = False
End Function
--
Gary''s Student


"AnthonyJ" wrote:

Hi,

I have a lot of Excel (Microsoft office XP) files that are all linked to a
main source document. I need to open that source document each time I open
one of the files so they can update. I hide the source document when it
opens. I have a functional code in VBA, but I am not the real user of the
documents and I need it to be more user-friendly. I need to prevent the
alerts asking if I want to re-open the the source document each time and
warning that any unsaved information will be discarded. I always click "no"
because I never want to reopen the source document if it is already open, and
then it gives an error '1004' and asks to end or debug.

Is there a way to check if the source document is already open and take no
action if it is, but then if it is not already open, open it?

Here is the code:

'In the "This Workbook" object of each VBA Project

Private Sub Workbook_Open()

Application.ScreenUpdating = False
'Opening the main document:
Workbooks.Open "Workbook.xls"
'Hiding it:
ActiveWindow.Visible = False
Application.ScreenUpdating = True
End Sub


Can someone help me out?

Thanks
--
AnthonyJ

Check if a document is open
 

Thanks a lot for the help. It works great. You responded so fast...
--
AnthonyJ


"Charles Chickering" wrote:

Private Sub Workbook_Open()

Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("Workbook.xls")
If Not wb Is Nothing Then Exit Sub
On Error GoTo 0

Application.ScreenUpdating = False
'Opening the main document:
Workbooks.Open "Workbook.xls"
'Hiding it:
ActiveWindow.Visible = False
Application.ScreenUpdating = True
End Sub

--
Charles Chickering

"A good example is twice the value of good advice."


"AnthonyJ" wrote:

Hi,

I have a lot of Excel (Microsoft office XP) files that are all linked to a
main source document. I need to open that source document each time I open
one of the files so they can update. I hide the source document when it
opens. I have a functional code in VBA, but I am not the real user of the
documents and I need it to be more user-friendly. I need to prevent the
alerts asking if I want to re-open the the source document each time and
warning that any unsaved information will be discarded. I always click "no"
because I never want to reopen the source document if it is already open, and
then it gives an error '1004' and asks to end or debug.

Is there a way to check if the source document is already open and take no
action if it is, but then if it is not already open, open it?

Here is the code:

'In the "This Workbook" object of each VBA Project

Private Sub Workbook_Open()

Application.ScreenUpdating = False
'Opening the main document:
Workbooks.Open "Workbook.xls"
'Hiding it:
ActiveWindow.Visible = False
Application.ScreenUpdating = True
End Sub


Can someone help me out?

Thanks
--
AnthonyJ