Ps. I changed the starting folder and the name of the file (.xls). I didn't
have a Z: drive and I tested in xl2003.



RoofIL wrote:

I'm not a programmer. I don't know how to write code. I did a search for
what I needed and that is how I found the code below. Like I said, I just
copied and pasted it and replaced the filename with mine, and it worked
fine.... until I decided to save the completed forms in a different folder.
Then when I tried to open those from that folder, it tried to look for the
second workbook in that folder (because that is what the code is telling it
to do), but it isn't there.

I understand that it would be a lot to look through. What if I narrow it
down for you. The file is on my Z: drive. Is that narrow enough? If not, I
could even go down to Z:\Company Files. How do I fix the code below to do
this?

"Dave Peterson" wrote:

Any folder is quite a lot of folders.

It could be on my C: drive. My D: drive. Or any A: to Z: drive that I have
mapped. And it could be on any network drive that I have access to -- and those
don't have to be mapped.

This doesn't start the removable media -- I have CDs, DVDs, thumbdrives, and
even floppies!

How would your program even know where to start -- and how would it know that it
found the correct version--there could be millions!

ps. Look at ThisWorkbook.path instead of using that left/instrrev stuff.

pps. If you decide that you know what folder to check, you could hardcode it in
your code or maybe put it in a cell (on a hidden sheet???).

RoofIL wrote:

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.

--

Dave Peterson
.


--

Dave Peterson