How To Send Values/Return Results from ASP with Excel Web Query (201048)

MORE INFORMATION

Creating an Active Server Page/Web Query application involves two major components: The Active Server Page and an IQY (Internet Query) file to be used by Excel 97.

For this sample, you'll simply return data in a tabular format that will fill in the columns and cells of our Excel worksheet. To accomplish this, you will return your query results in an HTML table. Note that Excel will pick up other HTML formatting as well including colors, hyperlinks, fonts, and so forth.

For this sample, you'll query the Adventure Works Access database (AdvWorks.mdb), which can commonly be found at \Program Files\Common Files\System\Msadc\Samples\Advworks.mdb.

Create the Active Server Page. Our Active Server Page will retrieve a single value posted by Excel 97 and create a SQL statement using that value as criteria. In an Application (or Virtual Directory) on your IIS server, save an Active Server Page called SampleASPQuery.asp with the following source (you may need to update the location of the Adventure Works database).

<%@ Language=VBScript %>
<HTML>
<BODY>

<%
DBLoc = "C:\Program Files\Common Files\System\msadc\Samples\advworks.mdb"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & DBLoc & ";"

strProductType = Request.Form("ProductType")
strSQL = "SELECT * FROM Products WHERE ProductType = '" & strProductType & "'"

Set objRS = objConn.Execute(strSQL)
%>
<TABLE>
<TR>
<%
For I = 0 To objRS.Fields.Count - 1
  Response.Write "<TD><B>" & objRS(I).Name & "</B></TD>"
Next
%>
</TR>
<%
Do While Not objRS.EOF
  Response.Write "<TR>"
  For I = 0 To objRS.Fields.Count - 1
    Response.Write "<TD>" & objRS(I) & "</TD>"
  Next
  Response.Write "</TR>"
  objRS.MoveNext
Loop

objRS.Close
objConn.Close

Set objRS = Nothing
Set objConn = Nothing
%>

</TABLE>
</BODY>
<HTML>

Create the IQY File. Create a file named SampleASPQuery.iqy in the directory \Program Files\Microsoft Office\Queries. Place the following text in SampleASPQuery.iqy (update the URL to be valid for the SampleASPQuery.asp that you created in Step 1) and save the file. You can simply edit the file with Notepad.exe:
```
WEB
1
http://server/application/SampleASPQuery.asp
ProductType=["ProductType","Please enter a Product Type to search for:"]
						
```
By specifying your IQY file in this format, you will be using the POST method to send your values to the ASP page (retrieved with Request.Form()). You could also create an IQY file like this, which would use the GET method to send our values to the Active Server Page (retrieved with Request.QueryString()):
```
WEB
1
http://server/application/SampleASPQuery.asp?ProductType=["ProductType","Please</WWLINK> enter a Product Type to search for:"]
						
```
Open Microsoft Excel 97 and create a new Workbook. Select cell A1 (the upper-left corner cell). From the Data menu, select Get External Data and Run Web Query. In the Run Query window, select SampleASPQuery.iqy and click Get Data. You will be prompted for a location to place the returned data, leave the default value and click OK.
Now you will be prompted to "Please enter a Product Type to search for:," which you had entered in your IQY file. Enter Boot and click OK. Excel will post to the ASP page and return the results in your spreadsheet. From the Data menu, select Refresh Data. You will again be prompted to enter a Product Type. Enter "SleepingBag" and click OK. Excel will post to the ASP page and return new data according to your new query condition.

How To Send Values/Return Results from ASP with Excel Web Query (201048)

SUMMARY

MORE INFORMATION

REFERENCES