Putting a formula to a picture

[plusunim]

Hi All.

Excel 2007 / WinXP

I wish to apply a formula to an inserted picture. The formula would be a named range, say .formula = "=NamedR". This should be done all through VBA. A large number of workbooks need to be processed.

To insert the picture I used the usual .Picture.Insert(...) function. I then Set a shape picture object to the newly inserted picture and select it to be able to assign the .Formula method. I was not successful as I was getting an error. The error being that it cannot apply the method to the picture class.

I opened a new workbook and tried it out. I had a named picture.

The above is just one of the many ways I tried to apply the .Formula method. I even tried to copy the picture. Not a successful move either. I have searched extensively over the internet. Exceptionally, only in Excel 2007 the picture cannot be named! And embedding might be giving me problems with these figures when I convert the worksheet to a PDF. I might find a way to use a later version of Excel, but I would like to find a solution with the tools I currently enjoy without continuously depending external sources.

Thanks in advance for the help.

 

[pgc01]

Hi

This is a simple example. Please try:

Sub Test()
Dim pic As Picture

Set pic = ActiveSheet.Pictures.Insert("C:\tmp\im.jpg")
pic.Formula = "=Sheet1!$A$1:$C$4"
End Sub

 

[plusunim]

Thanks for your reply.
I tried your solution. I am still getting a 'unable to set the Formula property of the picture class' error. It should work as it showed in the list of properties, meaning I'm not trying to use something unknown to Excel 2007.

For completeness, this is the sketchcode being executed before arriving to the picture insert, and a little bit beyond:

get files with Dir()
open a template workbook acting as a source
[Loop]
open a workbook
create a named range with RefersTo
Remove all pictures in Sheet1 with .Delete
copy a sheet from the template and paste to sheet1
in sheet2 remove the pictureLookup ie .Delete
set insert the dummy image for the picturelookup (as suggested)
[With dummyImage]
name, Left, Top
Formula [<- execution stops here!]
[End With]
close & save modified workbook
some counter just to know things add up (not strictly necessary but it is my practice)
[End loop] - Repeat

Hope it helps. I visually verified the modifications. The code executes well except for that line with Formula, which is really needed.

Any ideas why it cannot be set?

Thanks for your help.

 

[pgc01]

Hi

Did you try my code first, as posted, and it did not work?

If you did not, then try first the code as I posted (in a new workbook, just amend the pathname of the file) and post back.

 

[plusunim]

I tried your code as you asked me. I am still getting the same error 1004 "Unable to set the Formula property of the Picture class".

Thanks.

 

[plusunim]

Hi All.

Any ideas anyone with regards to this problem?

Thanks.

 

[plusunim]

Hi all.

I tried the piece of code that pgc01 asked me to try on different suite installations. The code did not work with Excel 2007. So I guess it is the inability of the Excel2007 that makes up all the trouble.

Can we say this mystery is 'solved' ?

Regards.

 

[pgc01]

I'm glad you found what's happening. I can't test with excel 2007.

Just out of curiosity, did you try adding the picture through the shapes collection?

It should be the same, but if you have a minute try:

Sub Test()
Dim shp As Shape

Set shp = ActiveSheet.Shapes.AddPicture("C:\tmp\im.jpg", msoFalse, msoTrue, 100, 100, 100, 100)
shp.OLEFormat.Object.Formula = "=Sheet1!$A$1:$C$4"

End Sub

 

[plusunim]

Hi pgc01.

It had crossed my path when searching, and for sure I had tried something out. Recently I was also pointing towards activeX. Moreover, I wish not to use jpg as the embedded compression format makes Acrobat11 go crazy. That's why all this hassle!

Tried it out...same result.

Thanks.

 

[pgc01]

Thanks for trying. I did not have much hope but it's always good to try.