Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel updating from XML file - file path specific? | Excel Discussion (Misc queries) | |||
naming .xls file based on a cell in excel | Excel Discussion (Misc queries) | |||
can not open excel file, too many different cell formats | Excel Discussion (Misc queries) | |||
Using Jet to read excel file returns blank for last cell - sometim | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |