Monthly Archives: April 2012

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

Scrolling Excel chart in Powerpoint


EDIT

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

http://www.siddharthrout.com/2012/04/21/scrolling-excel-chart-in-powerpoint/

 

While answering a question in stackoverflow.com, I came across an interesting question. How do we create a scrollable Excel chart in PowerPoint. In Excel it is very easy to make a scrollable chart using ScrollBar – Form Control. But how do we do it in PowerPoint?

I will cover this in 3 parts

1) Creating the Excel File

2) Setting up your presentation

3) The code


CREATING THE EXCEL FILE


Open a new Excel File and feed in some sample data as shown in the screen shot. I have filled 200 rows with sample data. Once the Data ready, create a line chart. Your Excel File should look like this.

image

Save the file to say, C: or any other location of your choice.


SETTING UP YOUR PRESENTATION


Open MS PowerPoint and go to slide 1. Click on the tab INSERT | OBJECT

image

You will be presented with Insert Object Dialog Box. Select the “Create From File” Option and then click on the “Browse” button. Select the Excel file that we had created earlier and select an icon by clicking “Display as Icon” and click on OK.

image

Your presentation will now look like this

image

Next navigate to the DEVELOPER Tab. (See Snapshot) In the developer tab, click on additional controls button and select “Microsoft Web Browser” and insert that control in your respective slide. Ensure that it hides the Excel Object that we inserted earlier. Size it accordingly. Also place a Command Button. Name it “Show Chart” or anything else what you feel is right.

image

And you are done with setting up your presentation


THE CODE


Press ALT + F11 or Click on tab DEVELOPER | VISUAL BASIC on the right hand side to open the visual basic editor.

image

Paste the code given below in the Slide1 code area.

image

Option Explicit

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

Dim ImageFile As String

Private Sub CommandButton1_Click()
    ExtractToTemp
    WebBrowser1.Navigate ImageFile
End Sub

Sub ExtractToTemp()
    Dim oSl As PowerPoint.Slide
    Dim oSh As PowerPoint.Shape

    Dim oXLApp As Object, oXLWB As Object, oXLSht As Object
    Dim mychart As Object

    Set oSl = ActivePresentation.Slides(1)

    Set oSh = oSl.Shapes(1)

    With oSh.OLEFormat.Object.Sheets(1)
        .Shapes(1).Copy
    End With

    '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")

    If Err.Number <> 0 Then
        Set oXLApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0

    oXLApp.Visible = False

    '~~> Open the relevant file
    Set oXLWB = oXLApp.Workbooks.Add
    Set oXLSht = oXLWB.Worksheets(1)

    oXLSht.Paste

    '~~> Save Picture Object
    ImageFile = TempPath & "Tester.jpg"

    If Len(Dir(ImageFile)) > 0 Then Kill ImageFile

    Set mychart = oXLSht.ChartObjects(1).Chart
    mychart.Export FileName:=ImageFile, FilterName:="jpg"

    '~~> Wait till the file is saved
    Do
        If FileExists(ImageFile) = True Then Exit Do
        DoEvents
    Loop

    oXLWB.Close SaveChanges:=False
    oXLApp.Quit
    Set oXLWb = Nothing
    Set oXLApp = Nothing 
End Sub

'~~> Get User's TempPath
Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
End Function

'~~> Function tot check if file exists
Public Function FileExists(strFullPath As String) As Boolean
    On Error GoTo Whoa
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileExists = True
Whoa:
    On Error GoTo 0
End Function

And you are done. Smile

Run the presentation by pressing F5 and click on the “Show Chart Button”. You screen show now look like this.

image

If you have made this presentation then this presentation can also be distributed easily.

Hope this helps…

Add-in Express


EDIT

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

http://www.siddharthrout.com/2012/04/18/add-in-express/

 

Not sure if you have heard about it. But maybe you might have. Smile

I first came across “Add-In Express” couple of months ago while answering questions in the MSDN forum and I was always wondering what was it all about. Do I really need it considering the fact that I had all the tools readily available in Visual Studio. So finally one day, I downloaded a copy to see myself what was so unique about it?

Much to my dislike, I realized my mug of coffee had gone cold by the time I moved away from my laptop (And I love my coffee!!!) However at the same time I was pleasantly surprised with the ease I could now build my Add-ins using Add-In Express.

I went back to the site to see if there was something that I had not yet discovered and found a blog post by Pieter van der Westhuizen. As I started reading it, I felt as if he was echoing my thoughts. Especially this part.

You might be thinking “Why do I need Add-in Express? I get the tools I need for Office development with Visual Studio.” Yes, you do get the tools needed for Microsoft Office development with Visual Studio, but comparing the two is like comparing driving a nail into a piece of wood with either a hammer or a wrench. With the wrench you’ll eventually get the nail into that piece of wood which will take a lot of time and maybe missing the nail and hitting your finger a few times but it’ll work. Whereas with the hammer, driving the nail into that piece of wood will be quick, although you might still miss and hit your finger.

How true! In today’s world, time is really important. As a freelance consultant, I have to work on multiple projects at any given time. And this tool actually made my life much simpler. If you are a serious developer of VSTO add-ins (Be it Excel, Word, Outlook or any other MS-Office App) then I would recommend you to have a look at http://www.add-in-express.com/creating-addins-blog/

I would also like to thank Andrei Smolin (You can also see him selflessly assisting people in the msdn forum) for promptly answering questions that I had about the product. Talk about support! Thanks Andrei Smile

Check if an Excel File has a Macro


EDIT

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

http://www.siddharthrout.com/2012/04/12/check-if-an-excel-file-has-a-macro/

 

Today while answering a question in MSDN Excel Forum, I came across an interesting question. Can we check if the Excel File has a “Macro”. When I say “Macro”, I am not referring to just any “Code”.

I started off with a small piece of code and as the discussion went on in the thread, I realized that I had not taken several scenarios into consideration. For ex. What if the user had “Require Variable Declaration” checked. My code was just counting the lines and then deciding whether there was a macro or not. I would like to thank Hans Vogelaar for suggesting different scenarios which made it possible to narrow down the code to the specifics.

So how do we check if the file has a “Macro”?

Logic: Strictly speaking Macros start with “Sub“ or “Private Sub” or “Public Sub”. So if we check for “Sub” then we can decide if the file has any macros or not.

Code:

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim HasMacro As Boolean
    Dim StrCode As String
    Dim i As Long

    '~~> Open the file to check if it has any MACRO

    Set wb = Workbooks.Open("C:\Users\Siddharth Rout\Desktop\book2.xlsm")

    Select Case UCase(Split(wb.Name, ".")(UBound(Split(wb.Name, "."))))
        '<~~ Excel files which can have a macro.
    Case "XLS", "XLSM", "XLTM", "XLT", "XLA", "XLSB", "XLAM"
        With wb.VBProject
            '~~> Components are like sheet1, thisworkbook, module etc.
            If .VBComponents.Count > 0 Then
                For i = 1 To .VBComponents.Count
                    '~~> get the entire code in the module
                    StrCode = .VBComponents.Item(i).CodeModule.Lines(1, _
                    .VBComponents.Item(i).CodeModule.CountOfLines)

                    StrCode = " " & Replace(StrCode, vbCrLf, " ") & " "

                    If InStr(StrCode, " Sub ") > 0 Then
                        HasMacro = True
                        Exit For
                    End If
                Next
            End If
        End With
    End Select

    wb.Close SaveChanges:=False

    If HasMacro Then
        MsgBox "The workbook has macro"
    Else
        MsgBox "The workbook doesn't have a macro"
    End If
End Sub

However an Userform might also have “Sub” so how do we ignore that?

All VBComponents have a type. To check that simply run this code in an Excel file which has Sheets, Thisworkbook, Module, Userform and a Class Module.

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim i As Long

    Set wb = ActiveWorkbook

    With wb.VBProject
        '~~> Components are like sheet1, thisworkbook, module etc.
        If .VBComponents.Count > 0 Then
            For i = 1 To .VBComponents.Count
                Debug.Print .VBComponents.Item(i).Name
                Debug.Print .VBComponents.Item(i).Type
            Next
        End If
    End With
End Sub
You will notice that these are the types
Name : ThisWorkbook Type : 100
Name : Sheet1       Type : 100
Name : Module1      Type : 1
Name : UserForm1    Type : 3
Name : Class1       Type : 2
So you can actually trap the “type” in the above code to ignore say Userforms
With wb.VBProject
    '~~> Components are like sheet1, thisworkbook, module etc.
    If .VBComponents.Count > 0 Then
        For i = 1 To .VBComponents.Count
            If .VBComponents.Item(i).Type = 3 Then
                '~~> Rest of your code
            End If
        Next
    End If
End With
You also use the Excel Constants instead of the above numbers for example, when you type
.VBComponents.Item(i).Type =
Intellisense automatically gives you the options. See picture.
image
Taking this post a step forward.
  • How to Check if an Excel File has any code

To check if there is any “Code” or not in an excel file you can use this code. This takes account of only “Option Explicit”. You can amend it to also take into considerations the following. One can also just check if the first word is “Option” to trap all the scenarios mentioned below.

Option Compare Binary
Option Compare Text
Option Private Module
Option Base 0
Option Base 1

Code:

Sub Sample()
    Dim wb As Workbook
    Dim Count_of_Lines As Long
    Dim StrCode As String

    Set wb = Workbooks.Open("C:\Users\Siddharth Rout\Desktop\book2.xlsm")

    Select Case UCase(Split(wb.Name, ".")(UBound(Split(wb.Name, "."))))

    '<~~ Excel files which can have a macro.
    Case "XLS", "XLSM", "XLTM", "XLT", "XLA", "XLSB", "XLAM"
       '~~> Taking this approach as there are very few extensions which support macros
    Case Else
        MsgBox "The workbook doesn't have any Code"
        wb.Close SaveChanges:=False
        Exit Sub
    End Select

    Count_of_Lines = 0

    With wb.VBProject
        '~~> Components are like sheet1, thisworkbook, module etc.
        If .VBComponents.Count > 0 Then
            For i = 1 To .VBComponents.Count
                '~~> Get the entire code in the module
                StrCode = Trim(.VBComponents.Item(i).CodeModule.Lines(1, _
                .VBComponents.Item(i).CodeModule.CountOfLines))
                If checkstatus(StrCode) = False Then
                    Count_of_Lines = Count_of_Lines + .VBComponents.Item(i).CodeModule.CountOfLines
                End If
             Next
         End If
    End With

    If Count_of_Lines > 0 Then
        MsgBox "The workbook has Code"
    Else
        MsgBox "The workbook doesn't have any Code"
    End If
End Sub

'~~> Checking if the code doesn't have just blank lines or "Option Explicit" only
Function checkstatus(strg As String) As Boolean
    Dim ar
    Dim strTemp As String

    strTemp = strg

    If InStr(1, strTemp, vbNewLine) Then
        Do While InStr(1, strTemp, vbNewLine) > 0
             strTemp = Replace(strTemp, vbNewLine, "")
        Loop
        strTemp = Trim(strTemp)
    Else
        strTemp = Trim(strg)
    End If

    If Trim(strTemp) = "Option Explicit" Or _
    Len(Trim(strTemp)) = 0 Or _
    Left(Trim(strTemp), 1) = "'" _
    Then checkstatus = True
 End Function
EDIT
This edit was required as Doug Glancy gave some nice suggestions.
To access the VBA components as shown in the code above, the user needs to have checked “Trust access to the VBA project object model” in Macro Security. To do this, follow these steps
Excel 2003: Click on menu Tools | Macro | Security to access the”Security” dialog Box. Under “Trusted Publisher” tab, check the box which says “Trust access to  the Visul Basic Project

Excel 2007 : Click on the “Office”  icon| Excel Options. You will get a dialog box as shown in the snapshot below. Click on “Trust Center Settings“. Under the “Macro Settings“, check the box which says “Trust access to  the VBA project object model
Excel 2010 : Click on the “File” Tab | Options. You will get a dialog box as shown in the snapshot below. Click on “Trust Center Settings“. Under the “Macro Settings“, check the box which says “Trust access to  the VBA project object model
HTH Smile