Sub to check & force open Parent.xls
 

I have 3 files in the same folder (for simplicity):
Parent.xls, Child1.xls, Child2.xls

Is there a sub I could place in Child1/2.xls which will automatically check
& if necessary "force" open Parent.xls first before Child1/2.xls itself
proceeds to open. I always want Parent.xls to be open before either
Child1.xls or Child2.xls is open. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sub to check & force open Parent.xls
 

Create a fourth workbook that you ALWAYS open before openning any of the
other three. This workbook's sole function is to open the others in the
correct order.
--
Gary''s Student - gsnu200731


"Max" wrote:

I have 3 files in the same folder (for simplicity):
Parent.xls, Child1.xls, Child2.xls

Is there a sub I could place in Child1/2.xls which will automatically check
& if necessary "force" open Parent.xls first before Child1/2.xls itself
proceeds to open. I always want Parent.xls to be open before either
Child1.xls or Child2.xls is open. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sub to check & force open Parent.xls
 

Max,

Try something like the following. If Parent.xls is already open, it does
nothing. If Parent.xls is not open, it opens Parent.xls, schedules an
OnTime to cause Excel to re-open the Child.xls workbook, then closes itself.

Sub Auto_Open()
Dim WB As Workbook
On Error Resume Next
Set WB = Workbooks("Parent.xls")
Select Case Err.Number
Case 0
' Parent.xls already open
Case 9
' Parent.xls is not open
Workbooks.Open Filename:="C:\Parent.xls"
Application.OnTime Now, Chr(39) & ThisWorkbook.Name & Chr(39) &
"!DoNothing"
ThisWorkbook.Close savechanges:=True

Case Else
MsgBox "Error: " & CStr(Err.Number) & vbCrLf & _
Err.Description
End Select
End Sub


Sub DoNothing()
' no code required.
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)



"Max" wrote in message
...
I have 3 files in the same folder (for simplicity):
Parent.xls, Child1.xls, Child2.xls

Is there a sub I could place in Child1/2.xls which will automatically
check & if necessary "force" open Parent.xls first before Child1/2.xls
itself proceeds to open. I always want Parent.xls to be open before either
Child1.xls or Child2.xls is open. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sub to check & force open Parent.xls
 

Thanks, GS. It's a discipline to "enforce" onto users who forget to always
open Parent.xls ahead of opening Child1/2.xls, despite reminders. I'm not
sure that asking them to open yet another workbook? would help here. Anyway,
could you share some sample code for try-out?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary''s Student" wrote in message
...
Create a fourth workbook that you ALWAYS open before openning any of the
other three. This workbook's sole function is to open the others in the
correct order.
--
Gary''s Student - gsnu200731

Sub to check & force open Parent.xls
 

Thanks, Chip. Where should I place the subs, within either Child1.xls &
Child2.xls? Is it within ThisWorkbook? Pl advise.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chip Pearson" wrote in message
...
Max,

Try something like the following. If Parent.xls is already open, it does
nothing. If Parent.xls is not open, it opens Parent.xls, schedules an
OnTime to cause Excel to re-open the Child.xls workbook, then closes
itself.

Sub Auto_Open()
Dim WB As Workbook
On Error Resume Next
Set WB = Workbooks("Parent.xls")
Select Case Err.Number
Case 0
' Parent.xls already open
Case 9
' Parent.xls is not open
Workbooks.Open Filename:="C:\Parent.xls"
Application.OnTime Now, Chr(39) & ThisWorkbook.Name & Chr(39) &
"!DoNothing"
ThisWorkbook.Close savechanges:=True

Case Else
MsgBox "Error: " & CStr(Err.Number) & vbCrLf & _
Err.Description
End Select
End Sub


Sub DoNothing()
' no code required.
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

Sub to check & force open Parent.xls
 

Here is an example. We have a folder with four sheets:

master.xls
parent.xls
child1.xls
child2.xls

The user is told to ONLY open the master.

The master has the following in workbook code:

Private Sub Workbook_Open()
ChDir "C:\temp"
Workbooks.Open Filename:="C:\temp\parent.xls"
Workbooks.Open Filename:="C:\temp\child1.xls"
Workbooks.Open Filename:="C:\temp\child2.xls"
Windows("master.xls").Activate
ActiveWorkbook.Close
End Sub

So the master opens the working sheets in the proper order and then excuses
itself.

--
Gary''s Student - gsnu200731


"Max" wrote:

Thanks, GS. It's a discipline to "enforce" onto users who forget to always
open Parent.xls ahead of opening Child1/2.xls, despite reminders. I'm not
sure that asking them to open yet another workbook? would help here. Anyway,
could you share some sample code for try-out?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary''s Student" wrote in message
...
Create a fourth workbook that you ALWAYS open before openning any of the
other three. This workbook's sole function is to open the others in the
correct order.
--
Gary''s Student - gsnu200731

Sub to check & force open Parent.xls
 

GS, many thanks. Tried it and it works.

But I'm still hoping that there's a way to effect the force open Parent.xls
directly should the user "inadvertently" proceed to open Child1.xls,
notwithstanding the instructions given.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary''s Student" wrote in message
...
Here is an example. We have a folder with four sheets:

master.xls
parent.xls
child1.xls
child2.xls

The user is told to ONLY open the master.

The master has the following in workbook code:

Private Sub Workbook_Open()
ChDir "C:\temp"
Workbooks.Open Filename:="C:\temp\parent.xls"
Workbooks.Open Filename:="C:\temp\child1.xls"
Workbooks.Open Filename:="C:\temp\child2.xls"
Windows("master.xls").Activate
ActiveWorkbook.Close
End Sub

So the master opens the working sheets in the proper order and then
excuses
itself.

--
Gary''s Student - gsnu200731