R
RoofIL
I copied and pasted the following code to the "This Workbook" object in
workbook1, and substituted my info where appropriate. I found this in a
previous post by someone else. I wanted a second workbook to automatically
open when I opened the first, because the second workbook contains data lists
that I need for drop-down lists in the first notebook. The first notebook is
an estimating form that, when completed is saved as a new filename everytime
(named for that estimate) in a different folder. The second notebook has the
price lists.
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 = "Holthaus D-L Estimating Guide.xlsx"
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
My problem is that this code tells it to open the file "Holthaus D-L
Estimating Guide.xlsx" located in the same folder. After I save a completed
form as a new name in the completed estimated folder, the file "Holthaus D-L
Estimating Guide.xlsx" is no longer in the same folder as the new file. I
need this code to look for the file "Holthaus D-L Estimating Guide.xlsx" in
any folder.
workbook1, and substituted my info where appropriate. I found this in a
previous post by someone else. I wanted a second workbook to automatically
open when I opened the first, because the second workbook contains data lists
that I need for drop-down lists in the first notebook. The first notebook is
an estimating form that, when completed is saved as a new filename everytime
(named for that estimate) in a different folder. The second notebook has the
price lists.
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 = "Holthaus D-L Estimating Guide.xlsx"
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
My problem is that this code tells it to open the file "Holthaus D-L
Estimating Guide.xlsx" located in the same folder. After I save a completed
form as a new name in the completed estimated folder, the file "Holthaus D-L
Estimating Guide.xlsx" is no longer in the same folder as the new file. I
need this code to look for the file "Holthaus D-L Estimating Guide.xlsx" in
any folder.