Prev: Make protected hidden row & column headers appear and rehide based
Next: Counting records with ADO
From: gbruintjes on 28 Sep 2009 15:35
I am using a macro to loop through several workbooks and publish
selected sheets to PDF. The sheets have both tables and charts and are
scaled at about 85% to fit on a single page.

The PDFs retain the correct print area/scaling size, the problem is
the the charts seem to stay at 100% so they end up being cut-off.
Below is the macro code I use. Any ideas?

Sub pdf()
'
' pdf Macro
'

'
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5",
"Sheet6")).Select
Sheets("Sheet6").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\WORK\Template.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub
From: Ron de Bruin on 28 Sep 2009 15:57
Hi

This is a known problem and as far as I know there is no fix

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"gbruintjes" <gbruintjes(a)gmail.com> wrote in message news:2032dec6-7f18-4804-b929-0b8e6cefcae7(a)a21g2000yqc.googlegroups.com...
>I am using a macro to loop through several workbooks and publish
> selected sheets to PDF. The sheets have both tables and charts and are
> scaled at about 85% to fit on a single page.
>
> The PDFs retain the correct print area/scaling size, the problem is
> the the charts seem to stay at 100% so they end up being cut-off.
> Below is the macro code I use. Any ideas?
>
> Sub pdf()
> '
> ' pdf Macro
> '
>
> '
> Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5",
> "Sheet6")).Select
> Sheets("Sheet6").Activate
> ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
> "C:\WORK\Template.pdf" _
> , Quality:=xlQualityStandard, IncludeDocProperties:=True,
> IgnorePrintAreas _
> :=False, OpenAfterPublish:=False
> End Sub
From: EricG on 29 Sep 2009 10:42
A possible fix - just before exporting to PDF, what if you convert the chart
to a picture (either metafile or bitmap) and print that instead? Would it
print out correctly then, or still get cut off?

For example:

'
' Convert_Chart_to_Picture Macro
' For a chart on a worksheet
'
Sub Convert_Chart_to_Picture()
'
Sheets("Sheet1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.CopyPicture Appearance:=xlPrinter, Size:=xlPrinter, Format:= _
xlPicture
Sheets("Sheet2").Select
ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)",
Link:=False _
, DisplayAsIcon:=False
Selection.ShapeRange.Left = 0#
Selection.ShapeRange.Top = 0#
End Sub
'
' Convert_Chart_to_Picture Macro
' For a chart on its own sheet
'
Sub Convert_Chart_to_Picture2()
Sheets("Chart1").Select
ActiveChart.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
Sheets("Sheet2").Select
ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)",
Link:=False _
, DisplayAsIcon:=False
Selection.ShapeRange.Left = 0#
Selection.ShapeRange.Top = 0#
End Sub

You could also scale the size of the picture to ensure it fits the PDF page
if necessary.

HTH,

Eric

"Ron de Bruin" wrote:

> Hi
>
> This is a known problem and as far as I know there is no fix
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
>
>
> "gbruintjes" <gbruintjes(a)gmail.com> wrote in message news:2032dec6-7f18-4804-b929-0b8e6cefcae7(a)a21g2000yqc.googlegroups.com...
> >I am using a macro to loop through several workbooks and publish
> > selected sheets to PDF. The sheets have both tables and charts and are
> > scaled at about 85% to fit on a single page.
> >
> > The PDFs retain the correct print area/scaling size, the problem is
> > the the charts seem to stay at 100% so they end up being cut-off.
> > Below is the macro code I use. Any ideas?
> >
> > Sub pdf()
> > '
> > ' pdf Macro
> > '
> >
> > '
> > Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5",
> > "Sheet6")).Select
> > Sheets("Sheet6").Activate
> > ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
> > "C:\WORK\Template.pdf" _
> > , Quality:=xlQualityStandard, IncludeDocProperties:=True,
> > IgnorePrintAreas _
> > :=False, OpenAfterPublish:=False
> > End Sub
>
 | 
Pages: 1
Prev: Make protected hidden row & column headers appear and rehide based
Next: Counting records with ADO