Back to Papers and Articles
Using Excel As a Reporting Engine
Copyright 1999 Paragon Corporation   ( May 26, 1999)
Excel can make a great reporting engine if you have a basic understanding of the Excel Object Model.

Excel can be used as a reporting engine in situations where a standard reporting engine is inadequate. Here are some situations:

  • when you don't know the number of fields or columns in a report beforehand (e.g., budget schedules);
  • when you would like to autofit onto any paper size (report writers confine you to layout a report for a particular page type);
  • when you would like sophisticated shading on columns; and
  • when you would like to allow user customization (e.g., adding totals or what-if analysis on a generated report).

In this article, we'll go over the basics of Excel Automation with VBA and delve into some neat capabilities. We'll cover setting cell values, cell formulas, and number formats, and then get into more intricate things like drawing borders, shading columns, and setting cell tooltips. In a later article, we will query an Access database and render the results in Excel.

Excel Object Model - Key Objects

The Key Objects in Excel starting from the top of the hierarchy and working down are
  • the Workbook Object,
  • the Worksheet Object,
  • the Chart Object,
  • the Range Object (which can be composed of columns, rows, or a named set of cells),
  • Borders, and 
  • the Cell (technically the smallest range you can create).
There are certain objects that are not unique to Excel yet are very important to know for report generation in Excel. These are
  • the Application (the Excel application - the starting point for creation of workbooks and sheets),
  • the Font Object,
  • the Page Setup Object (which allows you to set such things as autofit and landscape/portrait),
  • Shape Objects and Tooltips (which allows you to embed tooltips in Excel cells and manipulate shapes, e.g., the shape of a tooltip balloon).

Creating the Excel Spreadsheet in VBA

An Excel spreadsheet can be generated from any VBA-enabled product such as all the Office 97 applications or Visual Basic 5/6. These tools have an intellisense feature (auto code fill) which becomes enabled if you reference the Excel VBA library. Once you reference the VBA library you can create Excel spreadsheets and instances of Excel using the modern "New" keyword approach. Without referencing the Excel VBA library, you lose the auto sense functionality and must resort to using the CreateObject() OLE construct. For simplicity, we will use the more modern reference approach in our examples.

Step 1 - Reference the "Microsoft Excel 8.0 Object Library" when you are in the VBA module design mode. This is usually found under Tools - References.

Step 2 - Create a VBA module and declare a global object as an Excel.Application like so: 
Dim appXl As Excel.Application
We declare an Excel application at the global level so that when we create the application in a subroutine or function, it does not lose scope when the subroutine or function is completed.

Step 3 - Create the function or subroutine.
This would look something like this: 
Sub CreateTestXl()
    Dim currentRow As Integer, currentColumn As Integer
    currentRow = 1
    currentColumn = 1
    Set appXl = New Excel.Application
    
    appXl.Workbooks.Add        'Creates a new workbook

    With appXl.ActiveSheet
        .Cells(currentRow, currentColumn).Value = "THIS IS A TEST EXCEL SHEET"
        .Cells(currentRow, currentColumn).Font.Bold = True
        .Cells(currentRow, currentColumn).Font.Name = "Times New Roman"
        .Cells(currentRow, currentColumn).Font.Size = 11
        .Cells(currentRow, currentColumn).HorizontalAlignment = xlCenter

       currentRow = 2
       .Cells(currentRow, currentColumn).Formula = "=Now()"
    End With
 
End Sub
 The example above creates an instance of an Excel application, creates a workbook object in that application window, sets some properties of Cell A1, and sets the formula of Cell A2 to display the current date and time. Note that we can use Excel specific constants such as xlCenter. If we had not first referenced the Excel Object Library, then we would have had to specify the integer equivalent of the constant, which is -4108 for xlCenter . This is another benefit of using library references.

Merging Cells - To merge cells together into a single cell, use the Merge method on a range object. For example appXl.ActiveSheet.Range("A1:C1").Merge would merge cells A1, B1, C1 into a single cell in the active sheet of the appXl application we defined previously.

Borders - To draw a border around a cell, use the BorderAround property of a cell object. For example appXl.ActiveSheet.Cells(1, 1).BorderAround Weight:=xlThin draws a border around cell A1.

Page Setup
The PageSetup object allows you to control the laying out of an excel spreadsheet. Below is a little snippet of code that sets the Active Sheet to fit on a page 1 page wide and 1 page tall, sets the left, right, top, and bottom margins to 0.25 inches, prints the first 6 rows as the header for each printed page, and sets the print area. 

   With appXl.ActiveSheet.PageSetup
            .Orientation = xlLandscape
            .Zoom = False 'Force to use fit to page
            .FitToPagesWide = 1
            .FitToPagesTall = 1
'Do not print the grid lines on the printout
            .PrintGridlines = False  
'Set left margin to 0.25 inches
            .LeftMargin = appXl.InchesToPoints(0.25)  
'Set right margin to 0.25 inches 
            .RightMargin = appXl.InchesToPoints(0.25) 
            .TopMargin = appXl.InchesToPoints(0.25)
            .BottomMargin = appXl.InchesToPoints(0.25)
'Print the first 6 rows as the header on each sheet
            .PrintTitleRows = appXl.ActiveSheet.Range("A1:A6").Address 
            .PrintArea = "$A$1:$D25$"
    End With


Del.icio.us Del.icio.us | Reddit | Digg Digg | Blinklist BlinkList | Furl Furl Spurl Spurl | Add to Simpy Simpy



Back to Papers and Articles

Last rejuvenated: 10/16/2024 1:09:25 AM        © 1997 - ∞ Paragon Corporation   All Rights Reserved