Excel 2007 AddPicture help needed

[es330td]

I have an application that generates reports displayed in Excel using
VBA code. Our company just upgraded to Office 2007 and MS has changed
the behavior of the AddPicture method so I am trying to convert my
2003 reports over. It used to be that I could just add a picture to a
worksheet and manipulate it after the fact. Now the Shapes.AddPicture
method requires a height and width value; unfortunately, I don't know
the height or width of my picture going in as the pictures are
supplied by the user and can vary in size and h/w ratio. I've been
banging on this trying to figure out how to add the pictures without
specifying a size or barring that, "ask" the picture how big it is but
can't figure that one out either. If someone could point out the
right way to do this I'd appreciate it.

 

[Ron Coderre]

I don't have XL2007, but this is how I do it in XL2003:

With rDestCell
'Create the new picture
'(arbitrarily sized as a square that is the height of the rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue
End with

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)