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

Check this out. It might help.

http://www.dummies.com/how-to/conten...s-in-exce.html



"John" wrote in message
...
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

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

John


"

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




"John" wrote in message
...
The manual method is not the problem. My problem is achieving the same
goal using VBA.

John


"

On Nov 9, 2:14*pm, "JLGWhiz" wrote:
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

"John" wrote in message

...



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

John

"- Hide quoted text -

- 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...ick-and-excel/
and
http://newtonexcelbach.wordpress.com...rfanview-link/

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


"Dougaj4" wrote in message
...
On Nov 9, 2:14 pm, "JLGWhiz" wrote:
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

"John" wrote in message

...



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

John

"- Hide quoted text -

- 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...ick-and-excel/
and
http://newtonexcelbach.wordpress.com...rfanview-link/

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

John


"JLGWhiz" wrote in message
...
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




"John" wrote in message
...
The manual method is not the problem. My problem is achieving the same
goal using VBA.

John


"

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


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


"Dougaj4" wrote in message
...
On Nov 9, 2:14 pm, "JLGWhiz" wrote:
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

"John" wrote in message

...



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

John

"- Hide quoted text -

- 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...ick-and-excel/
and
http://newtonexcelbach.wordpress.com...rfanview-link/

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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


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


"Dougaj4" wrote in message
...
On Nov 9, 2:14 pm, "JLGWhiz" wrote:
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

"John" wrote in message

...



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

John

"- Hide quoted text -

- 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...ick-and-excel/
and
http://newtonexcelbach.wordpress.com...rfanview-link/

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.

"Ron de Bruin" wrote in message
...
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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


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


"Dougaj4" wrote in message
...
On Nov 9, 2:14 pm, "JLGWhiz" wrote:
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

"John" wrote in message

...



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

John

"- Hide quoted text -

- 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...ick-and-excel/
and
http://newtonexcelbach.wordpress.com...rfanview-link/

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 wrote:

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

--

Dave Peterson

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

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.)

John wrote:

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

Hi John

2010 is on another machine

I will test more this evening and post back

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"John" wrote in message ...
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.

"Ron de Bruin" wrote in message
...
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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


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


"Dougaj4" wrote in message
...
On Nov 9, 2:14 pm, "JLGWhiz" wrote:
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

"John" wrote in message

...



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

John

"- Hide quoted text -

- 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...ick-and-excel/
and
http://newtonexcelbach.wordpress.com...rfanview-link/

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




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Hi John

2010 is on another machine

I will test more this evening and post back

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"John" wrote in message ...
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.

"Ron de Bruin" wrote in message
...
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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


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


"Dougaj4" wrote in message
...
On Nov 9, 2:14 pm, "JLGWhiz" wrote:
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

"John" wrote in message

...



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

John

"- Hide quoted text -

- 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...ick-and-excel/
and
http://newtonexcelbach.wordpress.com...rfanview-link/

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 editclear 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