EXcel Chart Size Probelm with Printer - Please Help

[Dennis Macdonald]

I create via VBA a number of charts on an Excel sheet. I want to size
each of these to suit a single page of the printer. THe problem I have
is I cannot figure out the relationship between the Excel shape width
and the printer's printable width.

I have via a series of API got the printer's printable area and
converted to twips and points and as I understand Excel uses points as
its chart width and height.

I have setup a page in Excel manually with no margins and created a
chart and manually sized it to fit the page perfectly (and yes the
page setup is 100% scaling and zero on all margins). Below is the
numbers I get;

Directly from the API
--------------------------------
Printable Height (Pixels)= 14031
Page Width (Pixels)= 9920
Page Height (Pixels) = 14033
PrtTopNoPrint (Pixels) = 0
PrtLeftNoPrint (Pixels) = 0
Printer Resolution (DPI) = 1200 x 1200
TwipsPerPixelX = 1.2
TwipsPerPixelY = 1.2
PointsPerPixelX = 0.06
PointsPerPixelY = 0.06

Directly from the Shape Object
----------------------------------------------
Shape Width (Points) = 766.5
Shape Height (Points) = 610.5

Landscape Page Orientation gives:
---------------------------------------------------
Printable Width (Pixels) = 14031
Printable Width (Twips) = 16837.2
Printable Width (Points) = 841.86

WHY IS DIFFERENCE 766.5 and 841.86?

Please Help.

Thanks,
Dennis.

 

[Peter T]

Best thing is to print the chart itself as a page. If you have a chart sheet
it's already set up like that. Its size (and shape) is defined by the paper
size and the margins, all of which you can change. An embedded chart on a
sheet can also be similarly printed, as a page. Select the chart and go into
print-preview. Size the margins to suit and press print.

Record a macro while doing the above to get the syntax. Though once you've
sized the margins and changed any other relevant print settings should
persist.

Regards,
Peter T

 

[Dennis Macdonald]

Thanks for the info, I already know of this. It works for a simply
chart on a sheet, but I have 20-50 charts on this seet and I need to
print them 1 per page and therefore need to to size thme to suit.

This is why I need to determine the page sizes to set the charts the
correct size.

Cheers,
Dennis.

 

[Peter T]

I'm a bit confused, in your OP you said -

"I create via VBA a number of charts on an Excel sheet. I want to size
each of these to suit a single page of the printer."

That sounds like one chart per page?

But now you say you want to print up to 50 per page, is that right, they
would be extremely small.

Anyway, assuming you have set your margins you can get your page size like
this -

With Activesheet
wd = .VPageBreaks(1).Location.Left
ht = .HPageBreaks(1).Location.Top
End With

You might need to ensure the sheet has something beyond the respective page
breaks before they will be returned to full size. Keep in mind the page size
will be to the right-most column & bottom-most row that fits between the
margins. You might want to incrementally increase the width of a column by
say 0.75 points until the VPageBreak moves to a more left column, then reset
back a tad (similarly for the first HPagebreak).

Regards,
Peter T

 

[Dennis Macdonald]

Yes, sometimes one and sometimes many per page -this iis why I needed
the page sizes.

I have been working on this a lot more and I think using VPageBreaks
and HPageBreaks may be a better solution.

Excel shows the page markers on the screen with the small dashed line
both vertically and horizontally.

Do you know how to determine which cell these are on via VBA?

Thanks,
DEnnis.

 

[Peter T]

Sub test()
Dim hp As HPageBreak, vp As VPageBreak
Dim ws As Worksheet

Set ws = Worksheets(3)
With ws
' .Range("a100, z1") = 1
Set vp = .VPageBreaks(1)
Set hp = .HPageBreaks(1)
' .Range("a100, z1").Clear
End With

With vp.Location
Debug.Print .Address, .Column, .Left
End With

With hp.Location
Debug.Print .Address, .Row, .Height
End With

End Sub

Regards,
Peter T