Can I get the inserted picture's original filename.

[Bernie Deitrick]

John,

As long as you are using pictures from the same folder, you can do something like this: the first
one inserts the file, the second one tells you its name (though it must be selected)

Dim myName As String
Dim myPicName As String
Const myPath As String = "C:\Documents and Settings\DEITBE\My Documents\My Pictures\"

Sub InsertPicture()
myName = "0ead1e14.jpg"
ActiveSheet.Pictures.Insert(myPath & myName).Select
'get rid of spaces and periods, illegal characters in picture names
myPicName = Replace(Replace(myName, ".", "xxx"), " ", "qqq")
MsgBox myPicName
End Sub

Sub WhatFileNameIsPicture()
Selection.Name = myPicName
MsgBox myPath & Replace(Replace(Selection.Name, "qxq", "\"), "xxx", ".")
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

John,

Someday I'll figure out which version to post..... this version has your path, not mine ;-)

Dim myName As String
Dim myPicName As String
Const myPath As String = "C:\Documents and Settings\Administrator\My Documents\My Pictures\"

Sub InsertPicture()
myName = "0ead1e14.jpg"
ActiveSheet.Pictures.Insert(myPath & myName).Select
'get rid of spaces and periods, illegal characters in picture names
myPicName = Replace(Replace(myName, ".", "xxx"), " ", "qqq")
MsgBox myPicName
End Sub

Sub WhatFileNameIsPicture()
Selection.Name = myPicName
MsgBox myPath & Replace(Replace(Selection.Name, "qxq", "\"), "xxx", ".")
End Sub

HTH,
Bernie
MS Excel MVP

 

[Norman Jones]

Hi John,

See Bernie Dietrick's responses to your earlier formulation of this
question:

http://tinyurl.com/8hu95

 

[Norman Jones]

Hi John,

I see that the question has not in fact been posed again.

Because of an error in your clock setting, your question appears (for me) at
the top of other posts. It was this that caused me to assume, erroneously,
that you had reposted your question.

My apologies for this incorrect assumption.

 

[john]

I used the following code to insert a picture into a xls file.

ActiveSheet.Pictures.Insert("C:\Documents and Settings\Administrator\My
Documents\My Pictures\0ead1e14.jpg")

But I want to get this inserted picture's original filename days later. And
I want to export this inserted picture to a picture file by VBA code, not by
copying and pasting. Can I do it by VBA code. I use the Name property of the
inserted picture. But The property return "Picture 1" to me. How can I get
the original filename and export it by VBA code.


Best Regards
John Black

 

[Peter T]

All that (OP's clock) confused me which at the time prevented me from
suggesting perhaps make use of the AlternativeText property, eg

With .Pictures.Insert(sFullPicturePath)
..name = "MyPicture"
' didn't test but might need
'.Shaperange.name = "MyPicture"
..Shaperange.Alternativetext = sFullPicturePath
End with

Msgbox Activesheet.shapes("MyPicture").AlternativeText

Regards,
Peter T