Way to automate adding pictures in a series in Excel?
Sub AddPictures()
Dim pic As Picture
'ActiveSheet.Pictures.Delete
For Each cell In Range("C2:C4") ' < replace with the column letter where
the pictures will be inserted. Change 2 to the first row where a
picture
will be inserted, change 4 to the last row where a picture will be
inserted
cell.Select
Set pic = _
ActiveSheet.Pictures. _
Insert("C:\WINDOWS\" & cell.Row & ".jpg")
You want an argument like "c:\mypictures\2.jpg"
cell.row will supply the 2. Replace "C:\Windows\" with
the path to your pictures, put the "\" on the end. Change ".jpg" to
reflect the file extension of your pictures. (but you said the were jpg)
pic.Top = cell.Top
pic.Left = cell.Left
' pic.Width = cell.Width
' pic.Height = cell.Height
Next
End Sub
Lines preceded with a single quote are commented out - they won't be
executed. To make them executable, remove the singel quote
So assume the pictures (jpg's) go into column F starting with Row 2 to 30
and the pictures are in D:\My Pictures\My Folder"
Sub AddPictures()
Dim pic As Picture
'ActiveSheet.Pictures.Delete
For Each cell In Range("F2:F30")
cell.Select
Set pic = _
ActiveSheet.Pictures. _
Insert("D:\My Pictures\My Folder\" & cell.Row & ".jpg")
pic.Top = cell.Top
pic.Left = cell.Left
' pic.Width = cell.Width
' pic.Height = cell.Height
Next
End Sub
go into the VBE (alt+F11), choose Insert = Module. In the resulting
module, paste in the above code.
go back to Excel (alt+F11)
Go to Tools=Macro=Macros, select AddPictures and click the run button.
do this on a copy of your workbook.
--
Regards,
Tom Ogilvy
MS wrote in message
...
So, should the macro below work to insert the pictures automatically with
the correspondence between the filename and the row #, to know which
picture
goes in which cell in the column?
Where it says C2-C4, I replace the Letter with the column number in which
I
have the pictures, and replace the number range with the range of rows
that
will have pictures inserted? Where it says "C:\Windows" replace that with
the folder where those numbered pictures are located?
What about the text following that in the same line--& cell.Row & ".jpg."
Just like that? Or substitute cell.Row with the first row in which a
picture
should be inserted? (Isn't that information already there, in the range
"C2:C4"?) Do I preceded the ".jpg" with the number of the first photo to
be
inserted?
By "uncomment", do you mean take away the ' at the bottom of those two
lines?
Sorry for the "dummy" questions, but I am a novice at this.
So I can click on "Write Macro", and paste in that text, with the changes
mentioned above? Anything I missed that requires my input , before saving
the macro?
Thanks a lot for writing it. :-)
"Tom Ogilvy" wrote in message
...
Sub AddPictures()
Dim pic As Picture
'ActiveSheet.Pictures.Delete
For Each cell In Range("C2:C4")
cell.Select
Set pic = _
ActiveSheet.Pictures. _
Insert("C:\WINDOWS\" & cell.Row & ".jpg")
pic.Top = cell.Top
pic.Left = cell.Left
' pic.Width = cell.Width
' pic.Height = cell.Height
Next
End Sub
Change C2:C4 to represent the cells where you want the picture.
If you want the pictures to exactly cover the cell, then uncomment the
Width
and Height commands.
--
Regards,
To Ogilvy
MS wrote in message
...
I'm putting a column of pictures of people next to a column with their
names. If I have the pictures together in a folder, with the photo
file
names numbered with the corresponding row numbers that the pictures go
in,
is there a way to record a macro so that (for instance) 28.jpg will go
into
the picture column in row #28, then 29.jpg will go into the picture
column
in row #29, etc. In other words, insert a whole series of pictures
(filenames the same as their appropriate row numbers) in their correct
places, by initiating one macro?
If anyone has any idea of how that could be done, I would greatly
appreciate
any suggestions on how to do that.
|