views:

106

answers:

1

When a picture is inserted into an Excel worksheet using Shapes.AddPicture(...) method, Excel gives it a name "Picture 1", "Picture 2" etc automatically.

This name can be used to get a reference to this shape object in Shapes collection like Shapes.Item("Picture 1"). If the name is changed in Excel using the Name Box, there are two different names (or one of them is a key/Caption) through which the Shape object can be referenced. So if I change the name to "MyPic" I can use any of these to reference a shape in Shapes collection:

Shapes.Item("Picture 1")
OR
Shapes.Item("MyPic")

The name can be accessed using Shape.Name property in VBA but how can we access the other value (MyPic) that does not seem to change internally?

UPDATED
What I am trying to do is to link a cell to a picture in Excel. I keep the picture data in cell's comment. These are the scenarios:

  1. If I keep the picture name (external name) then copy-pasting the structure on the same worksheet will duplicate the name and the cell will point to the same structure.
  2. If I keep the internal name then copy-pasting to other worksheet will create problem as the same internal name can exist on some other worksheet in the same workbook.
  3. If I take ID, I will not be able to get the Picture reference from it

For me getting the internal name is important. I have the Shape reference but no idea how to get the internal name from this ref.

+3  A: 

Immediately after you have added the shape to a worksheet (oSht) you can use Osht.Shapes(Osht.Shapes.count) to reference it.
so oSht.Shapes(osht.shapes.count).Name will give you its name
If you want to find the index of a shape in the Shapes collection and you know its name then you need to loop through Shapes.name until you find it.
If you know the Index then you can construct the "Picture n" alternate name.

Or you can store the "Picture n" alternate name.

Or you can store the ID property of the shape and then reference the shape by looping through the Shapes collections until you find the Shape.ID
If the user moves the shape to a different sheet and then renames it there is no way of identifying it as the original shape, because the external name, alternate name, Shapes index and ID will all be different. So if this is a problem in your scenario you would need to consider shadow copying or sheet protection.

Charles Williams
+1: "If you know the Index then you can construct the "Picture n" alternate name" - this might not always be true because if a picture at index 2 is deleted from the Shapes collection, we might have "Picture 3" at index 2. I do not think the internal name will be changed to match the index.
A9S6
If I add a Shape in VBA, the return value from Shapes.AddPicture(...) will give me the reference to it so there is no need to do a Shapes(Count) here. Also, for a newly created object both the internal and display names will be the same, so shape.Name will work at that time. The problem is how to get that internal name using the Shape reference at a later time (user might have changed the display name)
A9S6
If you have a valid reference to a shape object then Shape.name will give you its external name.
Charles Williams
If you have a valid reference to a shape object then Shape.name will give you its current external name, even if the user has changed it. If you know the internal name (Picture n) then you can reference the object and its external name. If you don't have either a valid reference or its internal name and the use has deleted shapes, moved shapes and changed the names of shapes there is no way of finding which shape is the original one you want.
Charles Williams
or you can use the Shape.ID property
Charles Williams
The Shape.ID property will not get me the Shape ref from the collection. I have updated the question to include the original problem that I am trying to solve.
A9S6