Help inserting an image

[Bailey]

I am stumbling here. I have column a and column b. I have a filename in
column A and I want to insert that name in column b. It would be nice to
cascade down the list and insert the image.

So the end result will be a bunch of images in column B. I am having a
brain toot because I am unsure how to even get started. It would be great if
I can also manipulate the images to move with cells but not size with cells.
Any idea's?

Col A B

img3.jpg
img1.jpg

 

[Tim Williams]

Try this.

Tim

'*******************************************
Sub Tester()
Dim c As Range, picCell As Range, picPath As String
Const PIC_FOLDER As String = "C:\Local Files\"

For Each c In ActiveSheet.Range("A2:A10").Cells

If Len(c.Value) > 0 Then
picPath = PIC_FOLDER & c.Value
If Dir(picPath) <> "" Then
c.Parent.Pictures.Insert(picPath).Select
Set picCell = c.Offset(0, 1)
Selection.Top = picCell.Top + 1
Selection.Left = picCell.Left + 1
'...not sure how you want to size the pics ?
picCell.RowHeight = Selection.Height + 3
Selection.Placement = xlMove
End If
End If

Next c

End Sub
'******************************************

 

[Bailey]

I tried this for some reason it skips the second "if" statement where its
checking the path = "" -- even when I do a watch on that variable it has
contents but still treats it like it doesn't have anything. Thanks for
looking, I know it's close.

 

[Bailey]

Also, when I comment out that second if statement then I get another error
that says unable to get the insert property of the picture class.

 

[Dave Peterson]

This line:

If Dir(picPath) <> "" Then

Actually checks to see if there is a file in that Pic_Folder string by the name
of the value in the cell.

So...

I'm guessing that either you changed the name of the folder to something not
correct (maybe forgot the final backslash in the path???) or the filename that
you think is there isn't named what you think it is (maybe a typo???).

 

[Bailey]

You nailed it, I forgot the slash in the directory identification. It's
working perfectly! -- I still need to change the properties of the pictures
as you mentioned in the original code example. Is there a way to manipulate
the move but don't size with cells properties? Do you know where I should
look to adjust each of these properties rather than depend on experts like
yourself. I am so happy, this works so well, thank you so much for your time
and efforts.

Jay

 

[Dave Peterson]

If you use a variable to represent the object and you dimension it as the
correct type, you can get the VBE to share its intellisense with you.

For example, I'd use:
Dim myPict as Picture

Then when I type:
mypict.
(that dot is the key)

the VBE will show all the properties and methods (liked adjectives and verbs)
that I can use next.

If that's not enough, I can put a watch on that myPict variable and when I step
through the code, I can look at the watch window to see what properties are
available.

Another option is to hit F2 in the VBE. This will show you the Object Browser.
Then rightclick on the righthand side and choose "show hidden members".

Some features in excel have been "superseded" by other objects (like pictures
and controls from the Forms toolbar). But they're still supported by excel and
VBA.

Then you can search for Picture and see all those hidden members. You'll see
that the properties have a different icon than the methods.

Anyway, this alternative code may get you closer:

Option Explicit
Sub Test2()
Dim myCell As Range
Dim picCell As Range
Dim picPath As String
Dim PIC_FOLDER As String
Dim myPict As Picture

PIC_FOLDER = "U:\My Pictures\2000_12_25"
If Right(PIC_FOLDER, 1) <> "\" Then
PIC_FOLDER = PIC_FOLDER & "\"
End If

For Each myCell In ActiveSheet.Range("A2:A10").Cells
If myCell.Value <> "" Then
picPath = PIC_FOLDER & myCell.Value
If Dir(picPath) <> "" Then
Set myPict = myCell.Parent.Pictures.Insert(picPath)
With myCell.Offset(0, 1)
myPict.Top = .Top + 1
myPict.Left = .Left + 1
If myPict.Height + 3 > 409 Then
.EntireRow.RowHeight = 409
Else
.EntireRow.RowHeight = myPict.Height + 3
End If
End With
myPict.Placement = xlMove
End If
End If
Next myCell

End Sub

I don't like to use a constant for the path -- for the problem you had. If I
make it a variable, I can check for that trailing backslash and add it if I
forgot to include it.

 

[Bailey]

Dave,
Thank you for looking at the code and providing options - I appreciate the
opportunity to learn. Both examples work very well but I am curious about
why in both examples you do .TOP +1 and a .LEFT+1 -- what is the 1 do and
also, what is the significance of evaluating the row height +3 > 409 -- where
purpose does 409 have?


Thanks for taking the time to educate on the VBE -- I experimented with a
few things and just discovered how much I really don't know - smile. But
thank you.

 

[Dave Peterson]

The original code wasn't mine. I would expect that the author wanted the
pictures just a little bit inside the cell.

But checking the rowheight before trying to change it was mine. Try this in a
new worksheet.

Select a cell.
Format|row|height
type 5555
and hit enter.

You'll see a warning message when you do it manually.

If you try it in code, then you won't get a warning message. The code will stop
with an error.