My Blog

VB.Net to Read and Set Excel’s Inbuilt Document Properties

4

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 “25. Number Of Slides” property applies only to PowerPoint and doesn’t apply to 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("E:UsersSiddharth RoutDesktopSample.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:

  1. Anthony Michie
    Anthony Michie03-04-2012

    My sister bookmarked this web publication for me and I have been going through it for the past couple hrs. This is really going to assist me and my friends for our class project. By the way, I enjoy the way you write.

    • Siddharth Rout
      Siddharth Rout03-04-2012

      Thanks for your kind comments Anthony :) Comments like these motivate me to write more. Cheers.

  2. Dorothea Garrity
    Dorothea Garrity03-04-2012

    Thanks for the good writeupIt in fact was a entertainment account itLook complex to far introduced agreeable from you! However, how could we keep in touch?

    • Siddharth Rout
      Siddharth Rout03-04-2012

      Thank you :) You can keep in touch using any of these two ways :)

      1) You can subscribe to the blog by entering your email address.

      2) If you want to directly write to me then my email is in the ‘About’ Page.