Inserting pictures then sorting

[Dav]

I have a sheet containing data in the first few columns then a column
containing references to pictures. Can these pictures be imported into
the cell next to the reference eg data is in columns A & B a reference
to a picture in columnC. If the pictures refered to in column C can be
imported into column D.

I can then sort by the data in columns A & B and print out the pictures
I am interested in.

Obviously this is a simplified version of what I require as there are
hundreds of lines of data but hopefully you will get the picture. I
could use file insert picture, but this would be highly time consuming
and hopefully the task can be automated with a macro, but I am fairly
new to writing macros




If anyone could help that would be great

Thanks

Dav


+-------------------------------------------------------------------+
|Filename: Example.txt |
|Download: http://www.excelforum.com/attachment.php?postid=3982 |
+-------------------------------------------------------------------+

 

[Dave Peterson]

Something like this might work for you:

Option Explicit
Sub testme01()

Dim testStr As String
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim myPict As Picture

Set wks = Worksheets("sheet1")

With wks
.Pictures.Delete 'nice for testing.
Set myRng = .Range("c2", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In myRng.Cells
testStr = ""
On Error Resume Next
testStr = Dir(myCell.Value)
On Error GoTo 0

If testStr = "" Then
myCell.Offset(0, 1).Value = "Picture not found"
Else
With myCell.Offset(0, 1)
.ClearContents
Set myPict = .Parent.Pictures.Insert(myCell.Value)
myPict.Top = .Top
myPict.Left = .Left
myPict.Width = .Width
myPict.Height = .Height
myPict.Placement = xlMoveAndSize
End With
End If
Next myCell
End Sub

 

[Dav]

That has saved a colleague a huge amount of work and will greatly add
value to our survey process as pictures of handwritten text can be
included with parts of the survey. Its made my day!

Dav