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


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/01/18/vb-net-to-read-and-set-excels-inbuilt-document-properties/

 

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

  1. Title
  2. Subject
  3. Author
  4. Keywords
  5. Comments
  6. Template
  7. Last author
  8. Revision number
  9. Application name
  10. Last print date
  11. Creation date
  12. Last save time
  13. Total editing time
  14. Number of pages
  15. Number of words
  16. Number of characters
  17. Security
  18. Category
  19. Format
  20. Manager
  21. Company
  22. Number of bytes
  23. Number of lines
  24. Number of paragraphs
  25. Number of slides
  26. Number of notes
  27. Number of hidden Slides
  28. Number of multimedia clips
  29. Hyperlink base
  30. Number of characters (with spaces)
  31. Content type
  32. Content status
  33. Language
  34. 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 of an Excel Document say SAMPLE.XLSX

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

image

Setting the values of Properties

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

    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:\Users\Siddharth Rout\Desktop\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

image

Advertisements

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

  1. Anthony Michie March 4, 2012 at 11:53 am

    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.

  2. Dorothea Garrity March 4, 2012 at 11:54 am

    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 March 4, 2012 at 12:05 pm

      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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: