Print Graphs to XPS 1

[shelby55]

Hi

I am using Excel 2003.

I have a workbook with many graphs...is it possible to create a button with VBA behind it to print multiple graphs into a single XPS file? If so, can someone please point me in the direction of where to start on this?

Thanks very much.

 

[Gruuuu]

Record a Macro. Make sure you actually select your XPS driver as the printer and actually print. Excel will capture all of those actions.

Then, you should be able to loop through your graphs and print them out as part of your code.

 

[shelby55]

Hi

Thanks Gruuuu...I don't know why I always forget about the macro recorder but of course that is a great place to start!

 

[shelby55]

Hi

The code the macro gave me was:

ActiveChart.ChartArea.Select
    Application.ActivePrinter = "Microsoft XPS Document Writer on Ne00:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "Microsoft XPS Document Writer on Ne00:", Collate:=True

But I want to be able to have all charts within my workbook print and print into a single document. Each chart is 1 full 8 1/2 x 11 page and I have 8 charts so the document will be 8 pages, each chart appended to the file.

Is this even possible with XPS? Thanks.

 

[PHV]

What about this ?
ActiveWorkbook.PrintOut ActivePrinter:="Microsoft XPS Document Writer on Ne00:"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

Indeed. Printing out the workbook should include the charts in one printout. You would only need to set the print ranges and page setups to fit your needs.

 

[shelby55]

Hi PHV

Thanks....but there are some worksheets in the workbook that are huge because they are the raw data being used by the graphs (300 rows or more). Is there anyway to exclude some of the worksheets (though I doubt it since the whole purpose of using this technique is to print the whole workbook)?

Thanks.

 

[PHV]

Do you know the names of the sheets you want to print ?

Sheets(Array("SomeSheet", "AnotherSheet", "YetSomeOtherSheet)).Select
ActiveWindow.SelectedSheets.PrintOut ActivePrinter:="Microsoft XPS Document Writer on Ne00:"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

Just select the pages with the charts on them, print Selected Sheets (I don't remember exact syntax, but you can record the macro to see it).

Also you can set your print ranges.

Unfortunately you won't be able to set your graphs to more than one chart per page unless that's an option in the XPS print driver settings, which you can set as default settings.

 

[shelby55]

Hi

Thanks. I don't want to print more than one chart per page, just one per page but have one document with all 8 in the same document (i.e. an 8 page document, with each chart on one full page).

I'm still not sure where to start but I'll keep looking..thanks.

 

[Gruuuu]

When you print from Excel, you can choose to Print Active Sheet(s)

This includes all sheets that are selected (ctrl or shift+click on other sheets).

So if you've got only the chart sheets selected and you Print Active Sheets...