Updating Excel From the Web

By Bill Jeffries


Too often, Microsoft Excel spreadsheets are updated through the laborious and tedious process of manually importing data from an external data source–or, worse yet, by cutting and pasting. Excel 97 implements a powerful, yet underpublicized feature called Web queries to simplify this task.


Web queries provide spreadsheets with the ability to update selected cells over an HTTP connection. By running a Web query, the user can circumvent manual data manipulation and make the spreadsheet update itself directly over the Web. By combining this feature with ASP, developers can create extremely powerful intranet and Internet Excel solutions.


To run a Web query inside Excel 97, go to the Data menu, click Get External Data, and select Run Web Query. Select the Web query from the dialog box, and press Get Data. Next, choose the cell that represents the top left corner of the Web query result. If the Web query requires any parameters, you will enter them in subsequent dialogs.


Figure 1


Press OK and watch the magic unfold. A small globe will spin in the status window at the lower right section of the screen. This indicates that Excel has initiated an HTTP connection and is retrieving the Web page for display. The results will appear in the spreadsheet.

Getting Started



A great way for developers to get familiar with Web queries is to download the Microsoft Excel Web Query Connectivity Kit.


The magic glue that connects an Excel spreadsheet with a Web page is a text file having an iqy extension. These iqy files can be created quickly using Notepad. Excel reads this simple, four-line file to determine which Web page to request. In addition, the file instructs Excel about which data to pass to the Web page. Let’s first examine the contents of a sample iqy:





WEB
1
http://www.myserver.com/MonthlySales.asp
Month=[“Month”,”Enter month (1-12).”]





Line 1 and 2 are optional lines because WEB and 1 are the only options, respectively. The third line is the name of the Web page that is requested by Excel. The fourth line indicates POST parameters that are sent in the HTTP request. In this case, the form variable Month is sent to the Web server. Inside the brackets are two visual cues. The first string is the title of the parameter’s dialog box. The second string is the label used to prompt the user for the parameter.


To send a query using the GET method, the fourth line would instead be concatenated at the end of the third line:





http://www.myserver.com/MonthlySales.asp?Month=[“Month”,”Enter month (1-12).”]




Excel will convert only a subset of HTML into Excel cells. For a complete list of supported and unsupported tags, refer to the Connectivity Kit. Web queries are geared toward HTML tables, for obvious reasons. Excel even adds some HTML table attributes that add to the interaction between Excel and the HTML table.


ASP developers can create Web queries (iqy files) that point to ASPs, either located on a company intranet or on the public Internet. Let’s continue with our Monthly Sales example. Above, we looked at the MonthlySales.iqy.


MonthlySales.iqy would be saved under Program Files\Microsoft Office\Queries so that Excel can find it.


Next, let’s look at MonthlySales.asp. This page assumes that there is a server-side COM object, Acme.Sales, that accesses company sales data from the company intranet:





<%@LANGUAGE=VBSCRIPT%>
<%
Dim lMonth
Dim objSales
Dim rsSales

lMonth = Request.Form(“Month”)
Set objSales = Server.CreateObject(“Acme.Sales”)
objSales.lMonth = lMonth
Set rsSales = objSales.Sales
%>
<HTML>
<HEAD>

</HEAD>
<BODY>
<TABLE border=0>
<TR>
<TD> ProductID</TD>
<TD> Product Name</TD>

<TD> Sales Person</TD>
<TD> Date Closed</TD>
<TD> Sale Amount</TD>

</TR>
<% While Not rsSales.EOF %>
<TR>
<TD><%= rsSales (“ProductID”)%></TD>
<TD><%= rsSales (“ProductName”)%></TD>

<TD><%= rsSales (“SalesPerson”)%></TD>
<TD><%= rsSales (“SalesDate”)%></TD>
<TD><%= rsSales (“SalesAmount”)%></TD>
</TR>

<% rsSales.MoveNext%>
<% Wend %>
</TABLE>
</BODY>
</HTML>




By having a copy of MonthlySales.iqy, anyone in the company can now run a monthly sales report by running this Web query.


Figure 2


Web queries are part of the Excel object model, so there are a number of impressive ways to manipulate them through VBA (Visual Basic for Applications) code. Excel uses the QueryTable object for Web query automation. This object can be linked to button events and so forth to extend the power of Web queries. Another powerful feature of Web queries is their ability to automatically refresh when a workbook is opened.


Excel’s Web query technology adds some custom table data cell (TD) attributes that can enhance the spreadsheet. One of these attributes is Filter. Placing Filter inside a <TD> causes drop-down filters to appear in the column headers of the spreadsheet. Let’s place a FILTER=ALL inside the <TD> tags in the column header:





<TR>

<TD FILTER=ALL > ProductID</TD>
<TD FILTER=ALL > Product Name</TD>
<TD FILTER=ALL > Sales Person</TD>
<TD FILTER=ALL > Date Closed</TD>

<TD FILTER=ALL > Sale Amount</TD>
</TR>




You would now be able to filter down the results by using drop downs. For instance, you could drop down the Sales Person filter and view the results for a specific sales person. The filtering is all client side (inside Excel) and the results are instantaneous. Excel simply hides the rows that don’t meet the filter criteria.


Another custom attribute that can be added to the HTML table is Formula. When Formula is used inside a <TD>, any Excel formula can be used inside the cell. An example would be for summary cells, where you want to show averages and the like:




<TR>
<TD FORMULA=”=Avg(A4:A20″></TD>
<TD FORMULA=”=Sum(A4:A20″></TD>
</TR>



Again, this can modified using ASP scripting. If you are tracking the number of rows you are writing to the HTML table, you can dynamically write Excel formulas:




<TR>
<TD FORMULA=”=Avg(A<%=iFirstRow%>:A<%=iLastRow%>”></TD>

<TD FORMULA=”=Sum(A<%=iFirstRow%>::A<%=iLastRow%>”></TD>
</TR>




The majority of styles used in Microsoft Internet Explorer’s (IE’s) cascading style sheet (CSS) will be converted properly by Excel. However, pay special attention to the number format. By default, Excel imports all data into the General format. If you need a specific, precise Excel number format, you need to add a style attribute on the ASP side. This style attribute takes the form of vnd.ms-excel.numberformat:xxx. To ensure that a cell uses a precision of 2, your tag would look like the following:



<TD STYLE=”vnd.ms-excel.numberformat:#.##”> ><%= rsSales (“SalesAmount”)%></TD>




For more detailed information on these HTML extensions, please consult the Connectivity Kit.


Once the data is resting safely in Excel, there are an infinite amount of possibilities for further data manipulation. You can link the cells from the resulting HTML into other worksheets, workbooks, Word documents, and charts. For a good example of this, experiment with Pcquote.xls, which comes with the Connectivity Kit.


Excel even integrates with the IE security model, and will display an authentication dialog for ASPs protected by IIS. Just like IE, Excel will cache the user name and password until that instance of Excel is shut down.

Excel 2000 has added some new wrinkles to Web queries. The basic premise is the same, but 2000 makes it easier and faster to customize a Web query. Once developers learn the Excel 97 version, they will really come to appreciate these changes in Excel 2000.


Microsoft has made a strong case for simple, Web-based Excel reporting solutions. The real value comes from ASP and the use of dynamic variables. This type of solution frees up the business analyst from time-consuming data manipulation and takes away a degree of human error. And, best of all, the implementation of Web queries is most assuredly rapid development.


About the Author



Bill Jeffries is an independent Web developer located in the Washington, D.C., area. He specializes in building Microsoft Internet applications centered on ASP technology. Bill is currently working on a government ecommerce site. He holds a computer science degree from the U. S. Naval Academy. Hobbies include hockey, reading, and his beloved Orioles. He can be reached at bill@billjeffries.com.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read