VB.Net – Interacting with Comments in Excel Cells


EDIT

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

http://www.siddharthrout.com/2012/04/28/vb-net-interacting-with-comments-in-excel-cells/

 

In this post we will cover the following

  • Inserting Comment
  • Editing Comment
  • Deleting Comment
  • Show/Hide Comment
  • Formatting Comment
  • Inserting Images in Comment
  • Inserting Charts in Comment

I am assuming that you have your application setup and ready. If not see here.


INSERTING COMMENT


Once your application is all setup, we will insert a CommandButton (Name it “Insert Comment”) and in the command button we will add a new workbook. You application might look like this.

image

The below code will insert “Hello! This is a Sample Comment” in Cell D10

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim xlRng As Excel.Range

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Set reference to Sheet1
        xlWorkSheet = xlWorkBook.Sheets(“Sheet1″)

        '~~> Set reference to the range that we would be working with
        xlRng = xlWorkSheet.Range(“D10″)

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Here we set what needs to go into comments
        Dim msgComment As String = "Hello! This is a Sample Comment"

        xlRng.AddComment(msgComment)

        '~~> Save the file
        xlWorkBook.SaveAs(Filename:="C:\SampleNew.xlsx", FileFormat:=51)

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

        '~~> 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

SNAPSHOT

image


EDITING COMMENT


Now let’s try and edit the comment that we inserted earlier. The below code will open the file that we created above and then edit the comments in Cell D10. Insert another button on the form (Name it “Edit Comment”).
image
Place this code in the button click event.
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
    '~~> Open the existing Workbook
    xlWorkBook = xlApp.Workbooks.Open("C:\SampleNew.xlsx")

    '~~> Set reference to Sheet1
    xlWorkSheet = xlWorkBook.Sheets(“Sheet1″)

    '~~> Set reference to the range that we would be working with
    xlRng = xlWorkSheet.Range(“D10″)

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Here we set what needs to be added to the existing comment.
    Dim msgComment As String = vbNewLine & vbNewLine & "We have Just added new line to Existing Text"

    '~~> Add the new text to the existing comment
    xlRng.Comment.Text(Text:=xlRng.Comment.Text & msgComment)

    '~~> Save the File
    xlWorkBook.Save()

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

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

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

DELETING THE COMMENT


Insert a 3rd button in the application and name it say, “Delete Comment”

image

In the click event of the button paste this. This is perhaps the easiest of all the code we will be writing here. All we need to do is issue the delete command.

Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
    '~~> Open the existing Workbook
    xlWorkBook = xlApp.Workbooks.Open("C:\SampleNew.xlsx")

    '~~> Set reference to Sheet1
    xlWorkSheet = xlWorkBook.Sheets("Sheet1")

    '~~> Set reference to the range that we would be working with
    xlRng = xlWorkSheet.Range("D10")

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Add the new text to the existing comment
    xlRng.Comment.Delete()

    '~~> Save the File
    xlWorkBook.Save()

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

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

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

SHOW/HIDE THE COMMENT


Let’s re-insert the comment again so that we can continue with the rest of the things. Click the “Insert Comment” button again to re-insert the comment.

To show the comment you can use

xlRng.Comment.Visible = True

image

And to hide it

xlRng.Comment.Visible = False

image


FORMATTING THE COMMENT


Now we know how to Add/Modify/Delete/Show/Hide a Comment. Let’s see how can we format it. Below are 1 or 2 liners which will help you format the comments

Increasing/Decreasing the Height of the Comment

'~~> Set the relevant Height
xlRng.Comment.Shape.Height = 150

Increasing/Decreasing the Width of the Comment

'~~> Set the relevant Width
xlRng.Comment.Shape.Width = 150

Changing the background color of the Comment

Dim Shp As Excel.Shape = xlRng.Comment.Shape
'~~> This will color the comment background to red
Shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
Changing the font name of the Comment
Dim Shp As Excel.Shape = xlRng.Comment.Shape
'~~> This changes the font to "Time new Roman"
Shp.TextFrame.Characters.Font.Name = "Times New Roman"
Changing the font size of the Comment
Dim Shp As Excel.Shape = xlRng.Comment.Shape
'~~> This will change the font size of the comment to 12
Shp.TextFrame.Characters.Font.Size = 12
Changing the font color of the comment
Dim Shp As Excel.Shape = xlRng.Comment.Shape
'~~> This will change the font size of the comment to Red
Shp.TextFrame.Characters.Font.ColorIndex = 3
Changing the margins inside the comment
Margins are set or returned in points. Use the InchesToPoints method or the CentimetersToPoints method to convert measurements from inches or centimeters.
Dim Shp As Excel.Shape = xlRng.Comment.Shape
With Shp.TextFrame
    '~~> Set the Automargins to false to work with margins
    .AutoMargins = False
    '~~> Set the margins to 0.5 inch (36 points)
    .MarginBottom = xlApp.InchesToPoints(0.5)
    .MarginLeft = xlApp.InchesToPoints(0.5)
    .MarginRight = xlApp.InchesToPoints(0.5)
    .MarginTop = xlApp.InchesToPoints(0.5)
End With
Working with the Borders of the comment
Dim Shp As Excel.Shape = xlRng.Comment.Shape
With Shp.Line
    '~~> Set border color to red
    .ForeColor.RGB = RGB(255, 0, 0)

    '~~>Sets border weight to 8 points
    .Weight = 8

    '~~> Set Style to Dash Dot Dot (check intellisense for more styles
    .DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineDashDotDot
End With

INSERTING AN IMAGE IN THE COMMENT


In this example, I will insert an image from the user’s sample picture directory and the image that I will use is “Chrysanthemum.jpg”. If you want to autosize the comment to show the image in it’s actual size then see the next section “Inserting a chart in the comment

In a button click, insert this code

Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
     '~~> Add a New Workbook
     xlWorkBook = xlApp.Workbooks.Add

     '~~> Set reference to Sheet1
     xlWorkSheet = xlWorkBook.Sheets(“Sheet1″)

     '~~> Set reference to the range that we would be working with
     xlRng = xlWorkSheet.Range(“D10″)

     '~~> Display Excel
     xlApp.Visible = True

     '~~> Here we set what needs to go into comments
     Dim picComment As String = "C:\Users\Public\Pictures\Sample Pictures\Chrysanthemum.jpg"
     Dim msgComment As String = "Chrysanthemum:"

     '~~> Add the comment
     xlRng.AddComment(msgComment)

     Dim shp As Excel.Shape = xlRng.Comment.Shape

     '~~> Insert the picture
     shp.Fill.UserPicture(picComment)

     '~~> Save the file
     xlWorkBook.SaveAs(Filename:="C:\SampleNew.xlsx", FileFormat:=51)

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

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

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

image


INSERTING A CHART IN THE COMMENT


Let’s say we have a file which has a chart and it looks like this

image

And let’s say we want to show this chart in a comment in cell J10. Use this code in a button click.

Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
    '~~> Get user’s temp directory
    Dim TmpDir = Environment.GetEnvironmentVariable("TEMP")

    '~~> Open the existing Workbook
    xlWorkBook = xlApp.Workbooks.Open("C:\SampleNew.xlsx")

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Set reference to Sheet1
    xlWorkSheet = xlWorkBook.Sheets(“Sheet1″)

    '~~> Set reference to the range that we would be working with
    xlRng = xlWorkSheet.Range(“J10″)

    '~~> Set reference to the chart we will be working with
    Dim xlChartObj As Excel.ChartObject = xlWorkSheet.ChartObjects(1)
    Dim xlChart As Excel.Chart = xlChartObj.Chart

    '~~> Decide on the temp file name
    Dim strTempFileName As String = TmpDir & "\" & xlChartObj.Name

    '~~> Export the chart as jpg to the temp folder
    xlChart.Export(strTempFileName & ".JPG", "JPG", False)

    '~~> Here we set what needs to go into comments
    Dim msgComment As String = xlChartObj.Name & ":"

    '~~> Add the comment
    With xlRng
        .AddComment(msgComment)
        '~~> Uncomment the below part if you don’t want the comment to autosize as per chart size
        With .Comment.Shape
            .Height = xlChartObj.Height
            .Width = xlChartObj.Width
        End With
    End With

    Dim shp As Excel.Shape = xlRng.Comment.Shape
    '~~> Insert the picture
    shp.Fill.UserPicture(strTempFileName & ".JPG")

    '~~> Kill the temp file
    Kill(strTempFileName & ".JPG")

    '~~> Save the file
    xlWorkBook.SaveAs(Filename:="C:\SampleNew.xlsx", FileFormat:=51)

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

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

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

image


Hope this helps Smile

About these ads

4 responses to “VB.Net – Interacting with Comments in Excel Cells

  1. parcakontor October 6, 2012 at 2:06 pm

    i’ll add this code to my site very thanks :)

  2. length converter April 26, 2013 at 12:51 am

    I will right away grasp your rss feed as I can
    not find your email subscription link or newsletter service.
    Do you’ve any? Please let me understand so that I could subscribe. Thanks.

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

Follow

Get every new post delivered to your Inbox.

Join 31 other followers

%d bloggers like this: