Cell that displays .xls etc, depending on file name

[djmarak]

Hi,

I would like to write some kind of code that causes a cell to display the file extension of a particular document, knowing the file name and the folder. Preferably the code would run on its own, without the need for an update button. So far I dont have any idea how to do this.

Thanks

[Leith Ross]

Hello Djmarak,

You can use this macro (UDF) on the Worksheet. The macro doesn't check if the file path or file name exist, but you can easily modify it if you need to do this.

Function FileExt(FileName As String) As String

 Application.Volatile
 
 Dim FSO As Object
    
      Set FSO = CreateObject("Scripting.FileSystemObject")
      FileExt = FSO.GetExtensionName(FileName)
    
End Function

Place the file name in "A1", and place this formula "B1"
=FileExt(A1)

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Right Click on any Sheet's tab
4. Left Click on View Code in the pop up menu
5. Press the keys ALT+I to activate the Insert menu
6. Press M to insert a Standard Module
7. Paste the code by pressing the keys CTRL+V
8. Make any custom changes you need to the macro
9. Save the Macro by pressing the keys CTRL+S

Sincerely,
Leith Ross

[djmarak]

I appologize Im probably just too new with VBA, but I cant make the function work. Do I need to type in the full file path? It seems that excel simply doesnt recognize the function. Was there something I needed to do with the code other than save it?

Thanks

[Leith Ross]

Hello Djmarak,

I have attached a workbook with the macro installed, and an example on Sheet1 of how it is used.

Sincerely,
Leith Ross

[djmarak]

Hi, thanks for all your help. Now that I see your example I realize thats not exactly what I meant. I was looking for a function that would detect the file type of a document by actualy searching for the document. I dont have the file paths, just the document name and folder.

Sorry about the confusion. Is there a way to get a file path for a document knowing only the name and folder?

Thanks