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

[brah]

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.

[Mallycat]

You need to use VBA to be able to return file names into cells in Excel. The VBA function is called DIR.

http://en.allexperts.com/q/Excel-105...reate-List.htm

[brah]

You need to use VBA to be able to return file names into cells in Excel. The VBA function is called DIR.

http://en.allexperts.com/q/Excel-105...reate-List.htm

Hi Mally, would this work for PDF files too? as most of the files I'm working with are in PDF format.

[Firefly2012]

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

Please Login or Register  to view this content.

[brah]

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

Please Login or Register  to view this content.

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.

[Firefly2012]

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:

Please Login or Register  to view this content.

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.

[brah]

Okay thanks Firefly, I'll work on it.