Graph on new Sheet fills Sheet

[Pierre]

I print a graph on a new Sheet. The first instance the graph fills the full
surface available, and I can adjust the plot area within for the proper
aspect ratio.
On second instance the graph fills horizontally but leaves gray space above
and below. I can still adjust the aspect ratio, but since I start with a
graph that has less vertical size I do not get the max size graph.
How can I force the Chart Area to fill up the available space?
Thanks
Pierre

 

[Jon Peltier]

Pierre -

You might try fixing the chart this way. Go to Page Setup (File menu),
click on the Chart tab, and select the Use Full Page option. If that
doesn't help, go back to Page Setup, and on the Page tab, check the
paper size. If somehow an envelope has been selected, it would match
your description.

- Jon

 

[Pierre]

Jon,

Thank you, I have tried this but to no avail.

Some more input : I do not intend to print these charts but I do a print
screen and capture them as jpg for photo service, so the page set up I would
hope would not impact, I had not touched it, and strangely the preview
window seems to offer similar views.

Anyway, the chart that takes the full area has no elevator on the right. All
the others (13 charts in all) have one. All of these chart but one have the
elevator taking the full height of the available area, one has the elevator
taking practically all the available heigth but not quite (i.e. I can
uncover parts of the chart that are hidden below the top or lower frame, by
acting on the elevator)

Does that give any clue as to where the settings may be hidden?

Thanks,

Pierre

 

[Pierre]

Found the trick:
Goto Tools, Options, Chart, tick Chart sizes with window frame.
Pierre

 

[Jon Peltier]

Hi Pierre -

Glad you found it. I didn't suggest that one, because I find it gives an
appearance that is different from the eventual printout. But then you're
not printing.

If you know a little VBA, you can directly export a chart to a gif or
jpg file. Most charts will look better in gif, by the way, unless you're
using gradient fills or picture fills.

Here's a sample macro:

Sub ExportChart()
ActiveChart.Export ActiveWorkbook.Path & "\" & ActiveSheet.Name _
& ".gif", "GIF"
'' change "gif" to "jpg" or "png"
'' other formats may be available on your machine
End Sub

- Jon

 

[Pierre]

Jon,

Thank you for your macro I will try it.

Actually for this application I may print on a quality printer with photo
paper, but the main issue is this
When I do picture fill the picture adapts itself to the plot area. This
results in pixel interpolation since the source jpg has no reason to have
excatly the same dimensions than the plot area. As a result this loses
resolution.

What I would prefer is that Excel adapts its plot area to fit the picture. I
can crop the picture before so that it falls within acceptable limits for
Excel.
I believe however, that this is asking the application a lot. What I resort
to doing is copy the picture above the plot and touch up the excel plot
window so that the boundaries match as well as possible. However, no perfect
match leads to interpolation, difficult trick. Would be good if one could
force Excel to adopt a plot area of, say, 895*732, (the picture size). That
could perhaps the easy way out. Can this be done with VBA?

Regards,

Pierre

 

[Jon Peltier]

Pierre -

You need to make the plot's inside area 895x732, give or take a factor
of 0.75, to convert pixels (the picture measurement) to points (the
chart measurement). You directly control the plot's "outside" area when
you set the plotarea.width and .height, but this includes the variable
width margins used for axis labels. What you have to do in VBA is freeze
the font size and label orientation, measure the plot area inside and
outside dimensions, and adjust the outside dimensions by enough to get
the desired inside dimensions. Sometimes this takes a couple iterations,
and due to rounding you might only get as close as one point on the
inside area dimensions. But a mismatch of one pixel on the width of the
picture probably will not matter.

- Jon