HOWTO: Print Two Excel Charts on Single Page w/OLE Automation

Last reviewed: September 29, 1997
Article ID: Q114348

The information in this article applies to:

- Standard and Professional Editions of Microsoft Visual Basic

  Programming System for Windows, version 3.0
- Microsoft Excel, version 5.0

SUMMARY

It is possible using OLE Automation with Excel version 5.0 to print two charts on a single page. This article demonstrates how to do it in Visual Basic version 3.0.

MORE INFORMATION

Excel version 5.0 exposes its printing capabilities through OLE Automation. The Excel Worksheet object supports the PrintOut method. The PrintOut method prints the Worksheet to the printer using the current PageSetup.

The following example creates and positions two Charts on a Worksheet. Then it modifies the PageSetup, and prints the Worksheet to the printer.

Step-by-Step Example

  1. Start a new Project in Visual Basic. Form1 is created by default.

  2. Add a command button (Command1) to Form1.

  3. Place the following code in the command button click event:

       Sub Command1_Click()
    
          ' Define Excel Constants:
          ' Constant values can be found with "MsgBox xlConst" in Excel
          ' Constants are also in XLCONST.BAS in Office Developer's Kit 1.0
          Const xlColumn = 3
          Const xlRows = 1
          Const xlLine = 4
          Const xlPortrait = 1
          Const xlPaperLetter = 1
          Const xlAutomatic = -4105
          Const xlDownThenOver = 1
    
          ' Dimension Variables:
          Dim XL As Object
          Dim WS1 As Object
          Dim WS2 As Object
          Dim PS As Object
          Dim Col As Integer
    
          ' Create an Excel Application object:
          Set XL = CreateObject("Excel.Application")
    
          ' Make Excel visible:
          ' If the following line is changed to a comment, Excel will not be
          ' visible, but the charts will still print
          XL.Visible = True
    
          ' Add a Workbook and set Worksheet objects to the first two Sheets:
          XL.Workbooks.Add
          Set WS1 = XL.WorkSheets(1)
          Set WS2 = XL.WorkSheets(2)
    
          ' Fill in Cells of first Worksheet with data for Charts:
          Randomize Timer
          For Col = 1 To 10
             WS1.Cells(1, Col).Value = 10 * Rnd
             WS1.Cells(2, Col).Value = 10 * Rnd
          Next
    
          ' Display the second Worksheet:
          WS2.Select
    
          ' Add a Chart object at specified position:
          ' Top and Left are relative to Cell A1
          ' Enter the following two lines as one, single line of code:
          WS2.ChartObjects.Add(0, 0, XL.InchesToPoints(6),
             XL.InchesToPoints(4)).Select
    
          ' Use the ChartWizard method to fill in the Chart:
          ' Enter the following three lines as one, single line of code:
          WS2.ChartObjects("Chart 1").Chart.ChartWizard
             WS1.Range(WS1.Cells(1, 1), WS1.Cells(1, 10)), xlColumn, 1, xlRows,
             0, 0, 1, "Chart 1 (Column Chart)", "Columns", "Value", ""
    
          ' Create a second Chart on the same Worksheet:
          ' Enter the following two lines as one, single line of code:
          WS2.ChartObjects.Add(0, XL.InchesToPoints(5), XL.InchesToPoints(6),
             XL.InchesToPoints(4)).Select
          ' Enter the following three lines as one, single line of code:
          WS2.ChartObjects("Chart 2").Chart.ChartWizard
             WS1.Range(WS1.Cells(2, 1), WS1.Cells(2, 10)), xlLine, 4, xlRows,
             0, 0, 1, "Chart 2 (Line Chart)", "Points", "Value", ""
    
          ' The following lists various property settings for the PageSetup
          ' Object in Excel. There may be additional properties available for
          ' different printers. Please check the Excel documentation for
          ' details on the PageSetup object.
          Set PS = WS2.PageSetup
          PS.PrintTitleRows = ""
          PS.PrintTitleColumns = ""
          PS.PrintArea = ""
          PS.LeftHeader = ""
          PS.CenterHeader = "Two Charts on a Page"
          PS.RightHeader = ""
          PS.LeftFooter = ""
          PS.CenterFooter = "Page &P"
          PS.RightFooter = ""
          PS.LeftMargin = XL.InchesToPoints(.75)
          PS.RightMargin = XL.InchesToPoints(.75)
          PS.TopMargin = XL.InchesToPoints(1)
          PS.BottomMargin = XL.InchesToPoints(1)
          PS.HeaderMargin = XL.InchesToPoints(.5)
          PS.FooterMargin = XL.InchesToPoints(.5)
          PS.PrintHeadings = False
          PS.PrintGridlines = False
          PS.PrintNotes = False
          PS.CenterHorizontally = True
          PS.CenterVertically = True
          PS.Orientation = xlPortrait
          PS.Draft = False
          PS.PaperSize = xlPaperLetter
          PS.FirstPageNumber = xlAutomatic
          PS.Order = xlDownThenOver
          PS.BlackAndWhite = False
          PS.Zoom = 100
    
          ' Print the WorkSheet:
          WS2.PrintOut 1
    
          ' Close the Workbook without saving the contents:
          ' The brackets [] around Close are necessary because Close is
          ' a Visual Basic method.
          XL.ActiveWorkbook.[Close] (False)
          ' Shut down instance of Excel:
          XL.Quit
    
          Set XL = Nothing
          Set WS1 = Nothing
          Set WS2 = Nothing
          Set PS = Nothing
    
       End Sub
    
    

  4. Save the project.

  5. Run the example, and click the command button.

Excel should become visible. You will see data being put into the cells. After the data is entered, you will see the creation of the charts. Finally, Excel will begin printing.


Additional query words: MSGraph graph
Keywords : IAPVBA vbwin GnrlVb kbinterop kbprg
Technology : kbvba
Version : WINDOWS:3.0
Platform : WINDOWS
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.