excel cell lookup file on harddrive

[dj_siek]

Hi everyone!

Dunno if this is a quick question or not!

In excel, i would like to have a cell look up to see if a file exists on a harddrive.

For instance.

the cell will either say OK or not done.

OK would be if it can find the file on the harddrive i.e an excel file containing serial numbers of a particular computer brand. So the different cell headings above would be IBM, ACER, etc. the cell below these would then search for a file containing acer or ibm and if it finds it it will then put either OK or Not Done if not found.

I would want it to search for the name of the file that is in another cell or has part of the filename with in it.

Any advise would be greatly appreciated.

Cheers

Joel

[flummi]

Here's a VBA procedure.

Try it and if okay modify it to meet your needs. Create a command
button on your worksheet, right click it in design mode, select 'view
code' and copy the code below between private sub and end sub.

The names of the manufacturers you oare looking for is supposed to be
in A1:?1 (I have provided for 50 cells). Empty name cells are ignored.
If a file is found, it says "OK" in A2 .... and it lists the files from
A4 down.

With Application.FileSearch
NumNames = 50
Path = "c:\" 'your path here
For t = 1 To NumNames
If Worksheets("Sheet1").Cells(1, t).Value <> "" Then
.NewSearch
.LookIn = Path
.SearchSubFolders = False 'if you want to search
subfolders, set to true
.Filename = Worksheets("Sheet1").Cells(1, t)
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
Worksheets("Sheet1").Cells(2, t).Value = "OK"
For i = 1 To .FoundFiles.Count
Worksheets("Sheet1").Cells(i + 3, t).Value =
..FoundFiles(i)
Next i
Else
Worksheets("Sheet1").Cells(2, t).Value = "Not found"
End If
End If
Next t
End With

Hope it helps.

Hans

[dj_siek]

you're a genius Hans.

Thanks Mate

[dj_siek]

So If i wanted to have Column A igored, and then rows 6 with the manufacturer in it. Then Row 10 to have OK, or Not Found come up. how would I do that?

I changed the row numbers from 1 and 2 to those, but it didn't work.

Also, how do you stop it from showing file location?

Thanks again,

Joel

[dj_siek]

its all good i worked it out with a bit of trial and error!

Cheers

Joel