Generate the Chart
All the magic happens when the user clicks the button that says 'Generate Chart,' which submits the form. At the very top of our ASP page, we determine whether the user is returning after the form has been submitted. If that is the case, we branch out to a different subroutine—HandleRepeatVisit—to generate the chart:
mintPass = Request("pass")
Select Case Int(mintPass)
At this point it makes sense for me to declare some constants and variable used in the code:
Dim xlapp ' Our Excel App
Dim wb ' Our Workbook within the Excel App
Dim ws ' Our Worksheet within the Workbook
Dim crt ' The chart object
Dim SourceRange ' The Source Range for the chart object
Const xlWorkSheet = -4167
Const xlLineMarkers = 65
Now we can actually begin the process of generating the chart by creating an instance of an Excel application by opening a new workbook and grabbing the first worksheet:
' -- Create an instance of Excel Application
Set xlapp = Server.CreateObject("Excel.Application")
' -- Create a new workbook
Set wb = xlapp.Workbooks.Add(xlWorksheet)
' -- Grab the first worksheet of the new workbook
Set ws = wb.Worksheets(1)
"ws" points to a blank worksheet in our Excel application in which we will dump the values we wish to chart:
' -- Insert the data the user requested
' -- First, the title
ws.Range("A1").Value = Request("co") ' -- defaults to "Microsoft"
' -- Then the data in two vertical columns
For i = 1 To 12
ws.Range("A" & i + 1).Value = FormatDateTime(i & "/01/99", 2)
ws.Range("B" & i + 1).Formula = "=" & Request("v" & i)
Because we are obtaining user-input values the Request object is used to call the values. First paste the company name within the cell "A1." Underneath that, in columns A and B, we paste the date and the user-entered values, respectively. If we were obtaining data from a database, we would use very similar code, but instead of obtaining data from the Request object and placing it on the worksheet we would use a Recordset object.
At the end of this process, the Excel spreadsheet would look like the image below. Remember though, you cannot actually see this spreadsheet; it is generated in memory on your Web server (see Figure 3).
Next we prepare to generate our chart by invoking the ChartWizard through code. To do that, we need to get a handle to our range object containing the chart data and then use it to insert a chart object on the worksheet. We use the worksheet's Range method to return a range object that we can work with and then add a new entry in the worksheet's ChartObjects collection to obtain a new chart. The Add method of the ChartObjects collection accepts four parameters that determine the location and size of the chart in pixels:
' -- Set our source range
Set SourceRange = ws.Range("A2:B13")
' -- Create a new Chart Object
Set crt = ws.ChartObjects.Add(20, 20, 300, 200)
Now it's time to perform magic. But first, let's take stock of what we've done so far. We placed a ChartObject object on our worksheet. The ChartObject object, in turn, has a property that returns a Chart object. The Chart object has a method we can invoke called ChartWizard. So, if we invoke the ChartWizard method of the Chart object of the ChartObject object within the worksheet, we get the equivalent of clicking the ChartWizard tool button on the toolbar. The syntax of this statement is:
In our case, we will invoke it as follows:
Now, the "Arguments" for the ChartWizard method are as follows:
expression.ChartWizard(Source, Gallery, Format, PlotBy, CategoryLabels, SeriesLabels, HasLegend, Title, CategoryTitle, ValueTitle, ExtraTitle)
- expression: Required. An expression that returns a Chart object.
- Source: Optional Variant. The range that contains the source data for the new chart. If this argument is omitted, Microsoft Excel edits the active chart sheet or the selected chart on the active worksheet.
- Gallery: Optional Variant. The chart type. Can be one of the following XlChartType constants: xlArea, xlBar, xlColumn, xlLine, xlPie, xlRadar, xlXYScatter, xlCombination, xl3DArea, xl3DBar, xl3DColumn, xl3DLine, xl3DPie, xl3DSurface, xlDoughnut, or xlDefaultAutoFormat.
- Format: Optional Variant. The option number for the built-in autoformats. Can be a number from 1 through 10, depending on the gallery type. If this argument is omitted, Microsoft Excel chooses a default value based on the gallery type and data source.
- PlotBy: Optional Variant. Specifies whether the data for each series is in rows or columns. Can be one of the following XlRowCol constants: xlRows or xlColumns.
- CategoryLabels: Optional Variant. An integer specifying the number of rows or columns within the source range that contain category labels. Legal values are from 0 (zero) through one less than the maximum number of the corresponding categories or series.
- SeriesLabels: Optional Variant. An integer specifying the number of rows or columns within the source range that contain series labels. Legal values are from 0 (zero) through one less than the maximum number of the corresponding categories or series.
- HasLegend: Optional Variant. True to include a legend.
- Title: Optional Variant. The chart title text.
- CategoryTitle: Optional Variant. The category axis title text.
- ValueTitle: Optional Variant. The value axis title text.
- ExtraTitle: Optional Variant. The series axis title for 3-D charts or the second value axis title for 2-D charts.
Pay special attention to Item 2. If Source is omitted and either the selection isn't an embedded chart on the active worksheet or the active sheet isn't an existing chart, this method fails and an error occurs.
How did I know all that? Ah, it's good to be a genius <g>. No, actually all of the above can be found in the Microsoft Excel Visual Basic online help and documentation.
Now that we know what the syntax for the ChartWizard method is, we can use it in our code. In our case, we can either create a reference to Excel to be able to use intrinsic constants or we can simply replace the constants with our own or its numeric equivalents. So, our code is as follows:
crt.Chart.ChartWizard SourceRange, 4, , 2, 1, 0, 2, Request("co")
& " Stock Value"
' -- Configure the Chart
crt.Chart.ChartType = xlLineMarkers
crt.Chart.SeriesCollection(1).Name = "=Sheet1!R1C1"
crt.Chart.HasTitle = True
crt.Chart.Axes(1, 1).HasTitle = True
crt.Chart.Axes(1, 1).AxisTitle.Characters.Text = "Months"
crt.Chart.Axes(2, 1).HasTitle = True
crt.Chart.Axes(2, 1).AxisTitle.Characters.Text = "Stock Price"
This code illustrates how to create a chart and then configure its settings. We could have also condensed all eight lines of code above manipulating the "crt.Chart" object into a single line of code by utilizing all the arguments in the ChartWizard method. At this point, our invisible Excel spreadsheet looks like Figure 4.