Determine Image in Excel Comment

Question

I have found plenty of vba for inserting images into a comment

Selection.ShapeRange.Fill.UserPicture "C:\Temp\Pictures\ewe.jpg"

How can you determine the image already used for an comment?

I would like to extract the embedded image names if possible.

Is there not a property to access that will give me this? In the comment Fill Effects dialog box the image name somehow seems to be accessible.

Answer 1

Sorry, I didn't have the reputation to just comment on your question for clarification.

I made a test file, inserted a comment and image in that comment, and then extracted the base files. I then checked them all for the original file name. I also found the embedded JPEG and decoded it to get the metadata. As you've noted, the original file names are stored in xl\drawings\vmlDrawing1.vml (once you've extracted the xml files from the excel file by appending .zip to the filename and then running an unzip utility on it). I did find the file name, but not the path or file type, so I'm fairly certain that the path and file type aren't preserved.

If just the file name is sufficient for you, then that file contains information for each drawing that you have, and those will include the cell location, although they're 0 based, so you'd have to add one to get the actual row and column. My question is two part:

1) Is the file name alone sufficient, or did you need the entire path? If you needed the entire path, I think you're out of luck, since the paths are on a different computer and you can't even search for them if you do extract the file name.

2) If that is all you need, does the solution have to be VBA? In the past, I have programmatically unzipped and manipulated the xml base files, but it's a little tricky. It's simplified by the fact that you only have to read out the data, so that's a plus. I did it in .net before, but I'm sure that if it had to be VBA it could be done, but it would be simpler if you were open to the type of solution.

Let me know, I'd be happy to help you out.

====================================================================================

Try this: make a copy of the spreadsheet, append .zip (test.xlsm.zip), and then extract the files manually. Change vmlPath to the location of your xl\drawings\vmlDrawing1.vml file. Then run this. I did make some assumptions, for instance, I assumed that the order of the nodes and attributes would always be the same and so I used hardcoded indexes (shp.attributes(0), etc) instead of using logic to make sure I had the correct node or attribute, but you seem like you know your way around VBA, so I'm just going to code a barebones. This will need a reference to Microsoft XML 6.0.

Sub vmlParse()

    Dim vmlPath As String: vmlPath = "C:\Users\Lenovo\Desktop\test - Copy.xlsm\xl\drawings\vmlDrawing1.vml"
    Dim this As Worksheet: Set this = ActiveSheet
    Dim doc As New DOMDocument, shps As IXMLDOMNodeList
    Dim shp As IXMLDOMNode, n As IXMLDOMNode, a As IXMLDOMAttribute
    Dim fileName As String, productID As String
    Dim rng As Range, r As Long, c As Long

    doc.Load vmlPath

    Set shps = doc.getElementsByTagName("x:ClientData")
    For Each shp In shps

        If shp.Attributes(0).nodeValue = "Note" Then

            r = 0: c = 0

            For Each a In shp.ParentNode.FirstChild.Attributes
                If a.nodeName = "o:title" Then
                    fileName = a.nodeValue
                    Exit For
                    End If
                Next

            For Each n In shp.childNodes

                If n.nodeName = "x:Row" Then r = n.text
                If n.nodeName = "x:Column" Then c = n.text

                Next

            Set rng = this.Cells(r + 1, c + 1)
            productID = rng.Value

            'now you have the productID, the fileName, and the cell location

            End If
        Next

End Sub

Let me know how that worked out for you.