How can I extract a fill pattern from a cell comments field

[mzgaljic]

I have a file where comments are used for some cells. The user does not insert any text into these comments fields, but uses the comments feature to display pictures.
A picture is used for the fill pattern of the cell (inserted through fill effects) and that picture is what is displayed when someone moves the mouse over the cell.
The "fill pattern picture" becomes part of the excel file and my question is how can I extract this picture and use it somewhere else.
I can send you the file if needed.
Thanks.

 

[Legacy 98055]

Just opened Outlook and saw your mail. I noticed that the results are better if you remove the border line and shadow. Replace the procedure above with this one that removes and resets several properties...

Sub SaveCommentPicturesToFile(CommentsInThisRange As Range)
    Dim r As Range, c As Comment, LineState As MsoTriState, ShadowState As MsoTriState
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
    MkDir ThisWorkbook.Path & Application.PathSeparator & "CommentPics"
    On Error GoTo 0
    
    ChDir ThisWorkbook.Path & Application.PathSeparator & "CommentPics"
    
    For Each r In CommentsInThisRange
        Set c = r.Comment
        If Not c Is Nothing Then
            c.Visible = True
            LineState = c.Shape.Line.Visible
            ShadowState = c.Shape.Shadow.Visible
            c.Shape.Line.Visible = msoFalse
            c.Shape.Shadow.Visible = msoFalse
            c.Shape.CopyPicture xlScreen, xlBitmap
            c.Shape.Line.Visible = LineState
            c.Shape.Shadow.Visible = ShadowState
            c.Visible = False
            SavePicture PicFromClip, "CPic_" & r.Address(0, 0) & ".bmp."
        End If
    Next
    
End Sub

 

[erik.van.geit]

Tom!
Thanks for joining this one, I was curious how to do it.
Great!!

I have two questions.
1.
Why is there a dot at the end?
SavePicture PicFromClip, "CPic_" & r.Address(0, 0) & ".bmp."
Seems like it makes no difference if it is there or not.
2.
Replacing "bmp" by "jpg" still works, but there was no difference in filesize, though I expected to find a difference. Any comments?

kind regards,
Erik

 

[Legacy 98055]

Hi Eric. The period is a typo. It is a bitmap. The only choices available are meta and bitmap. If you change the extension, whatever program opens it would likely resolve it as an image, but simply changing the extension does not saveas a jpg.

 

[erik.van.geit]

Thank you for your response, Tom

The typo didn't make any trouble.
It is still not clear to me: when I change the extension I get a jpg file, at least it looks like one.
The icon is jpg, the extension is jpg.
Is that misleading me?

best regards,
Erik

 

[Legacy 98055]

If you change it to bin, you will get a binary file. If you change it to txt, you will get a text file. You're thinking too hard!

 

[erik.van.geit]

I might be thinking to hard, but now the confusion is complete.

If you change the extension, whatever program opens it would likely resolve it as an image, but simply changing the extension does not saveas a jpg.<!-- / message --><!-- sig -->

If you change it to bin, you will get a binary file. If you change it to txt, you will get a text file.

those two quotes seems contradictory to me

perhaps the misunderstanding was here

Replacing "bmp" by "jpg" still works, but there was no difference in filesize, though I expected to find a difference. Any comments?

I meant replacing the "bmp" WITHIN THE CODE.

Still patient with me ?

 

[Legacy 98055]

Replacing "bmp" by "jpg" still works, but there was no difference in filesize, though I expected to find a difference. Any comments?

If you are expecting a format transformation, It ain't gonna happen by simply changing the extension. Rather in code or in explorer...

 

[erik.van.geit]

OK, thanks!!