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.
Creating a Downloadable Spreadsheet
- 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 Data Source Web Page
- 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.
This is something you have all donemake 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
Get User Input (if Necessary)
- 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.
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:
Range("StartDate") = _
InputBox("Enter the start date", , _
Range("EndDate") = _
InputBox("Enter the end date", , _
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:
Generate Reports and/or Charts
sURL = "http://timetest.net/Results.cfm?StartDate=" _
& Format(Range("StartDate"), "mm/dd/yyyy") & _
"&EndDate=" & _
With Sheets("Raw Data").QueryTables.Add(Connection:= _
"URL;" & sURL, _
.Name = "getData"
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.
sAdd = sSht.UsedRange.Address
For i = Len(sAdd) To 1 Step -1
If Mid(sAdd, i, 1) = "$" Then
findLastRow = Right(sAdd, Len(sAdd) - i)
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.
iRw = 1 ' Row with variable names
iCol = 1
Do While sSht.Cells(iRw, iCol) <> ""
iCol = iCol + 1
FindLastCol = iCol - 1
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.
For Each obj In ActiveSheet.ChartObjects
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.
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.