XL7 bug in inserting pictures

[John]

I have been using code written in XL2000 for 8-9 years. My recent upgrade to
XL7 gave me an unexpected problem. Excel 2007 does NOT support
"pictures.insert". Previous versions do not have this problem. I have read
dozens of workarounds with many claiming that "pictures.insert" does indeed
function properly in XL7.

Being stupid, I used MS resources to research the problem. Indeed, this
property was eliminated in XL7. Several of my reference books for XL7 also
claim that the property is identical to XL3. Not so.!!!!

I'm going to have to increase my meds.

Let me state the problem. I have many folders, each having 24 photos. My
code allowed me to select the folder and automatically place all 24
thumbnail photos (pictures) in predetermined positions of the worksheet. It
still works in my old antiquated 2000. Sheesh.

Any ideas.

Thanks

 

[JLGWhiz]

Check this out. It might help.

http://www.dummies.com/how-to/content/how-to-insert-pictures-from-graphics-files-in-exce.html

 

[John]

The manual method is not the problem. My problem is achieving the same goal
using VBA.

John


"

 

[JLGWhiz]

According to Ron deBruin, this snippet should work in xl07.

Dim myPict As Picture

With ActiveCell
Set myPict = .Parent.Pictures.Insert(filename)
myPict.Top = .Top
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
End With

 

[Dougaj4]

According to Ron deBruin, this snippet should work in xl07.

Dim myPict As Picture

With ActiveCell
Set myPict = .Parent.Pictures.Insert(filename)
myPict.Top = .Top
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
End With






- Show quoted text -

I have a routine that pastes picture files into a spreadsheet, which
works in 2007.

The bit that does the work is:

For i = Start To Last
Set Target = Range("a1").Offset(TopRow + PicRows * (j), 0)
PicFile = FileList(i, 1)
If PicFile <> "" Then
Set pic = ActiveSheet.Shapes.AddPicture(PicFile, False, True, 20,
Target.Top, PicWidth, PicHeight)
End If
j = j + 1
Next i


More details and download at:
http://newtonexcelbach.wordpress.com/2008/10/17/imagemagick-and-excel/
and
http://newtonexcelbach.wordpress.com/2008/10/19/imagemagick-update-and-add-irfanview-link/

 

[JLGWhiz]

I am still running xl03, so I cannot test anything for xl07. If Ron's code
did not work, I have no more suggestions.

According to Ron deBruin, this snippet should work in xl07.

Dim myPict As Picture

With ActiveCell
Set myPict = .Parent.Pictures.Insert(filename)
myPict.Top = .Top
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
End With






- Show quoted text -

I have a routine that pastes picture files into a spreadsheet, which
works in 2007.

The bit that does the work is:

For i = Start To Last
Set Target = Range("a1").Offset(TopRow + PicRows * (j), 0)
PicFile = FileList(i, 1)
If PicFile <> "" Then
Set pic = ActiveSheet.Shapes.AddPicture(PicFile, False, True, 20,
Target.Top, PicWidth, PicHeight)
End If
j = j + 1
Next i


More details and download at:
http://newtonexcelbach.wordpress.com/2008/10/17/imagemagick-and-excel/
and
http://newtonexcelbach.wordpress.com/2008/10/19/imagemagick-update-and-add-irfanview-link/

 

[John]

Did you notice that the code you posted uses"Pictures.Insert" ?? Will not
work. "Pictures.Insert" is the missing element in XL7

John

 

[Ron de Bruin]

Hi JLGWhiz and others

I test it again on 2007 with the latest updates and indeed it is not working
I am sure that it was working when Excel 2007 was released, maybe another SP2 bug ?

I try to test it on a machine with no SP this week and post back

 

[Ron de Bruin]

Mmmmm

Strange

After testing it in 2010 (working OK)
I test it again in 2007 and no problem

Or it was a typo in the path/file name or something else

 

[John]

I had used the same identical code in XL2000, XL2003, and it worked
perfectly. Tried to run the same code in 2007..........NO.

How is it possible to suddenly run in 2007 after executing the code in 2010.

Remember, MS claims that "pictures.insert" was indeed left out of 2007.

 

[Dave Peterson]

IIRC, the problem isn't with pictures.insert.

It's when you try to run recorded code. (I thought it was the .select in the
recorded code that caused problems, but that worked ok for me.) But there's
something about the way the recorded code is, er, recorded and played back.

I didn't bother to test to try to remember--and you didn't post your code that
failed. But this code worked fine for me in xl2007:

Option Explicit
Sub testme()

Dim myPict As Picture
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("a1:c3")
Set myPict = .Parent.Pictures.Insert("C:\car.jpg")
myPict.Top = .Top
myPict.Left = .Left
myPict.Width = .Width
myPict.Height = .Height
End With
End With

End Sub

Maybe you could modify your existing code so that it avoids the .select's (or
whatever the problem is!).

 

[John]

Hi Dave: Good info, but it even makes me even more confused. The following
code still runs on XL2000.....bu tNOT on my xl7.

For x = 3 To 13 Step 2
For y = 1 To 7 Step 2

ActiveSheet.Cells(x, y).Select
ActiveSheet.Parent.Pictures.Insert(.FoundFiles(i)).Select
Selection.ShapeRange.Height = ActiveCell.RowHeight
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = Right(.FoundFiles(i), 12)
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = FileDateTime(.FoundFiles(i))
If .FoundFiles(i) = "" Then Exit Sub
i = i + 1
Next
Next

End With
End Sub

Anyway, I took your snippet and created a new workbook. Damn, it works.
!!!!!!

The only significant change is "parent". When I remove "parent", the code
FAILS.

Thanks very much. This problem has been driving me bonkers, but it has
taught me a great deal about VBA. At age 74, I still enjoy challenges.

Again thanks for your help.

John

 

[Dave Peterson]

Your code doesn't run for me when I tested in xl2003.

This line:
ActiveSheet.Parent.Pictures.Insert(...

Doesn't look right. The activesheet.parent is the workbook that owns the
activesheet.

I don't think you posted the code that worked in xl2003.

And I didn't do any testing in xl2007, but .select and selection.shaperange
could be the problem. (I still don't recall the real problem in xl2007.)

 

[Ron de Bruin]

Hi John

2010 is on another machine

I will test more this evening and post back

 

[Ron de Bruin]

Ok test it again in 2007 and 2010 on both different machines
with only one Office version

Both working OK with a picture that is on my desktop

Sub TestMe()
Dim myPict As Picture

With ActiveCell
Set myPict = .Parent.Pictures.Insert("C:\Users\Ron Desktop\Desktop\koornstra.png")
myPict.Top = .Top
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
End With

End Sub

 

[John]

There has been some major changes with "pictures" between 2000 & 2007.
Any picture inserted with the "pictures.insert" method in XL2000 can be
cleared from the WS using edit>clear contents. Not so in 2007. Selecting the
work range and applying clear contents has no effect.
Since MS claims the command "pictures.insert" is "hidden" in 2007, we now
know it can be called by prefixing with the "parent" statement. At least on
my machine.

I should have stated that I am using Windows 7. Hmmmm

The partial code that I posted earlier still works on my version of 2000. I
did not provide all of the additional code since it has an API call.

John