auto open from any folder

[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.

 

[Dave Peterson]

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]

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]

It's not a matter of writing code. It's a matter of really wanting to look
through 10's or 100's or 1,000,000's of folders--well, if you don't begin at a
reasonable starting folder.

You may find that it's quicker to drop this search idea and just find it
manually and open it when you need it.

But if you want to try, then I'd start as close as possible to that correct
location:

Option Explicit
Const StartHere As String = "C:\my documents"
Const FileNameToFind As String = "Holthaus D-L Estimating Guide.xls"
Dim UseThisFile As String
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.

Dim ChildWkbk As Workbook

Dim ChildIsOpen As Boolean

Set ChildWkbk = Nothing
On Error Resume Next
Set ChildWkbk = Workbooks(FileNameToFind)
On Error GoTo 0

If ChildWkbk Is Nothing Then
'it's not open
Call FoldersInFolder(myFolderName:=StartHere)

If UseThisFile = "" Then
MsgBox "It wasn't found in: " & StartHere
Else
On Error Resume Next
Set ChildWkbk = Workbooks.Open(Filename:=UseThisFile)
On Error GoTo 0
If ChildWkbk Is Nothing Then
MsgBox "Found, but failed to open: " & UseThisFile
End If
End If
Me.Activate
End If
End Sub
Sub FoldersInFolder(myFolderName As String)

Dim FSO As Object
Dim myBaseFolder As Object
Dim myFolder As Object

Dim TestStr As String

Set FSO = CreateObject("scripting.filesystemobject")

Set myBaseFolder = FSO.GetFolder(myFolderName)

TestStr = ""
On Error Resume Next
TestStr = Dir(myBaseFolder.path & "\" & FileNameToFind)
On Error GoTo 0

If TestStr = "" Then
'keep looking
Else
'found it!
UseThisFile = myBaseFolder.path & "\" & FileNameToFind
End If

If UseThisFile = "" Then
'keep looking
For Each myFolder In myBaseFolder.SubFolders
Call FoldersInFolder(myFolder.path)
If UseThisFile = "" Then
'keep looking
Else
Exit For
End If
Next myFolder
End If

End Sub

 

[Dave Peterson]

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]

This worked for my purpose. Thank you very much. In the end, it will be
quicker this way. The second workbook has to be open everytime the first
workbook is open, but there is too much data to just include it as a
worksheet (It is constantly saved as new versions and would take up too much
space). Thanks again.