About Us

Excelling Web Sites

If you, like us, attend presentations on the latest technology then you will know that one of the latest 'buzz words' is 'three tier applications'. What exactly does this mean, and how does it fit with designing web sites? We all know about client - server technology where the user has an application (Client) that talks to a central information store (server) usually on another machine over a network. This is of course how the Internet works with the browser being the client and the web server being (obviously) the server. Client - Server can also be called two tier technology, so where does the third layer in "three tier" computing come in? This extra tier lives on the server and handles requests from the client. It incorporates some functionality; for example the component may know how to handle a bank account and information would be passed from the client such as the amount, the account number, payee and so forth. The component would debit one account and credit the other. Many different front ends can then reuse these components.

rbr> Where all the bits go..

For example, take a web page that gathers the transaction details from a simple form. This information is passed to the middle tier component, which performs the necessary transactions, and returns a result back to the web page in the form of a 'success' (or otherwise) message.

Now a thought occurred to us about this. If you wanted to give web users access to a spreadsheet model that calculated, say an insurance quote. What would happen if you created a middle tier component that sat server-side and received data from a web page, which then passed this to an Excel spreadsheet via OLE and read the result back from the sheet and displayed it on the web page. "Sounds interesting" we thought, "it should work, but will it work?" In cases like these there is only one way to find out, and that is to do it.

Here Begineth the Lesson

We will now describe how we wrote a test web page that had a couple of input boxes and passed the data to a spreadsheet sitting on the webserver which then returned a calculated result to the web page. This is not a full application as there are many areas that need improving, and it was done more as a test of technology, so don't judge the code too harshly. The web server had IIS4 with ASP installed along with Transaction Server. Excel was also installed on this box.

Public Function Post(ByRef Value1 As Variant, ByRef Value2 As Variant) As String

Dim ObjXL As Object

Dim Result As Variant

On Error GoTo ErrorHandler

Set ObjXL = CreateObject("Excel.application")

ObjXL.Workbooks.Open filename:= "\\server\3tier\sheet.xls"

ObjXL.Range("A1").Select

ObjXL.ActiveCell.FormulaR1C1 = Val(Value1)

ObjXL.Range("A2").Select

ObjXL.ActiveCell.FormulaR1C1 = Val(Value2)

ObjXL.Range("A3").Select

ObjXL.Calculate

Result = ObjXL.ActiveCell.Value

Post = "The answer to calculating " & Str(Value1) & " and " & Str(Value2) & " is " & Result

Exit Function

' Return the error message indicating that

' an error occurred.

ErrorHandler:

Err.Raise Err.Number, "TalkXL.Post", _

Err.Description

End Function

First we fired up VB5, and created a new project then selected Active X DLL.

Next we created a function and defined the various objects that we would need. Value1 and Value2 are the two values from the web page that we will be passing to the spreadsheet, and Result is the calculated cell value returned from the spreadsheet.

Public Function Post(ByRef Value1 As Variant, ByRef Value2 As Variant) As String

Dim ObjXL As Object

Dim Result As Variant

On Error GoTo ErrorHandler

Next we created an instance of the application that we wanted to OLE link to, in this case an Excel Spreadsheet.

Set ObjXL = CreateObject("Excel.application")

Now we loaded the spreadsheet that contained the calculations that we wanted to perform.

ObjXL.Workbooks.Open filename:= "\\server\3tier\sheet.xls"

Now we passed the values from the web page to the component. This should use named ranges rather than absolute cell references, but we were feeling lazy....

ObjXL.Range("A1").Select

ObjXL.ActiveCell.FormulaR1C1 = Val(Value1)

ObjXL.Range("A2").Select

ObjXL.ActiveCell.FormulaR1C1 = Val(Value2)

ObjXL.Range("A3").Select

Made sure the sheet had calculated

ObjXL.Calculate

Passed the value from the calculated field back to the object to be displayed on the web page.

Result = ObjXL.ActiveCell.Value

Post = "The answer to calculating " & Str(Value1) & " and " & Str(Value2) & " is " & Result

Exit Function

The rest is just error trapping routines

' Return the error message indicating that an error occurred.

ErrorHandler:

Err.Raise Err.Number, "TalkXL.Post", _

Err.Description

End Function

What's in a name? ...everything!

Then we set the project attributes to a sensible name (otherwise you get an Active X component that is called Project1 or something equally unenlightening). Now, under the file menu we created our DLL. The correct place for this file is with all the other DLL's, in the system32 directory of your NT server. The component needed to be registered into the registration database and this was done with "regsvr32 myfile.dll" command. (To un-register a component use "regsvr32 /u myfile.dll"). Now your favourite developing environment should show you your component when asked to display a list of components installed.

Now to create the web pages. The first page is just a simple user form that takes a couple of values and passes them to the .asp page that does the real work.

<HTML>

<HEAD>

</HEAD>

<BODY>

Value 1 <input type = text name = "Value1"><br>

Value 2 <input type = text name = "Value2"><br>

</form>

</BODY>

</HTML>

Now we created the asp page called client.asp and this contained.

<HTML>

<HEAD>

</HEAD>

<BODY>

Value1 = request.querystring("Value1")

Value2 = request.querystring("Value2")

set myObject = Server.CreateObject("TalkXL.Class1")

res = myobject.post(Value1,Value2)

response.write(res)

</BODY>

</HTML>

The interesting bit is the code between the <% %> which indicates server-side scripting. The first two lines transfer values from the form to some server-side variables, then we created an instance of our middle-tier object that we called TalkXL. Next we passed the values to our object on the server which then goes off and fires up Excel with the spreadsheet containing the necessary formula. The Object then put the values into the sheet, calculated and returned the result back to another server-side variable called res. The final line wrote this value to the Web Page.

[Insert Graphic mmc.gif Caption: Configuring Transaction Server]

The last stage was to tell transaction server about our component. We fired up the Management console clicked on transaction server, went to "packages installed" and created a new package. Within this package selected "components" and with the right mouse click selected "new/component". A wizard popped up and enabled us to select our component and installed it within transaction server. Now our web page should work, so we entered a couple of values and hit the submit button and hey presto a result was returned.

There we are, OLE automation of Excel via a web page. Easy wasn't it? As you can see the amount of code required is frighteningly small, the tricky bit is getting it all to work together. This makes its own problems in this type of structure, as debugging can be very difficult, with no tools currently available from Microsoft to help.

Now all this seems very complicated just to do a simple calculation but as the spreadsheet is just a file stored on a web server, by changing the formula in this sheet different calculations can be done without any re-coding of a web site or components. The complexity of the calculations is limited only to the spreadsheet program itself. This is not limited to spreadsheets, any OLE automateable program can be accessed this way. One client who is an accountancy firm were considering using this as a way of offering a tax calculator on their web site. Another possibility is automated generation of sales request letters from a web site automating Word and printing the document out for someone in the office to pick it up and send it out with the information pack. The possibilities are endless.

A Funny thing happened.....

They say that some of the best comedy in the world comes from the BBC. Well whilst this little gem is hardly in the same league as the parrot sketch from Monty Python or the chandelier scene from Only Fools and Horses, it still made us laugh. The other day the BBC ran an advert on TV for their on-line services, asking for comments and suggestions from users about how they might improve these. Fine so far, until the point where they ask you to contact them by writing in, no email address was given just a normal postal mail address. Perhaps they will start to use Parcel Force for delivery of their network packets next !

It can not be overstated the importance of keeping your web site up to date, so it is interesting to see some of the big boys break this cardinal rule. Looking at the Tesco site the other day, we thought we'd have a look at their recipe suggestions. When we saw that in Tesco's words " The witching time of year is upon us" we checked the calendar just to make sure and then checked the site again and sure enough Tesco thinks that it's still October 1997!