Find File With Keyword In A Cell

  • Hi all

    I've had a look in the forums and can't seem to find this covered

    Here's what I'm trying to do,
    I'd like to have a cell where you'd put a word in and then click a button that would search for those files under that keyword, then maybe list the files
    as hyperlinks or as buttons that you can select from.
    It's for a recipe manager type, so if you'd put in pasta, it would search the
    recipes folder for any files with the name "pasta" then put them on the
    screen so you could click one of them to read it.

    Hope this is clear enough?

  • Re: Find File With Keyword In A Cell


    I think I have something that can help if you havent been able to create a macro yourself.
    This macro has a button that points to the macro called ListHypers, and you enter your searchcriteria in the cell b1. All the hits are listed on top of each other in the column E. You can of course change it if you want. Its just a quick and dirty thing I made in 5 minutes here.

    Hope it helps.

    Kind regards

  • Re: Find File With Keyword In A Cell

    Hi there Henrick!

    This is a great piece of code and is exactly what I'm after!
    Thank you for coming up with this!

    I gave it a test and works well although when I click on the
    hyperlink it doesn't open the page, maybe I've done something
    How would I go about changing where it starts to list the
    hyperlink? E column is perfect but I'm wondering if
    I can make them list from E13 onwards.

    Many thanks for your help!

  • Re: Find File With Keyword In A Cell

    Hi again!

    To change which row you want the hits to be starting on just change the startvalue of i from 1 to whatever you want (13 in your case).
    so instead of i = 1, just type i = 13.

    When it comes to the files not being displayed I didnt notive before, but it seems that the hyperlink doesnt include the last backslash used in the path-string somehow. I tried with just adding more of them, but to no avail, however it is quite easily solved. Try this code instead for making the actual hyperlink:

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Path & "\" & TmpFileName, _

    The only diffrence is that I added a fixed \ between the path and the filename. This should fix that problem.
    Oh, and I just assumed that you changed the path-string to your actual path on your hardddrive.

    Cant figure out why Excel wont include it in the string thou...thats really odd.

    Hope it works anyway.

    Kind regards

  • Re: Find File With Keyword In A Cell

    Hi Henrick

    That extra \ fixed the issue so thankyou!

    One other thing that seems to be an issue is that it isn't searching and
    displaying just what the keyword is for some reason.
    I put a few test books in there, ommlette, spanish ommlette, pasta - macaroni,
    pasta - lasagne, when I type pasta it'll display all recipes rather than just recipes with pasta in them, same happens if I type ommlette, and also, even if there isn't a keyword in the box, any idea why this might happen? I had a look at the code and can't for the life of me figure why.
    If need be I can send you the files for you to have a look at so you can
    better understand what it is I'm trying to do?

    Thanks again for all your help on this!! Believe me, it's very much appreciated!
    This is all for a restaurant My wife and I are opening.

  • Re: Find File With Keyword In A Cell

    Hello again!

    I tried to post earlier today but I only got "Page cannot be displayed", so sorry for the long reply, but its not my fault... :)

    I tried my own script a little bit more extensive and as far as I can see it works as it should, so I would think that the best option is if you can post your code you got from me here so I can see what has happend, and possibly come up with a solution.

    Kind regards

  • Re: Find File With Keyword In A Cell

    Hi Henrick!

    Thanks for the Reply

    I've uploaded the file just in case so you can see how it's laid out.

    Here's the code I have on the macro.

    Thanks for your continued help on this!!

  • Re: Find File With Keyword In A Cell

    Hello DauntlessUk!

    I just had a quick look at it, and as far as I can see you are using the wrong searchfield. In your version you changed to the cell 10,6 which would be the cell F10, but your searchcell in the document are placed at 10,4 which is D10. If you change the code to 10,4 instead of 10,6 it should work fine. At the moment the macro is reading the keyword from the wrong cell.
    Since the keyword is empty it will list all the files in that folder.

    You also have to have the last backslash in your path-string so it should look like: "C:\LEGENDS\Recipe Manager\Recipes\".

    I think that should do it. I will look a little bit closer later (I have to do my normal work too, the boss can be a bit strict on that point) and tell you if I find anything else.

    Kind regards

  • Re: Find File With Keyword In A Cell

    Hi Henrik - just a quick question - your "Substring" function... is there any difference between this and the INSTR function that comes with VBA (returns the starting position of a string within another string)?


    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


  • Re: Find File With Keyword In A Cell

    Well, actually...I didnt know about the Instr-function. Thats why I wrote my own.
    After having a look at it, my function and Instr is exactly the same, except that you have more options with Instr.

    Thats the way it goes when my boss wants something, but doesnt give me enough time to find the things I need... :)

    Thanks for the tip btw...

  • Re: Find File With Keyword In A Cell

    Hi Henrick!

    Thanks for the bump on that! I didn't see it!
    The code's working perfectly now!

    Thank you for all your help!! it's appreciated!
    I owe you a beer! lol


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!