excel cell lookup file on harddrive
 

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


--
dj_siek
------------------------------------------------------------------------
dj_siek's Profile: http://www.excelforum.com/member.php...o&userid=31247
View this thread: http://www.excelforum.com/showthread...hreadid=509219

excel cell lookup file on harddrive
 

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

excel cell lookup file on harddrive
 

you're a genius Hans.

Thanks Mate


--
dj_siek
------------------------------------------------------------------------
dj_siek's Profile: http://www.excelforum.com/member.php...o&userid=31247
View this thread: http://www.excelforum.com/showthread...hreadid=509219

excel cell lookup file on harddrive
 

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
------------------------------------------------------------------------
dj_siek's Profile: http://www.excelforum.com/member.php...o&userid=31247
View this thread: http://www.excelforum.com/showthread...hreadid=509219

excel cell lookup file on harddrive
 

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

Cheers

Joel


--
dj_siek
------------------------------------------------------------------------
dj_siek's Profile: http://www.excelforum.com/member.php...o&userid=31247
View this thread: http://www.excelforum.com/showthread...hreadid=509219