Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto open workbook(s)?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto open workbook(s)?
AutoRun macro.
Shek5150 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto open workbook(s)?
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto open workbook(s)?
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 "JLatham" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto open workbook(s)?
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. Shek5150 wrote: 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 "JLatham" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto open workbook(s)?
Dave,
Thanks for the follow-up...that took care of it...rookie mistake, sorry. Thanks, Steve "Dave Peterson" wrote: 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. Shek5150 wrote: 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 "JLatham" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto open workbook(s)?
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. "Shek5150" wrote: Dave, Thanks for the follow-up...that took care of it...rookie mistake, sorry. Thanks, Steve "Dave Peterson" wrote: 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. Shek5150 wrote: 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 "JLatham" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to code VBA: Auto open workbook and run macro | Excel Discussion (Misc queries) | |||
excel 2003 saved file will not open without a blank workbook open | Excel Discussion (Misc queries) | |||
Sometimes formuli in workbook don't auto update with auto-recal on | Excel Worksheet Functions | |||
Search open sheets in workbook and insert into open sheet | Excel Discussion (Misc queries) | |||
Excel workbook does not open in open window on desktop | Excel Discussion (Misc queries) |