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


"

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

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