VBA – Excel custom functions – Document Properties

If you program with Excel and VBA, probably you have plenty of various functions, showing you how to access some of the file properties. Mainly file path and file name. With the current VBA module, you may see how to access 9 different properties through a custom Excel formula.

The properties are the following:

1. Path with the file name
2. Path without the file name
3. Name
4. Type
5. Size of the file
6. Date created
7. Date last modified
8. Date last accessed
9. The name of the user, as registered in  the MS Office

In order to obtain any of these, you should simply write in any Excel cell “=DocumentProperty(n)”. In the place of “n” you should put one of the numbers, from 1 to 9.

Here comes the code! 🙂

Function DocumentProperty(Property As Integer)

    Dim oMyObject               As Object
    Dim sValue                  As String
    
    Set oMyObject = CreateObject("Scripting.FileSystemObject")
    With oMyObject.GetFile(ActiveWorkbook.FullName)
        Select Case Property
          Case Is = 1: sValue = .Path
          Case Is = 2: sValue = Mid(.Path, 1, _
               Len(.Path) - Len(.Name))
          Case Is = 3: sValue = .Name
          Case Is = 4: sValue = .Type
          Case Is = 5: sValue = .Size
          Case Is = 6: sValue = .DateCreated
          Case Is = 7: sValue = .DateLastModified
          Case Is = 8: sValue = .DateLastAccessed
          Case Is = 9: sValue = Application.UserName
          
          Case Else
            sValue = "Only values between 1 and 9 are to be included! :)"
        End Select
    End With
    
    DocumentProperty = sValue
    
End Function

😛