Excel and Active Server Pages

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

In Chapter 15 you learned various methods of retrieving data from a Microsoft Access database and placing this data in an Excel worksheet. This section explores another technology, known as ASP, that you can use for accessing and displaying data stored in databases.

Active Server Pages (ASP) is a technology developed a few years ago by Microsoft. Using this technology, you can design powerful and dynamic web applications that change every time they are viewed. ASP is platform independent. This means that you can view ASP pages in any browser. The

External Data Rdnge Properties

Query definition -

W Save guery definition

Save password Refresh control

Enable background refresh B.efresh every [&0 ^ Refresh data on file open

I" Remove external data from worksheet before saving Data formatting and layout P Include field names T~ Preserve column sortffllter/layout r I Preserve cell formatting

W adjust column widfch

If the number of row in the data range changes upon refresh: C Insert cells for new data, delete unused cells f Insert entire rows for new data, dear-unused cells (• ^erwrlte existing cells with new data, dear unused cells ]"" Fill down formulas In columns adjacent to data

Figure 16-21: After retrieving data from a particular web page using the web query, you can use the External Data Range Properties dialog to control when data is refreshed and how it is formatted.

current version of ASP is 3.0 and is available with Microsoft Internet Information Server (IIS 5.0 and above). While HTML (Hypertext Markup Language), which is used for creating web pages, contains text and formatting tags, ASP pages are a collection of HTML standard formatting elements, text, and embedded scripting statements. You can easily recognize an ASP page in a browser by its .asp extension in the URL address:

http://www.nycenet.edu/dist_sch/sch/searchresjlt.asp

Simply put, ASP pages are text files with the .asp extension. ASP code is processed entirely by the web server and sent to the user browser as pure HTML code. Users cannot view the script commands that created the page that they are viewing. All they can see is the HTML source code for the page. However, if you have access to the original ASP file and open this file in any text editor, you will be able to view the ASP code.

Creating ASP pages is quite easy for those who already know Visual Basic for Applications. VBScript, a subset of Visual Basic and VBA, is one of the built-in scripting languages in ASP (another built-in language is JScript). To create an ASP file, you need a simple text editor, such as Notepad. But if you are planning to create professional Internet applications using ASP it's worthwhile to purchase Microsoft Visual InterDev 6.0, which offers many tools for creating and debugging ASP scripts and viewing HTML. It is also a good idea to acquire some working knowledge of HTML. Needless to say, a good place to start is the Internet. For easy, step-by-step tutorials and lessons, check out the following web site addresses: http://www.html-goodies.com or http://www.bfree.on.ca/HTML.

The ASP has its own object model consisting of merely five objects (Request, Response, Application, Server, and Session). The ASP objects have methods, properties, and events that can be called to manipulate various features. For example, the CreateObject method of the Server object enables you to create a link between a web page and your Access database, while the Write method of the Response object allows you to write text to the client browser. You will use these methods while creating an example ASP script later in this chapter.

Let's see how you can use ASP to retrieve some data from the Shippers table in the sample Microsoft Access Northwind database and how to display this data as an HTML table in an Excel worksheet inside your Internet browser.

To create your first ASP script, you will use the following tags:

<% and %> Beginning and end of the ASP script fragment. The script code between the <% and %> tags will be executed on the server before the page is delivered to the user browser.

<HTML> and You should place the <HTML> tag at the beginning of each </HTML> web page. To indicate the end of a web page, use the closing tag: </HTML>.

<TABLE Border

<TH> and </TH> <TR> and </TR>

The text you want to display on the web page should be placed between these tags.

Beginning and end of a table

The Border parameter specifies the width of the table border.

Place table headings between these tags.

The <TR> tag begins a new row in a table. Each table row ends with the </TR> tag.

Use these tags to specify table cells. Each table data cell starts with the <TD> tag and ends with the </TD> tag. Table cells can contain any content, including another table.

Was this article helpful?

0 0

Post a comment