VBA – Excel custom functions – Document Properties

Posted on September 27, 2014 by

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:

Path with the file name
Path without the file name
Name
Type
Size of the file
Date created
Date last modified
Date last accessed
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 <span class="hiddenSpellError">DocumentProperty</span>(Property As Integer)

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

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

😛

VBA – Excel custom functions – Document Properties

Related posts: