Document properties allow us to get information about a document. They are either built into the document, or are custom, user defined properties. The default properties are called BuiltinDocumentProperties.

Here is a list of complete built-in properties are:

  • Title
  • Subject
  • Author
  • Keywords
  • Comments
  • Template
  • Last author
  • Revision number
  • Application name
  • Last print date
  • Creation date
  • Last save time
  • Total editing time
  • Number of pages
  • Number of words
  • Number of characters
  • Security
  • Category
  • Format
  • Manager
  • Company
  • Number of bytes
  • Number of lines
  • Number of paragraphs
  • Number of slides
  • Number of notes
  • Number of hidden Slides
  • Number of multimedia clips
  • Hyperlink base
  • Number of characters (with spaces)
  • Content type
  • Content status
  • Language
  • Document version

The document properties are shared by all Office applications. However not all the above built-in properties are supported by all Office programs. For example the Number Of Slides property applies only to MS PowerPoint and doesn’t apply to MS Excel.

The properties which I frequently use with Excel are:

  • Title
  • Subject
  • Keywords (Tags)
  • Comments
  • Revision number

So how do we get the list of all document properties using VB.Net and how do we set their values?

Get the list of all document properties

After you have set the relevant references to excel, use the code given below. The code will type the name of all the properties in a sheet in the Excel File. I would recommend adding a temporary sheet in the file and use that. For the code below, I am assuming that the name of the temporary sheet is Temp.

Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim DocProps As Object, DProps As Object

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")
        xlSheet = xlWorkBook.Sheets("Temp")

        DocProps = xlWorkBook.BuiltinDocumentProperties

        '~~> Display Excel
        xlApp.Visible = False

        '~~> Loop via all properties
        If Not (DocProps Is Nothing) Then
            Dim i As Integer
            For i = 1 To DocProps.Count
                DProps = DocProps(i)
                xlSheet.Range("A" & i.ToString()).Value2 = DProps.Name
            Next i
        End If

        '~~> Save and Close the File
        xlWorkBook.Close(True)

        '~~> Quit the Excel Application
        xlApp.Quit()

        '~~> Clean Up
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
    End Sub

    '~~> Release the objects
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

When you run the code you will get this kind of output:

Setting the values of Properties

And here is an example where we will try and update few of the values.

Public Class Form1
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim DocProps As Object, DProps As Object

    '~~> Setting the Built in Document Properties
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Dim prop As Object

        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        DocProps = xlWorkBook.BuiltinDocumentProperties

        DocProps("Title").Value = "Summary Report"
        DocProps("Subject").Value = "Sales"
        DocProps("Keywords").Value = "Sales; Report; Summary"

        '~~> Close the File
        xlWorkBook.Close(True)

        '~~> Quit the Excel Application
        xlApp.Quit()

        '~~> Clean Up
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
    End Sub

    '~~> Release the objects
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

And here is a snapshot of the properties: