Finding a File on the C Drive

[John Baker]

Hi:

I have a slight problem. I need to be able to FIND a specific spreadsheet ("Accounts.xls")
BUT the end users have put it in various folders and it could be anywhere on the system.

Is there some way I can FIND the specific spreadsheet, set up the path as a variable and
then refer to it using that variable?

Details would be appreciated..

Thanks a lot

John Baker

 

[Lonnie M.]

Hi John,
Tom Ogilvy posted an answer to a similar question:

http://groups-beta.google.com/group..._frm/thread/d6f2fcc219e70930#1f841e64d2fa3e7e
I think you could probably tweak this for your needs.
HTH--Lonnie M.

 

[Guest]

If you want to open the file through the Open Dialog Box:
Sub ShowOpenDialogBox()
Const YourFile = "C:\My Data\Accounts.xls"
Application.Dialogs(xlDialogOpen).Show YourFile
End Sub

OR

If you want to open the file through the macro:
Sub OpenYourWorkBook()
Const ItsDir = "C:\"
Const ItsPath = "C:\My Data"
Const ItsName = "Accounts.xls"
ChDrive ItsDir
ChDir ItsPath
On Error Resume Next
Workbooks.Open ItsName
End Sub

Swisse

 

[Dave Peterson]

Sometimes, I have multiple files with the same name in different folders. You
may find one called accounts.xls on my pc. But it might not be the one you
really wanted.

If you're writing a macro for the user to select that workbook, you may want to
just toss up a dialog that asks them to select it.

Take a look at VBA's help for application.getopenfilename.

 

[Fredrik Wahlgren]

Hi:

I have a slight problem. I need to be able to FIND a specific spreadsheet ("Accounts.xls")
BUT the end users have put it in various folders and it could be anywhere on the system.

Is there some way I can FIND the specific spreadsheet, set up the path as a variable and
then refer to it using that variable?

Details would be appreciated..

Thanks a lot

John Baker

Waht if there are several files with this name? If the file can be anywhere,
you should put up a file open dialog so that the user can chose the right
one.

/Fredrik

 

[John Baker]

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

 

[Tom Ogilvy]

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.

 

[John Baker]

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]

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.aspx?scid=kb;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.aspx?scid=kb;en-us;189751
INFO: Limitations of the FileSystemObject

 

[Dave Peterson]

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.