Hi all, In Folder "C:\Documents\Data" I have about 10 Subfolders in
each Subfolders I have about 20 excel files. All excel files names
are something like (see below)

523699.xlsx
568944 - pg 1.xlsx
568944 - pg 2.xlsx
578944 - 578945.xlsx
588771.xlsx
etc……

I want macro assigned to a button and I want that when I click on the
button macro should look the file name in cell A1 and then look that
file in all the Subfolders and then when file is found macro should
open that file else pop up MsgBox saying "File is not found". For
example if I put file name (like given above) 523699 in cell A1 and
when I click button, macro should look for this file in all the
Subfolders and if file is found then macro should open it otherwise
MsgBox should pop up saying that "File is not found". Hope I was able
to explain my question. Can please any friend help me?

Hi K,

Try this simple solution.

Private Sub CommandButton1_Click()

cFolder="C:\Documents\Data\Subfoldder\"

If dir(cFolder & Worksheets("Sheet1).Range("A1"))="" then
a=msgbox("File Not Found",vbExclamation,"Missing File")
exit sub
Else
Workbooks.Open Filename:= cFolder & Worksheets("Sheet1).Range("A1"))
Endif



End Sub
"K" wrote:

Hi all, In Folder "C:\Documents\Data" I have about 10 Subfolders in
each Subfolders I have about 20 excel files. All excel files names
are something like (see below)

523699.xlsx
568944 - pg 1.xlsx
568944 - pg 2.xlsx
578944 - 578945.xlsx
588771.xlsx
etc€¦€¦

I want macro assigned to a button and I want that when I click on the
button macro should look the file name in cell A1 and then look that
file in all the Subfolders and then when file is found macro should
open that file else pop up MsgBox saying "File is not found". For
example if I put file name (like given above) 523699 in cell A1 and
when I click button, macro should look for this file in all the
Subfolders and if file is found then macro should open it otherwise
MsgBox should pop up saying that "File is not found". Hope I was able
to explain my question. Can please any friend help me?

On Mar 16, 9:42*am, Francis Ang
wrote:
Hi K,

Try this simple solution.

Private Sub CommandButton1_Click()

*cFolder="C:\Documents\Data\Subfoldder\"

*If dir(cFolder & Worksheets("Sheet1).Range("A1"))="" then
* * a=msgbox("File Not Found",vbExclamation,"Missing File")
* * exit sub
*Else
* * Workbooks.Open Filename:= cFolder & Worksheets("Sheet1).Range("A1"))
*Endif

End Sub



"K" wrote:
Hi all, In Folder "C:\Documents\Data" I have about 10 Subfolders in
each Subfolders I have about 20 excel files. *All excel files names
are something like (see below)

523699.xlsx
568944 - pg 1.xlsx
568944 - pg 2.xlsx
578944 - 578945.xlsx
588771.xlsx
etc……

I want macro assigned to a button and I want that when I click on the
button macro should look the file name in cell A1 and then look that
file in all the Subfolders and then when file is found macro should
open that file else pop up MsgBox saying "File is not found". *For
example if I put file name (like given above) 523699 in cell A1 and
when I click button, macro should look for this file in all the
Subfolders and if file is found then macro should open it otherwise
MsgBox should pop up saying that "File is not found". Hope I was able
to explain my question. *Can please any friend help me?- Hide quoted text -

- Show quoted text -

Hi Francis, Thanks for replying. I think you misunderstood my
question. I dont have Folder called "Subfolder". I actually have 10
Subfolders with name "Record 1 , Record 2 etc... in Folder "Data" and
file can be in any Subfolder.

Application.FileSearch will do this for you. Lifted almost exactly
from the help sample:

Option Explicit

Private Sub CommandButton1_Click()
Dim fs As FileSearch
Dim i As Integer

Set fs = Application.FileSearch
With fs
.LookIn = "C:\Wherever"
.SearchSubFolders = True
.Filename = Cells(1, 1)
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub

Opening the file I leave to you, as you didn't say what kind of file
it might be. If it's another Excel workbook ,then
Workbooks.Open .FoundFiles(i)
would do the trick. Otherwise, you could use the ShellExecute API call
to launch the appropriate application for the selected file.

The user specified that the file extensions were .xlsx. That means that there's
a very good chance that the OP is using xl2007.

And filesearch was removed from xl2007.



Spiggy Topes wrote:

Application.FileSearch will do this for you. Lifted almost exactly
from the help sample:

Option Explicit

Private Sub CommandButton1_Click()
Dim fs As FileSearch
Dim i As Integer

Set fs = Application.FileSearch
With fs
.LookIn = "C:\Wherever"
.SearchSubFolders = True
.Filename = Cells(1, 1)
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub

Opening the file I leave to you, as you didn't say what kind of file
it might be. If it's another Excel workbook ,then
Workbooks.Open .FoundFiles(i)
would do the trick. Otherwise, you could use the ShellExecute API call
to launch the appropriate application for the selected file.

--

Dave Peterson