Inserting pictures into Excel Sheet at a particular size

[ToferKing]

We have about 50 pictures we need to print for an in-house brochure.

We have decided to import each picture into an Excel sheet.

We decided to do that because we want to type a caption underneath the
picture.

We want the picture to print landscape on an 8 1/2 X 11 sheet of paper. We
have the margins set to .5" on each side.

We want each picture we import to be the exact same size. Basically, the
picture will fill the grid area from A1 to L29.

When we import a picture, it uses a grid size of A1 to Z62. That means we
have to resize each of these 50 pictures. We don't want to have to do that.

Can you tell Excel when it imports a picture, that picture needs to fall
within a particular grid range or be a particular size?

Thanks for all your help as always.

 

[Dave Peterson]

You could resize the picture manually...

Or

You could create a worksheet that has the location of the picture
(drive/folder/name) and the caption you want to use.

Then you could have a macro that places each picture in A1:L29, places the
caption in A30 (or whatever you want), prints the picture, and repeats the
process for the next in the list.

If you want to try:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myListRng As Range
Dim myCell As Range
Dim myPic As Picture
Dim PicSheet As Worksheet
Dim LocOfPic As Range
Dim LocOfCaption As Range

Set wks = Worksheets("Sheet1") 'contains the list
Set PicSheet = Worksheets("Picture") 'margins, etc already setup

Set LocOfPic = PicSheet.Range("a1:l29")
Set LocOfCaption = PicSheet.Range("x30")

With wks
'headers in row 1
Set myListRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myListRng.Cells
Set myPic = Nothing
On Error Resume Next
Set myPic = PicSheet.Pictures.Insert(Filename:=myCell.Value)
On Error GoTo 0

If myPic Is Nothing Then
myCell.Offset(0, 2).Value = "Error finding picture!"
Else
myCell.Offset(0, 2).Value = "ok"
With myPic
.Top = LocOfPic.Top
.Left = LocOfPic.Left
.Width = LocOfPic.Width
.Height = LocOfPic.Height
End With

LocOfCaption.Value = myCell.Offset(0, 1).Value

PicSheet.PrintOut preview:=True 'save some trees!
End If
Next myCell

End Sub

One thing...

When I finished this, I thought that this may not be what you need. If the
pictures have different aspect ratios, then changing the .top, .left, .width,
..height is gonna make a (at least) a few of them look kind of strange.

Are you sure you want them all that same width (or height)???

Maybe just setting the .top and .left and maybe the .width or the .height (but
not both) would be better?????

 

[Dave Peterson]

I meant to use A30, not X30 for the location of the caption--And I meant to
include a note for you to change that address to what you want to use, too.

 

[ToferKing]

Thank you for your time and effort for the above routine.

I will try it out soon.

As for the same size question. I hadn't thought of that, but all of these
pictures are landscape and after importing 2 of them, I just assumed the rest
would be alright being the same size as the first 2.

Toferking

I meant to use A30, not X30 for the location of the caption--And I meant to
include a note for you to change that address to what you want to use, too.

 

[Dave Peterson]

The size may fit the paper ok, but it may appear that the pictures are squashed
(horizontally or vertically).

Thank you for your time and effort for the above routine.

I will try it out soon.

As for the same size question. I hadn't thought of that, but all of these
pictures are landscape and after importing 2 of them, I just assumed the rest
would be alright being the same size as the first 2.

Toferking

 

[Bill]

We have about 50 pictures we need to print for an in-house brochure.

We have decided to import each picture into an Excel sheet.

We decided to do that because we want to type a caption underneath the
picture.

We want the picture to print landscape on an 8 1/2 X 11 sheet of paper. We
have the margins set to .5" on each side.

We want each picture we import to be the exact same size. Basically, the
picture will fill the grid area from A1 to L29.

When we import a picture, it uses a grid size of A1 to Z62. That means we
have to resize each of these 50 pictures. We don't want to have to do that.

Can you tell Excel when it imports a picture, that picture needs to fall
within a particular grid range or be a particular size?

Thanks for all your help as always.

Wouldn't it be easier to print the 50 pictures using a graphics program?
MS Publisher comes to mind, but there are lots of others.

Bill