Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
dj_siek
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
flummi
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
dj_siek
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
dj_siek
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
dj_siek
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel updating from XML file - file path specific? Sean Excel Discussion (Misc queries) 4 August 5th 05 12:56 PM
naming .xls file based on a cell in excel lsu-i-like Excel Discussion (Misc queries) 1 June 24th 05 09:44 PM
can not open excel file, too many different cell formats Abdur Excel Discussion (Misc queries) 1 March 17th 05 01:13 PM
Using Jet to read excel file returns blank for last cell - sometim Ron Excel Discussion (Misc queries) 1 December 9th 04 09:21 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 10:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"