Auto open workbook(s)?

[Shek5150]

Hello there...

I have two spreadsheets (in two separate workbooks) that are linked and my
question is "is there a way to have Excel automatically open the second
(dependent) workbook when I manually open the first...so, I'm actually
opening both workbooks when I open just the parent workbook...

Hopefully that was clear...it seemed more coherent when it was rolling
around in my head.

Thanks,

Steve

 

[Bob I]

AutoRun macro.

 

[JLatham]

The code below should do it for you. It does assume that both workbooks are
located in the same folder. If they are not, you will have to provide the
full path and filename in the Workbooks.Open command instead of getting the
path based on the path to the parent as I have done in this code.

To put the code where it needs to be:
Open your 'parent' workbook.
Right-click on the small Excel icon immediately to the left of the word
"File" in the main Excel menu.
Choose [View Code] from the list that appears.
Copy the code below and paste it into the code module that appears.
Edit the name for the child workbook.
Save the workbook.
Close it and open it again to make sure it works.

Private Sub Workbook_Open()
'this code will open a second workbook
'with name specified by Const childName
'that is located in the same folder
'with this (parent) workbook.
Const childName = "ChildWorkbook.xls"
Dim ChildIsOpen As Boolean
Dim anyWorkbook As Workbook
For Each anyWorkbook In Workbooks
If anyWorkbook.Name = childName Then
ChildIsOpen = True
Exit For
End If
Next
If Not ChildIsOpen Then
'in case the workbook does not
'exist in this folder
On Error Resume Next ' ignore error
'try to open the child workbook
Workbooks.Open _
Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, _
Application.PathSeparator)) & childName
'clear any error that was encountered
If Err <> 0 Then
MsgBox childName & " Could not be found/opened"
Err.Clear
End If
'when other book is opened, it becomes the
'active workbook, so come back to this one.
ThisWorkbook.Activate
'reset the error trapping
On Error GoTo 0
End If
End Sub

 

[Shek5150]

Jlatham,

Thank you for your initial reply ... and I apologize for returning w/ a
follow-up question; however, when I cut/paste (w/ my workbook child names
into the code) I get the error message about "not being able to find/open
child workbook) ... this is your code w/ my child name...surely I've messed
up something ...thanks again.

Private Sub Workbook_Open()
'this code will open a second workbook
'with name specified by Const childName
'that is located in the same folder
'with this (parent) workbook.
Const childName = "Step_2.xls"
Dim ChildIsOpen As Boolean
Dim anyWorkbook As Workbook
For Each anyWorkbook In Workbooks
If anyWorkbook.Name = childName Then
ChildIsOpen = True
Exit For
End If
Next
If Not ChildIsOpen Then
'in case the workbook does not
'exist in this folder
On Error Resume Next ' ignore error
'try to open the child workbook
Workbooks.Open _
Left(Step_2.xls, InStrRev(Step_2.xls, _
Application.PathSeparator)) & Step_2.xls
'clear any error that was encountered
If Err <> 0 Then
MsgBox childName & " Could not be found/opened"
Err.Clear
End If
'when other book is opened, it becomes the
'active workbook, so come back to this one.
ThisWorkbook.Activate
'reset the error trapping
On Error GoTo 0
End If
End Sub

The code below should do it for you. It does assume that both workbooks are
located in the same folder. If they are not, you will have to provide the
full path and filename in the Workbooks.Open command instead of getting the
path based on the path to the parent as I have done in this code.

To put the code where it needs to be:
Open your 'parent' workbook.
Right-click on the small Excel icon immediately to the left of the word
"File" in the main Excel menu.
Choose [View Code] from the list that appears.
Copy the code below and paste it into the code module that appears.
Edit the name for the child workbook.
Save the workbook.
Close it and open it again to make sure it works.

Private Sub Workbook_Open()
'this code will open a second workbook
'with name specified by Const childName
'that is located in the same folder
'with this (parent) workbook.
Const childName = "ChildWorkbook.xls"
Dim ChildIsOpen As Boolean
Dim anyWorkbook As Workbook
For Each anyWorkbook In Workbooks
If anyWorkbook.Name = childName Then
ChildIsOpen = True
Exit For
End If
Next
If Not ChildIsOpen Then
'in case the workbook does not
'exist in this folder
On Error Resume Next ' ignore error
'try to open the child workbook
Workbooks.Open _
Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, _
Application.PathSeparator)) & childName
'clear any error that was encountered
If Err <> 0 Then
MsgBox childName & " Could not be found/opened"
Err.Clear
End If
'when other book is opened, it becomes the
'active workbook, so come back to this one.
ThisWorkbook.Activate
'reset the error trapping
On Error GoTo 0
End If
End Sub

Hello there...

I have two spreadsheets (in two separate workbooks) that are linked and my
question is "is there a way to have Excel automatically open the second
(dependent) workbook when I manually open the first...so, I'm actually
opening both workbooks when I open just the parent workbook...

Hopefully that was clear...it seemed more coherent when it was rolling
around in my head.

Thanks,

Steve

 

[Dave Peterson]

You changed this line:

Workbooks.Open _
Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, _
Application.PathSeparator)) & childName

Change it back what JL suggested. In fact, if you changed anything else, change
it back, too.

The only line you should have to worry about is this:

Const childName = "Step_2.xls"

That needs to be changed.

Jlatham,

Thank you for your initial reply ... and I apologize for returning w/ a
follow-up question; however, when I cut/paste (w/ my workbook child names
into the code) I get the error message about "not being able to find/open
child workbook) ... this is your code w/ my child name...surely I've messed
up something ...thanks again.

Private Sub Workbook_Open()
'this code will open a second workbook
'with name specified by Const childName
'that is located in the same folder
'with this (parent) workbook.
Const childName = "Step_2.xls"
Dim ChildIsOpen As Boolean
Dim anyWorkbook As Workbook
For Each anyWorkbook In Workbooks
If anyWorkbook.Name = childName Then
ChildIsOpen = True
Exit For
End If
Next
If Not ChildIsOpen Then
'in case the workbook does not
'exist in this folder
On Error Resume Next ' ignore error
'try to open the child workbook
Workbooks.Open _
Left(Step_2.xls, InStrRev(Step_2.xls, _
Application.PathSeparator)) & Step_2.xls
'clear any error that was encountered
If Err <> 0 Then
MsgBox childName & " Could not be found/opened"
Err.Clear
End If
'when other book is opened, it becomes the
'active workbook, so come back to this one.
ThisWorkbook.Activate
'reset the error trapping
On Error GoTo 0
End If
End Sub

The code below should do it for you. It does assume that both workbooks are
located in the same folder. If they are not, you will have to provide the
full path and filename in the Workbooks.Open command instead of getting the
path based on the path to the parent as I have done in this code.

To put the code where it needs to be:
Open your 'parent' workbook.
Right-click on the small Excel icon immediately to the left of the word
"File" in the main Excel menu.
Choose [View Code] from the list that appears.
Copy the code below and paste it into the code module that appears.
Edit the name for the child workbook.
Save the workbook.
Close it and open it again to make sure it works.

Private Sub Workbook_Open()
'this code will open a second workbook
'with name specified by Const childName
'that is located in the same folder
'with this (parent) workbook.
Const childName = "ChildWorkbook.xls"
Dim ChildIsOpen As Boolean
Dim anyWorkbook As Workbook
For Each anyWorkbook In Workbooks
If anyWorkbook.Name = childName Then
ChildIsOpen = True
Exit For
End If
Next
If Not ChildIsOpen Then
'in case the workbook does not
'exist in this folder
On Error Resume Next ' ignore error
'try to open the child workbook
Workbooks.Open _
Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, _
Application.PathSeparator)) & childName
'clear any error that was encountered
If Err <> 0 Then
MsgBox childName & " Could not be found/opened"
Err.Clear
End If
'when other book is opened, it becomes the
'active workbook, so come back to this one.
ThisWorkbook.Activate
'reset the error trapping
On Error GoTo 0
End If
End Sub

Hello there...

I have two spreadsheets (in two separate workbooks) that are linked and my
question is "is there a way to have Excel automatically open the second
(dependent) workbook when I manually open the first...so, I'm actually
opening both workbooks when I open just the parent workbook...

Hopefully that was clear...it seemed more coherent when it was rolling
around in my head.

Thanks,

Steve

 

[Shek5150]

Dave,

Thanks for the follow-up...that took care of it...rookie mistake, sorry.

Thanks,

Steve

You changed this line:

Workbooks.Open _
Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, _
Application.PathSeparator)) & childName

Change it back what JL suggested. In fact, if you changed anything else, change
it back, too.

The only line you should have to worry about is this:

Const childName = "Step_2.xls"

That needs to be changed.

Jlatham,

Thank you for your initial reply ... and I apologize for returning w/ a
follow-up question; however, when I cut/paste (w/ my workbook child names
into the code) I get the error message about "not being able to find/open
child workbook) ... this is your code w/ my child name...surely I've messed
up something ...thanks again.

Private Sub Workbook_Open()
'this code will open a second workbook
'with name specified by Const childName
'that is located in the same folder
'with this (parent) workbook.
Const childName = "Step_2.xls"
Dim ChildIsOpen As Boolean
Dim anyWorkbook As Workbook
For Each anyWorkbook In Workbooks
If anyWorkbook.Name = childName Then
ChildIsOpen = True
Exit For
End If
Next
If Not ChildIsOpen Then
'in case the workbook does not
'exist in this folder
On Error Resume Next ' ignore error
'try to open the child workbook
Workbooks.Open _
Left(Step_2.xls, InStrRev(Step_2.xls, _
Application.PathSeparator)) & Step_2.xls
'clear any error that was encountered
If Err <> 0 Then
MsgBox childName & " Could not be found/opened"
Err.Clear
End If
'when other book is opened, it becomes the
'active workbook, so come back to this one.
ThisWorkbook.Activate
'reset the error trapping
On Error GoTo 0
End If
End Sub

The code below should do it for you. It does assume that both workbooks are
located in the same folder. If they are not, you will have to provide the
full path and filename in the Workbooks.Open command instead of getting the
path based on the path to the parent as I have done in this code.

To put the code where it needs to be:
Open your 'parent' workbook.
Right-click on the small Excel icon immediately to the left of the word
"File" in the main Excel menu.
Choose [View Code] from the list that appears.
Copy the code below and paste it into the code module that appears.
Edit the name for the child workbook.
Save the workbook.
Close it and open it again to make sure it works.

Private Sub Workbook_Open()
'this code will open a second workbook
'with name specified by Const childName
'that is located in the same folder
'with this (parent) workbook.
Const childName = "ChildWorkbook.xls"
Dim ChildIsOpen As Boolean
Dim anyWorkbook As Workbook
For Each anyWorkbook In Workbooks
If anyWorkbook.Name = childName Then
ChildIsOpen = True
Exit For
End If
Next
If Not ChildIsOpen Then
'in case the workbook does not
'exist in this folder
On Error Resume Next ' ignore error
'try to open the child workbook
Workbooks.Open _
Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, _
Application.PathSeparator)) & childName
'clear any error that was encountered
If Err <> 0 Then
MsgBox childName & " Could not be found/opened"
Err.Clear
End If
'when other book is opened, it becomes the
'active workbook, so come back to this one.
ThisWorkbook.Activate
'reset the error trapping
On Error GoTo 0
End If
End Sub


:

Hello there...

I have two spreadsheets (in two separate workbooks) that are linked and my
question is "is there a way to have Excel automatically open the second
(dependent) workbook when I manually open the first...so, I'm actually
opening both workbooks when I open just the parent workbook...

Hopefully that was clear...it seemed more coherent when it was rolling
around in my head.

Thanks,

Steve

 

[JLatham]

Understandable - sometimes not everything is clear as glass in this
2-dimensional world. And that's why everybody cover's everbody else's back
in here. Today Dave had mine, one day if the opportunity arises, I'll return
the favor.

Dave,

Thanks for the follow-up...that took care of it...rookie mistake, sorry.

Thanks,

Steve

You changed this line:

Workbooks.Open _
Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, _
Application.PathSeparator)) & childName

Change it back what JL suggested. In fact, if you changed anything else, change
it back, too.

The only line you should have to worry about is this:

Const childName = "Step_2.xls"

That needs to be changed.

Jlatham,

Thank you for your initial reply ... and I apologize for returning w/ a
follow-up question; however, when I cut/paste (w/ my workbook child names
into the code) I get the error message about "not being able to find/open
child workbook) ... this is your code w/ my child name...surely I've messed
up something ...thanks again.

Private Sub Workbook_Open()
'this code will open a second workbook
'with name specified by Const childName
'that is located in the same folder
'with this (parent) workbook.
Const childName = "Step_2.xls"
Dim ChildIsOpen As Boolean
Dim anyWorkbook As Workbook
For Each anyWorkbook In Workbooks
If anyWorkbook.Name = childName Then
ChildIsOpen = True
Exit For
End If
Next
If Not ChildIsOpen Then
'in case the workbook does not
'exist in this folder
On Error Resume Next ' ignore error
'try to open the child workbook
Workbooks.Open _
Left(Step_2.xls, InStrRev(Step_2.xls, _
Application.PathSeparator)) & Step_2.xls
'clear any error that was encountered
If Err <> 0 Then
MsgBox childName & " Could not be found/opened"
Err.Clear
End If
'when other book is opened, it becomes the
'active workbook, so come back to this one.
ThisWorkbook.Activate
'reset the error trapping
On Error GoTo 0
End If
End Sub

:

The code below should do it for you. It does assume that both workbooks are
located in the same folder. If they are not, you will have to provide the
full path and filename in the Workbooks.Open command instead of getting the
path based on the path to the parent as I have done in this code.

To put the code where it needs to be:
Open your 'parent' workbook.
Right-click on the small Excel icon immediately to the left of the word
"File" in the main Excel menu.
Choose [View Code] from the list that appears.
Copy the code below and paste it into the code module that appears.
Edit the name for the child workbook.
Save the workbook.
Close it and open it again to make sure it works.

Private Sub Workbook_Open()
'this code will open a second workbook
'with name specified by Const childName
'that is located in the same folder
'with this (parent) workbook.
Const childName = "ChildWorkbook.xls"
Dim ChildIsOpen As Boolean
Dim anyWorkbook As Workbook
For Each anyWorkbook In Workbooks
If anyWorkbook.Name = childName Then
ChildIsOpen = True
Exit For
End If
Next
If Not ChildIsOpen Then
'in case the workbook does not
'exist in this folder
On Error Resume Next ' ignore error
'try to open the child workbook
Workbooks.Open _
Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, _
Application.PathSeparator)) & childName
'clear any error that was encountered
If Err <> 0 Then
MsgBox childName & " Could not be found/opened"
Err.Clear
End If
'when other book is opened, it becomes the
'active workbook, so come back to this one.
ThisWorkbook.Activate
'reset the error trapping
On Error GoTo 0
End If
End Sub


:

Hello there...

I have two spreadsheets (in two separate workbooks) that are linked and my
question is "is there a way to have Excel automatically open the second
(dependent) workbook when I manually open the first...so, I'm actually
opening both workbooks when I open just the parent workbook...

Hopefully that was clear...it seemed more coherent when it was rolling
around in my head.

Thanks,

Steve