Easy one: getting the name of a picture

[Paul]

Hi,

I need to insert a picture into excel and then extract the
numeric value from it's default name. For example, if you
create a new workbook and insert a picture, the picture
name will be "Picture 1". I need to extract the "1" from
that name, so that I can assign it to the the starting
value of a loop counter (don't ask...)

TIA
Paul

 

[Greg Koppel]

Not sure what you really want, but here is some code to play with that lists
all the picture names and gives the number of the last picture.

Sub test()
List = ""
Num = Sheets(1).Shapes.Count
For i = 1 To Num
List = List & Sheets(1).Shapes(i).Name & Chr(13)
Next i
MsgBox List
MsgBox Right(Sheets(1).Shapes(Num).Name,
Len(Sheets(1).Shapes(Num).Name) - 7)
End Sub

HTH, Greg

 

[JE McGimpsey]

One way:

This returns the number of the last pic (or -1 if none) inserted:

Public Function LastPicNum() As Long
Dim sName As String
With ActiveSheet.Pictures
If .Count > 0 Then
sName = .Item(.Count).Name
LastPicNum = CLng(Mid(sName, InStr(sName, " ") + 1))
Else
LastPicNum = -1
End If
End With
End Function