Find File With Keyword In A Cell

Re: Find File With Keyword In A Cell

Hello!

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.

Option Explicit


Sub ListHypers()


  Dim FileSys, Path, ActualFile, FileObject
  Dim TmpFileName As String
  Dim SearchString As String
  Dim i As Long
  Set FileSys = CreateObject("Scripting.FileSystemObject")
  Set Path = FileSys.GetFolder("C:\Recipies")
  Set FileObject = Path.Files
  Columns("E").Delete
  i = 1
  SearchString = Cells(1, 2).Value
  For Each ActualFile In FileObject
    TmpFileName = ActualFile.Name
    If SubString(TmpFileName, SearchString) > 0 Then
      Cells(i, 5).Select
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Path & TmpFileName, _
        TextToDisplay:=TmpFileName
      i = i + 1
    End If
  Next
  
End Sub


Function SubString(FullString As String, SearchForString As String) As Long
'Since VBA dont have this function I had to write it. This looks in a string (FullString) for a substring(SearchForString). If FullString contains the substring it will return the position that substring start on. If it doesnt find it, it returns 0. If SearchForString bigger than FullString it returns -1. It will only find the first occurence of the substring.
'Example: SubString("Hello World", "Hello") will return 1.
'         SubString("Hello World", "World") will return 7
'         SubString("Hello World", "o") will return 5 since the first o is located in position 5
'Note that this function is NOT case sensetive.
  Dim Pos As Long


  If Len(SearchForString) > Len(FullString) Then
    SubString = -1
    Exit Function
  End If
  
  For Pos = 1 To (Len(FullString) - Len(SearchForString)) + 1
    If LCase(Mid(FullString, Pos, Len(SearchForString))) = LCase(SearchForString) Then
      SubString = Pos
      Exit Function
    End If
  Next Pos
End Function

Hope it helps.

Kind regards
/Henrik

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, _
            TextToDisplay:=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
/Henrik

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...

Anyway...
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
/Henrik

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
/Henrik

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)?

Thanks,
Ger

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...