Function to extract certain words in file name and have certain cells in Excel update them

Hello,

I need a function to extract certain words in a file name (located in a specific folder) and have certain cells in Excel automatically update themselves according to the files inside the folder.

For example, I have a folder called "Completed Projects".

In this folder I have one file called, "Revision Level (Nil)". In Excel, a cell in should be able to automatically enter the following data "Rev Level 0". Let's just say that this cell is located in Column B. When I add another file with the title "Revision One", the next row of Column B should reflect "Rev Level One" and so on.

Is this possible? If so, what's the name of the function? I tried googling but so far I've only come up with Filelen( ) for checking file size data and not much else.

Thanks in advance.



Brah.

It will work with files of any type e.g.:

Dim strFile As String

strFile = Dir("C:\SomeFolder\*.pdf")

'check if any pdfs found:
If Len(strFile)>0 Then
   'return to a cell:
   Cells(Rows.Count,"A").End(xlUp).Offset(1).Value = strFile
   'now set up a loop to get all pdfs:
   Do
      strFile = Dir    'note, no need to include anything else here - same criteria as before will be used
      If Len(strFile)>0 Then Cells(Rows.Count,"A").End(xlUp).Offset(1).Value = strFile
   Loop While Len(strFile) > 0
Else
   MsgBox "No pdf found!"
End If

Originally Posted by Firefly2012

It will work with files of any type e.g.:

Dim strFile As String

strFile = Dir("C:\SomeFolder\*.pdf")

'check if any pdfs found:
If Len(strFile)>0 Then
   'return to a cell:
   Cells(Rows.Count,"A").End(xlUp).Offset(1).Value = strFile
   'now set up a loop to get all pdfs:
   Do
      strFile = Dir    'note, no need to include anything else here - same criteria as before will be used
      If Len(strFile)>0 Then Cells(Rows.Count,"A").End(xlUp).Offset(1).Value = strFile
   Loop While Len(strFile) > 0
Else
   MsgBox "No pdf found!"
End If

Thanks firefly, I'm experimenting with it now.

Edit:

Works fine, but if I were to use this method it would involve a lot of renaming of files else the list would appear very messy. Is there another way of doing this? Instead of calling the file extension, the function will look for certain keywords in the file name?

Thanks.

The string you pass to Dir can contain wildcard characters (you've seen one * meaning one or more of any character). You can build up your string using wildcards, for example, assume you want to find a file with the word "restaurant" in he title then you can find such a file with:

Dim strFile As String

strFile = Dir("C:\SomeFolder\*restaurant*.pdf")

Which will return the first file that contains "restaurant" anywhere within the filename.

If you have a lot of potential keywords you want to find, it might be worth returning all the filenames from the folder (eg using Dir), store them maybe in an array and then iterate over the array searching for each keyword.