harting and reporting can involve a lot of work. Numerous vendors have made a business out of selling their third party charting and reporting tools. High-end vendors such as Microstrategy, SoftArtisan, or Crystal Reports carry both a hefty price tag and a hefty learning curve. Other vendors offer VBA, ActiveX controls, Java applets, etc that all solve your reporting headaches to some degree or another. But you already have one of the most sophisticated charting and reporting tools at your disposal. This article focuses solely on the extent to which you can leverage the existing capabilities of Excel to deliver powerful, highly flexible charting and reporting solutions.
1. Excel Chart Publishing
Excel Chart Publishing is an interesting way to publish live, or almost live data to a web page using Excel’s powerful charting solutions. This solution is best for generic charts that need not be interactive or personalized in any way, for example, stock charts, web site performance, or survey results).
- You’ll be able to use the full range of chart types and customization features offered by Excel charting.
- This requires only minimal VBA coding skills, most of which is here in this article.
- It is very difficult (or impossible) to create charts or reports that are personalized for each client.
- Likewise, it is very difficult to achieve any level of interactivity using this method.
- You must be able to run Excel somewhere that allows it to write to your web root, or you must be able to transfer the chart image to your web server with some regularity.
- Excel must remain running for as long as you want chart data updated and published automatically.
Procedure for Publishing Excel Charts
Here’s the overview of the steps involved in publishing Excel charts. I’ll explain each of the steps in more detail below.
Import your chart data into your spreadsheet.
Create your chart based on that data.
Add a macro to publish the chart periodically.
|Figure 1: Use Excel to create almost live charts.|
Import Your Chart Data
Once you’ve created a new spreadsheet, you’ll need to tell Excel how to get your chart data. You have three options:
- Get your data from the web using Excel’s web query. To do this, use Data?> Get External Data?> New Web Query and add the URL from which you’ll get your data. Note that this works best if the web page from which you are retrieving your data lays out the data using HTML tables. For an example, see Figure 2 and Listing 1.
- Get your data from a database. To do this, use Data?> Get External Data?> New Database Query and follow the prompts. You may need to set up an ODBC data source in windows if your database is not Access (or FoxPro if you still use that).
- You can place static data in Excel and create your charts from that.
If you want to automatically refresh your data every few minutes, select the “Refresh every?” option under Data?> Get External Data?> Data Range Properties. The top left cell of your web query or database query must be selected for this option to appear on the menu.
Create Your Chart
This is the fun part. Start with the chart wizard (Insert?> Chart) and get the chart contents as close as you can to exactly what you’d like to publish. After completing that, you can customize the color schema, formatting, fonts, et cetera to your heart’s content. Excel’s charting capabilities are quite powerful, and you’ll be able to design something that really stands out if you invest the time. A quick hint for the uninitiated: you can right-click just about anything in an Excel chart to adjust its color or formatting.
Add a Macro to Publish Your Chart
To add a macro, you’ll need to open the Visual Basic editor in Excel; select Tools?> Macros?> Visual Basic Editor. With a little editing, the following sub routine will do the trick for you:
Sub Auto_Open() ActiveWorkbook.PublishObjects.Add(xlSourceChart, _ "H: imetestResponseDist.htm" _ , "Chart1", "", xlHtmlStatic, "DevXSample_16365", _ "Chart Title Goes Here").Publish (True) Application.OnTime Now + TimeValue("00:05:00"), _ "Auto_Open" End Sub
You will need to change the filename “H: imetestResponseDist.htm” in the preceding code fragment to the path and name of your web document root (for example F:inetpubwwwroot). Likewise, you should change “Chart1” to the name of the sheet you’re exporting to the web.
Auto_Open() is a special name for a subroutine that will automatically run when the workbook is opened. In this example, Excel will re-publish the chart when the spreadsheet is opened and then every five minutes because of the OnTime method.
Don’t feel obliged to use the .htm file that Excel generated for you. You can create your own HTML and include an image tag where you want to display the chart. For example:
2. Spreadsheet Download
This is the most flexible and powerful solution among the four options presented in this article. It does require that your users have Excel installed, and to have macros enabled, so it’s probably best suited for intranet clients. Figure 2 shows how a downloaded spreadsheet might look.
|Figure 2. A spreadsheet downloaded into Internet Explorer might look like this.|
- Few limits on interactivity.
- All charting and reporting functions are available.
- Many users are familiar with Excel, its user interface, and many of its advanced features, which means that you will be providing those features without having to code for it.
- Clients to whom I’ve delivered this solution report that their users love the ability to save and manipulate the chart / report data.
- Users must have Excel 2000 or higher in order to import data from a source on the internet.
- Users will be asked if they want to enable macros.
Creating a Downloadable Spreadsheet
- Create a data source Web page.
- Create your downloadable spreadsheet which
- Gets input from the user (if needed),
- Grabs data from your data source Web page,
- Formats that data as necessary, and
- Generates reports and/or charts based on that data.
- Make your spreadsheet available for download.
Creating a Data Source Web Page
This is something you have all done?make a Web page that simply returns an HTML table with the data that you want. I won’t go into the details here, but this is a basic example of what your Web page should output:
|Figure 3: A sample Web page serving as a data source.|
If you are curious, Listing 1 contains the code that generated the above screen shot.
Create the Spreadsheet
- Open a new Excel spreadsheet and give it a name.
- Since you’ll be writing VBA macros, open the VBA Editor (Tools?> Macro?> Visual Basic Editor).
- Make sure that you are adding your code to the correct module. Click on “ThisWorkbook” under your VBA project explorer in the VBA Editor. The VBA project explorer is shown by default; if it has been hidden, click Project Explorer from the VBA Editor’s View menu. You should see VBA Project (your spreadsheet name). Below that is a folder called Microsoft Excel Objects. In that folder is the “ThisWorkbook” object.
Get User Input (if Necessary)
If you want to allow users to input information, this is the proper point at which to gather the user data:
- In Excel, rename Sheet1 to “Inputs” (Tools?> Sheets?> Rename).
- Add the adjustable variables in the cells. For example, set cell B2 to “Start Date”, and C2 to “=now()-180”.
- Name the variables to make them easier to reference in VBA. The easiest way to do this is to click in the name box and change the text from “C2” to “StartDate”. Alternatively, you can Insert?> Names?> Define, then type “StartDate”, then set “Refers To” to “=Sheet1!$C$2”.
To prompt users with data entry messages switch to your VBA Editor and add a subroutine that looks like this:
Sub getInputs() Range("StartDate") = _ InputBox("Enter the start date", , _ Range("StartDate")) Range("EndDate") = _ InputBox("Enter the end date", , _ Range("EndDate")) End Sub
You can add validation as you see fit.
Extract Data from a Data Source Web Page
Change the name of Sheet2 to something such as “Raw Data”. (Tools?> Sheets?> Rename lets you rename a sheet). Activate cell A1.
If your query parameters will never change, add the Web query directly from Excel. Click Data?> Get External Data?> New Web Query. Add the URL from which you’ll get your data and output the data to cell A1. However, if you’re using user inputs or your query parameters change through some other means, then add a subroutine similar to the following in your VBA Editor:
Sub getData() Sheets("Raw Data").UsedRange.ClearContents sURL = "http://timetest.net/Results.cfm?StartDate=" _ & Format(Range("StartDate"), "mm/dd/yyyy") & _ "&EndDate=" & _ Format(Range("EndDate"), "mm/dd/yyyy") With Sheets("Raw Data").QueryTables.Add(Connection:= _ "URL;" & sURL, _ Destination:=Sheets("Raw Data").Range("A1")) .Name = "getData" .Refresh BackgroundQuery:=False End With End Sub
Generate Reports and/or Charts
I’m going to show you here how to have Excel regenerate a chart whenever a client opens the spreadsheet by using a macro. You don’t have to adhere to this. If you want, you can just create a chart that ties into the dynamic data that Excel extracts from the web. The disadvantage of this is that if the size of the data changes in any way, your chart may not properly represent all the data.
The easiest way to build a macro to generate a chart is to turn on the macro recorder, design the chart based on the data that you imported, and then clean up the code as needed; however, that’s not quite as simple as it should be. The data that you import will likely change in size, scale, number of variables, time period, etc. Your recorded macro won’t adapt accordingly, so here are some tips and tricks on how to make that work.
How to find the last row of data for your chart.
Function findLastRow(sSht) sAdd = sSht.UsedRange.Address For i = Len(sAdd) To 1 Step -1 If Mid(sAdd, i, 1) = "$" Then findLastRow = Right(sAdd, Len(sAdd) - i) Exit Function End If Next i End Function
How to find the last column of data for your chart. This will help you adapt your chart to when the number of variables changes.
Function findLastCol(sSht) iRw = 1 ' Row with variable names iCol = 1 Do While sSht.Cells(iRw, iCol) <> "" iCol = iCol + 1 Loop FindLastCol = iCol - 1 End Function
How to delete all the existing charts on a sheet. When you regenerate a chart, you’ll want to be sure that the old chart is deleted first.
Sub delChartObjs() For Each obj In ActiveSheet.ChartObjects obj.Delete Next End Sub
A quick note on Excel and VBA:
Because Excel Visual Basic adds a temporary sheet during the creation of a new chart, references to ActiveSheet will cause the macro to crash. Thus, you’ll need to refer to your chart using a variable for the active sheet. See Listing 2 for an example.
There is a problem in the way Excel places a newly created chart object. You must use the alternative syntax of
ActiveChart.Location xlLocationAsObject, sShtName
where sShtName is the name of the sheet in which to place the object. Your recorded macro probably generated something like:
ActiveChart.Location Where:=xlLocationAsObject, Name:="Inputs"
Be sure to change this.
Listing 2 exemplifies what your VBA project might look like.
Final Notes on Your Downloadable Spreadsheet
You can hide sheets that aren’t relevant for your client. In the sample spreadsheet, Refined Data is a good candidate for this. To hide a sheet, use Tools?> Sheets?> Hide. Note that most VBA methods require that the sheet on which they are performing an operation is visible in order to work properly.
You can also lock cells or sheets if you don’t want your client to change formulas or data. See Tools?> Protection for more information. After locking a worksheet or workbook, you can unlock individual cells using the Format?> Cells?> Protection dialog.
As a final note, you can also password protect your VBA code if you have anything sensitive that you don’t want clients to view. In the VBA Editor, select the Protection Tab from Tools?> VBAProject Properties. Note that this isn’t completely secure. There are services that will crack these passwords for $100-500.
Setting Up the Download
Simply place the Excel file on your Web server where you want your users to download the spreadsheet.
|Note on Security: Excel echoes Internet Explorer’s session. In other words, if a client is logged on to your Web site using Internet Explorer, so is their Excel program.|
3. Excel Web Publishing with Interactivity
This is Excel’s solution to professional looking interactive charting and reporting. The Excel team at Microsoft has put together an excellent suite of functionality to help you publish and report on the Web. There are a few drawbacks, which is why I generally prefer the first 2 methods listed in this article.
- You don’t need to write code to use this solution.
- Client-side software requirements: This requires that users have Internet Explorer 4 or higher and MS Office Web Components. MS Office Web Components comes with Office 2000, but often isn’t installed, which may mean that your user loses interest long before he or she finds an Office 2k CD and goes through the installation process.
- Live data issues: This will only work with live data through what is called an interactive pivot list. This means that you may not get your data published in the format that you’d like it to be. Also, live data will only refresh if the data source is a database query (ODBC or other data sources) to which Excel has direct access.
- Limited interactivity: Unless your reporting is very simple in nature, this solution may not be sufficiently flexible for your needs.
I won’t go into too much depth here, largely because Excel’s help under this topic is quite extensive. For more information, please refer to the Excel help topic “Putting Microsoft Excel data on the Web”.
4. Excel Web Publishing Without Interactivity
This is the perfect way to go if you are working with static data with which your users have no need to interact.
- Requires no coding.
This is an excellent way to produce non-interactive charts based on static data.
- This solution lacks functionality and interactivity.
Again, Excel’s online help for this is quite extensive, so I won’t duplicate efforts in this article. The Excel help topic is “Putting Microsoft Excel data on the Web”, but essentially you will be following the instructions listed in section one of this article on how to create and publish a chart.
Which Solution Is Right for You?
As you’ve seen, there are several solutions with varying levels of interactivity and capabilities. Table 1 consolidates this information and may help you decide which solution is right for you.
Table 1: Which Solution Is Right for You?
Although there are many ways to do charting and reporting over the Internet, Excel can offer a powerful suite of tools for a fraction of the cost and effort involved with other applications.