Prev: 4 field lookup
Next: how do you add times together to give a total time?
From: ToferKing on 24 Feb 2010 18:42
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.
From: Dave Peterson on 24 Feb 2010 18:59
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?????

ToferKing wrote:
>
> 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
From: Dave Peterson on 24 Feb 2010 19:03
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 wrote:
>
> 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?????
>
> ToferKing wrote:
> >
> > 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

--

Dave Peterson
From: ToferKing on 25 Feb 2010 10:16
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

"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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?????
> >
> > ToferKing wrote:
> > >
> > > 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
>
> --
>
> Dave Peterson
> .
>
From: Dave Peterson on 25 Feb 2010 10:44
The size may fit the paper ok, but it may appear that the pictures are squashed
(horizontally or vertically).



ToferKing wrote:
>
> 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
>
> "Dave Peterson" wrote:
>
> > 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 wrote:
> > >
> > > 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?????
> > >
> > > ToferKing wrote:
> > > >
> > > > 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
> >
> > --
> >
> > Dave Peterson
> > .
> >

--

Dave Peterson
 |  Next  |  Last
Pages: 1 2
Prev: 4 field lookup
Next: how do you add times together to give a total time?