Finding a File on the C Drive

Sub SearchForAccount()
Dim i As Long
Dim sStr As String
Dim MyVar As String
Dim FileList() As String
ReDim FileList(0 To 0)

sStr = "C:\"
With Application.FileSearch
.NewSearch
.LookIn = sStr
.SearchSubFolders = True
.FileName = "account.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
If i = 1 Then
MyVar = .FoundFiles(i)
Else
ReDim FileList(1 To .FoundFiles.Count)
For i = 1 To .FoundFiles.Count
FileList(i) = .FoundFiles(i)
Next i
End If
Else
MsgBox "There were no files found."
End If
End With

If LBound(FileList) = 1 Then
For i = LBound(FileList) To UBound(FileList)
ActiveSheet.Cells(i, 1).Value = FileList(i)
Next
Else
Activesheets.Cells(1, 1).Value = MyVar
End If

End Sub


The results will contain the fully qualified filename (path included).

note that this can pick up myaccount.xls as well, so once the list is
returned, you need to examine each file name and make sure it is the file
you are interested in - but it looks like you will need to do that anyway to
account for multiple files.

--
Regards,
Tom Ogilvy




"John Baker" <Baker.JH@Verizon.net> wrote in message
news:37d2u0hpk02kf1valitg96l3l60ki1f74t@4ax.com...
> Lonnie:
>
> Thanks very much. I know that I can tweak it so that when more than one
file is found I
> tell the user to select manually, but I am not quite certain how to grab
the path for a
> file when the file count is 1. I would appreciate a suggestion as to the
command. ONce I
> grab it I can embed it in the originating spreadsheet or set it up as a
variable, and the
> world is my oyster!
>
> Thanks again
>
> John Baker
>
> "Lonnie M." <lonnie_r_m@hotmail.com> wrote:
>
> >Hi John,
> >Tom Ogilvy posted an answer to a similar question:
> >
>
>http://groups-beta.google.com/group/...ogramming/brow
se_frm/thread/d6f2fcc219e70930#1f841e64d2fa3e7e
> >I think you could probably tweak this for your needs.
> >HTH--Lonnie M.
>

Tom:

Thanks, that great.

The only real questions I have now are:

a. It appears to return multiple entries for a given folder and file, when there is only
ONE representation of the file in the folder in fact. Is there some way to return just ONE
entry for each file encountered?

b. It returns anything with the word "account" in the file name "MyAccount.xls" etc. Is
there some way I can restrict it to the exact file name?

Thanks a lot

John
"Tom Ogilvy" <twogilvy@msn.com> wrote:

>Sub SearchForAccount()
>Dim i As Long
>Dim sStr As String
>Dim MyVar As String
>Dim FileList() As String
>ReDim FileList(0 To 0)
>
>sStr = "C:\"
>With Application.FileSearch
> .NewSearch
> .LookIn = sStr
> .SearchSubFolders = True
> .FileName = "account.xls"
> .FileType = msoFileTypeExcelWorkbooks
> If .Execute() > 0 Then
> If i = 1 Then
> MyVar = .FoundFiles(i)
> Else
> ReDim FileList(1 To .FoundFiles.Count)
> For i = 1 To .FoundFiles.Count
> FileList(i) = .FoundFiles(i)
> Next i
> End If
> Else
> MsgBox "There were no files found."
> End If
>End With
>
>If LBound(FileList) = 1 Then
> For i = LBound(FileList) To UBound(FileList)
> ActiveSheet.Cells(i, 1).Value = FileList(i)
> Next
>Else
> Activesheets.Cells(1, 1).Value = MyVar
>End If
>
>End Sub
>
>
>The results will contain the fully qualified filename (path included).
>
>note that this can pick up myaccount.xls as well, so once the list is
>returned, you need to examine each file name and make sure it is the file
>you are interested in - but it looks like you will need to do that anyway to
>account for multiple files.

a) I have never run into that. Others have reported that this can miss
files, but I have never heard of it returning duplicate entries.

b) No, you can't restrict it to exactly account.xls.

for either of these problems you can loop through the list and eliminate
those that are not appropriate.


Here are some other methods. Note the third method is a link to another
article.

http://support.microsoft.com/default...b;en-us;185476
How To Search Directories to Find or List Files

this is a link to that article for the third method
http://support.microsoft.com/kb/185601/EN-US/
HOW TO: Recursively Search Directories by Using FileSystemObject

One for Information.
http://support.microsoft.com/default...b;en-us;189751
INFO: Limitations of the FileSystemObject

--
Regards,
Tom Ogilvy


"John Baker" <Baker.JH@Verizon.net> wrote in message
news:rim3u01a8m16v62glb28o1jeoevc0ujrjh@4ax.com...
> Tom:
>
> Thanks, that great.
>
> The only real questions I have now are:
>
> a. It appears to return multiple entries for a given folder and file, when
there is only
> ONE representation of the file in the folder in fact. Is there some way to
return just ONE
> entry for each file encountered?
>
> b. It returns anything with the word "account" in the file name
"MyAccount.xls" etc. Is
> there some way I can restrict it to the exact file name?
>
> Thanks a lot
>
> John
> "Tom Ogilvy" <twogilvy@msn.com> wrote:
>
> >Sub SearchForAccount()
> >Dim i As Long
> >Dim sStr As String
> >Dim MyVar As String
> >Dim FileList() As String
> >ReDim FileList(0 To 0)
> >
> >sStr = "C:\"
> >With Application.FileSearch
> > .NewSearch
> > .LookIn = sStr
> > .SearchSubFolders = True
> > .FileName = "account.xls"
> > .FileType = msoFileTypeExcelWorkbooks
> > If .Execute() > 0 Then
> > If i = 1 Then
> > MyVar = .FoundFiles(i)
> > Else
> > ReDim FileList(1 To .FoundFiles.Count)
> > For i = 1 To .FoundFiles.Count
> > FileList(i) = .FoundFiles(i)
> > Next i
> > End If
> > Else
> > MsgBox "There were no files found."
> > End If
> >End With
> >
> >If LBound(FileList) = 1 Then
> > For i = LBound(FileList) To UBound(FileList)
> > ActiveSheet.Cells(i, 1).Value = FileList(i)
> > Next
> >Else
> > Activesheets.Cells(1, 1).Value = MyVar
> >End If
> >
> >End Sub
> >
> >
> >The results will contain the fully qualified filename (path included).
> >
> >note that this can pick up myaccount.xls as well, so once the list is
> >returned, you need to examine each file name and make sure it is the file
> >you are interested in - but it looks like you will need to do that anyway
to
> >account for multiple files.
>

I think you and Tom miscommunicated.

You meant that myAccount.xls and Account.xls appear in the same folder (and the
list). You only want Account.xls (and avoid any filenames like myAccount.xls).

If that's true:

Option Explicit
Sub SearchForAccount()
Dim i As Long
Dim rCtr As Long
Dim sStr As String

sStr = "c:\"
rCtr = 0

With Application.FileSearch
.NewSearch
.LookIn = sStr
.SearchSubFolders = True
.Filename = "account.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
ReDim FileList(1 To .FoundFiles.Count)
For i = 1 To .FoundFiles.Count
If LCase(.FoundFiles(i)) Like LCase("*\" & .Filename) Then
rCtr = rCtr + 1
ActiveSheet.Cells(rCtr, 1).Value = .FoundFiles(i)
End If
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub

The "like" stuff is looking for anything that ends with "\account.xls".
And that backslash is important.

John Baker wrote:
>
> Tom:
>
> Thanks, that great.
>
> The only real questions I have now are:
>
> a. It appears to return multiple entries for a given folder and file, when there is only
> ONE representation of the file in the folder in fact. Is there some way to return just ONE
> entry for each file encountered?
>
> b. It returns anything with the word "account" in the file name "MyAccount.xls" etc. Is
> there some way I can restrict it to the exact file name?
>
> Thanks a lot
>
> John
> "Tom Ogilvy" <twogilvy@msn.com> wrote:
>
> >Sub SearchForAccount()
> >Dim i As Long
> >Dim sStr As String
> >Dim MyVar As String
> >Dim FileList() As String
> >ReDim FileList(0 To 0)
> >
> >sStr = "C:\"
> >With Application.FileSearch
> > .NewSearch
> > .LookIn = sStr
> > .SearchSubFolders = True
> > .FileName = "account.xls"
> > .FileType = msoFileTypeExcelWorkbooks
> > If .Execute() > 0 Then
> > If i = 1 Then
> > MyVar = .FoundFiles(i)
> > Else
> > ReDim FileList(1 To .FoundFiles.Count)
> > For i = 1 To .FoundFiles.Count
> > FileList(i) = .FoundFiles(i)
> > Next i
> > End If
> > Else
> > MsgBox "There were no files found."
> > End If
> >End With
> >
> >If LBound(FileList) = 1 Then
> > For i = LBound(FileList) To UBound(FileList)
> > ActiveSheet.Cells(i, 1).Value = FileList(i)
> > Next
> >Else
> > Activesheets.Cells(1, 1).Value = MyVar
> >End If
> >
> >End Sub
> >
> >
> >The results will contain the fully qualified filename (path included).
> >
> >note that this can pick up myaccount.xls as well, so once the list is
> >returned, you need to examine each file name and make sure it is the file
> >you are interested in - but it looks like you will need to do that anyway to
> >account for multiple files.

--

Dave Peterson