none
Adding Images to Excel Comments Thru VB RRS feed

  • Question

  • I am having an issue adding an image into the comment of an Excel spread sheet. The code below attaches the image, but the width and height are not correct. For images that are narrower than they are tall, it tends to make the width almost 0 while applying a height value. For images that are wider than they are tall, the comment(image) is mostly correct but with an incorrect aspect ratio.

    Any thoughts on what I have missed?

    Sub AddPictureComment(ByVal strPartName As String, ByVal intExcelRow As Integer, ByRef xlsWorkSheet As Object)
     
         '~~> Set what needs to go into comments
         Dim strPartImageName As String = strFilePath & strPartName & ".jpg"
         Dim bmpImageForSize As New Bitmap(strPartImageName)
     
         '~~> Verify/Record the information
         lw.Writeline("Cell: A" & intExcelRow)
         lw.Writeline(" -Image  : " & strPartImageName)
         lw.WriteLine(" -Height : " & bmpImageForSize.height)
         lw.WriteLine(" -Width  : " & bmpImageForSize.width)
         lw.WriteLine("")
     
         '~~> Add the comment
         With xlsWorkSheet.Range("A" & intExcelRow).AddComment(strPartName)        
            .Shape.Height = bmpImageForSize.Height
            .Shape.Width = bmpImageForSize.Width 
            .Shape.Fill.UserPicture(strPartImageName)
         End With
     
    End Sub

    Friday, November 21, 2014 11:45 AM

All replies


  • Re:  Sizing images added to comments

    Images can look pretty ugly, I agree, when added to a comment.
    I found one has to determine the aspect ratio (AR) of the picture and size the comment to the same AR.

    If you have a fixed library of images, I would determine the AR of each image and save the picture name and AR in an array for use in your code.
    I use a fixed height for comments and adjust the width according to the AR in my commercial "Files or Folders" workbook (3 week trial).
    If the pictures come out of the wild, then on the fly, one can add the picture to worksheet, determine the AR, delete the picture and then create the comment.

    Something like this should work for you...
    With objComment.Shape
      .LockAspectRatio = False
      .Height = ConstantNumber
      .Width = ConstantNumber * AR
      .LockAspectRatio = True
    End With
    ObjComment.Shape.Fill ...
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, November 21, 2014 5:42 PM