none
Save Image out of Comment Box RRS feed

  • Question

  • Hi All

    How can I save an Image out of a Comment Box to a .jpg or .bmp Filename equal to the value in the cell the Comment is attached, and concatenating the value from the cell to the right of that cell. ie Lastnames and Comments in column B and Firstnames in Column C.

     

    TIA

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3
    Wednesday, December 7, 2011 9:53 PM

Answers

  • Get Stephen Bullen's PastePicture.zip
    http://www.oaltd.co.uk/Excel/Default.htm

    Drag the module modPastePicture into your own project

    in another module

    Sub test()
    Dim bVis As Boolean
    Dim sFolder As String, sFile As String, sName As String
    Dim sFilter As String, lPicType As Long
    Dim oPic As IPictureDisp
    Dim cm As Comment
            sFolder = Application.DefaultFilePath & "\"
         sName = "CommentPicture.bmp"
         sFile = sFolder & sName
            Set cm = Range("B3").Comment
            bVis = cm.Visible
         cm.Visible = True
         cm.Shape.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
         cm.Visible = bVis
            On Error Resume Next
         Set oPic = PastePicture(xlBitmap)
         On Error GoTo 0
            If oPic Is Nothing Then
                 MsgBox "no image in clipboard"
         Else
                 On Error Resume Next
                 Kill sFile
                 On Error GoTo 0
                 SavePicture oPic, sFile
            End If
    End Sub

    Ensure there's a comment in B3 with a picture, adjust folder & file names to suit.

    As I mentioned, this will copy the commet as-is to file, not necessarily quite the same as the originally applied picture in terms of size, fileformat and any text in the comment (which could be temproarily removed before doing the copypicture).

    Peter Thornton

    • Marked as answer by CABGx3 Saturday, December 10, 2011 3:16 AM
    Thursday, December 8, 2011 10:11 PM
    Moderator
  • Save the file as HTLM.  The picture should be in the associated folder.
    gsnu201111
    • Marked as answer by CABGx3 Thursday, December 8, 2011 3:13 AM
    Wednesday, December 7, 2011 10:38 PM
    Moderator

All replies

  • Save the file as HTLM.  The picture should be in the associated folder.
    gsnu201111
    • Marked as answer by CABGx3 Thursday, December 8, 2011 3:13 AM
    Wednesday, December 7, 2011 10:38 PM
    Moderator
  • Hi Gary's Student.......

    Good to see you, it's been awhile.  Thanks much for the tip, it works great to get all the pictures out of the Excel file, but unfortunately it does not "name" them except for "Image 1" etc..........  It at least gets me part of the way home.

    Do you know of any way to do it with code that I could get the Filenames to use the Cell value(s)?

     

    BTW, I've been away for awhile.......I don't hardly recognize anyone here but you anymore........could you drop me an email about what has been going on with the groups?

    Vaya con Dios,

    Chuck, CABGx3

    croberts  "at"  tampabay  "dot"  rr  "dot"  com

     

     


    Chuck, CABGx3
    Thursday, December 8, 2011 3:13 AM
  • I don't think there's any straightforward way. In 2007+ you might delve into the zipped xml files, the images are stored as original in the "media" folder, there are xml's for each comment. I haven't looked myself but I assume there's a way of linking the image file to the cell, possibly via one or two other files with id's. In VBA files can be unzipped with Shell, but a fair bit of code required.

    A different approach which should work in all versions is to copy the comet as a picture, eg

    cm.Shape.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

    where cm refers to the comment. You'd need to (temporarily) make it visible. Any text would also be copied though that could be removed & replaced. Once it's in the clipboard, with APIs it can be saved to file. However it could only be saved as a bmp or metafile, and with the dimensions of the comment.

    The rest of the stuff about getting adjacent cell values is the simple part.

    I seem to recall that tag, and a long discussion concluding eventually that polynomials require a high degree of precision for use in forecasting some years ago, was that you?

    Peter Thornton

    Thursday, December 8, 2011 1:20 PM
    Moderator
  • Thanks for your response Peter, but you are talking "Way" over my head.  I've been banging at it a bit, using your suggested line of code but have not had much luck yet.

    Here's where I am so far, if you see anything that could help, it would be much apprecaited.  With this, the Comment Box turns on and off, but not much else happens that I can see.

    Sub SavePictureFromComment()

    Range("b12").Select
     If Selection.Value <> "" Then
        On Error Resume Next
        Selection.Comment.Visible = True
        Selection.Comment.Shape.Select True
        Selection.Shapes.Select
    With Selection.Shape
       .CopyPicture Appearance:=xlScreen, Format:=xlBitmap
       .SaveAs FileName:="C:/PicTry.bmp"
       .Range("a3").Paste

    End With

    ActiveCell.Comment.Visible = False
    End If

    End Sub

     

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3
    Thursday, December 8, 2011 5:56 PM
  • Get Stephen Bullen's PastePicture.zip
    http://www.oaltd.co.uk/Excel/Default.htm

    Drag the module modPastePicture into your own project

    in another module

    Sub test()
    Dim bVis As Boolean
    Dim sFolder As String, sFile As String, sName As String
    Dim sFilter As String, lPicType As Long
    Dim oPic As IPictureDisp
    Dim cm As Comment
            sFolder = Application.DefaultFilePath & "\"
         sName = "CommentPicture.bmp"
         sFile = sFolder & sName
            Set cm = Range("B3").Comment
            bVis = cm.Visible
         cm.Visible = True
         cm.Shape.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
         cm.Visible = bVis
            On Error Resume Next
         Set oPic = PastePicture(xlBitmap)
         On Error GoTo 0
            If oPic Is Nothing Then
                 MsgBox "no image in clipboard"
         Else
                 On Error Resume Next
                 Kill sFile
                 On Error GoTo 0
                 SavePicture oPic, sFile
            End If
    End Sub

    Ensure there's a comment in B3 with a picture, adjust folder & file names to suit.

    As I mentioned, this will copy the commet as-is to file, not necessarily quite the same as the originally applied picture in terms of size, fileformat and any text in the comment (which could be temproarily removed before doing the copypicture).

    Peter Thornton

    • Marked as answer by CABGx3 Saturday, December 10, 2011 3:16 AM
    Thursday, December 8, 2011 10:11 PM
    Moderator
  • Peter......you are ABSOLUTELY THE MAN!!!............
      
    Just a little bit of tweaking to fit my specific circumstances and your code worked PERFECTLY in both XL97 and XL2000,  It does everything, and exactly the way I wanted it to.
     
    I can't begin to thank you enough....I really appreciate your help.
     
    Vaya con Dios,]
    Chuck, CABGx3
     

    Chuck, CABGx3
    Saturday, December 10, 2011 3:16 AM