EXcel Chart Size Probelm with Printer - Please Help
in [Programming]
|
Prev: Stop duplicate record
Next: Conditional formatting?
From: Dennis Macdonald on 22 Apr 2010 23:45 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.
From: Peter T on 23 Apr 2010 08:47 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" <newsgroups(a)bandwood.com> wrote in message news:8fad5c71-1297-438c-912f-e2e011a39b21(a)w20g2000prm.googlegroups.com... >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.
From: Dennis Macdonald on 23 Apr 2010 19:16 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.
From: Peter T on 24 Apr 2010 06:46 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" <bandwood1(a)gmail.com> wrote in message news:01dd13be-2414-47f9-a420-d2729a0510ac(a)o15g2000pra.googlegroups.com... > 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.
From: Dennis Macdonald on 24 Apr 2010 20:15
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. |