Forums     Knowledge Base     OfficeWriter Online     
 
 
This documentation is for
OfficeWriter v3.5.4
ASP/COM Platform
View Docs for Another
Version or Platform

What is HotCell Technology?

ExcelWriter's HotCell Technology provides the ability to dynamically retrieve or update data on a Web server based on modifications made in an Excel file on a client machine. HotCell Technology uses Excel VBA code embedded in the spreadsheet on the client. The VBA code communicates with an ASP.NET or ASP script running on a Web server. The VBA code can be executed when the user modifies a cell's value. A "HotCell" is a cell in the client-side spreadsheet that is modified by the user.

Updating a Server-Side Data Source with HotCell Technology

HotCell Technology allows you to update a server-side data source based on changes a user made to a client-side Excel spreadsheet that contains HotCells. This solution utilizes client-side Excel VBA code to detect when changes have been made to worksheet cells. The address and value of each modified cell is submitted to the server in order to modify the data source. There are two different upload methods that you can use to submit the changed cell data back to the server:

  1. Upload the entire workbook to the server

    This method uploads the entire edited workbook back to the server. When you issue the command to update the data source, OfficeWriter Assistant* (included in OWAssis.cab) uploads the entire workbook back to the server along with information about which cells have changed. On the server, ExcelWriter opens the uploaded workbook, reads the values of the changed cells, and updates the data source. For more information, see the OfficeWriter Assistant documentation.

  2. POST changed-cell instructions to the server

    This method submits cell information in an HTTP POST request. On the server, the receiving ASP.NET or ASP script parses the information and updates the data source. This solution is far less network-intensive because it just posts bits of information instead of the entire workbook. However, the POST method is more complicated than the upload method and requires more code on both the client and server.

POST Example: Editing Cells with Known Positions

The POST example contains the following files:

  • Form.asp
    Form.asp is the sample's entry point. The form contains a drop-down list of employee IDs. The user will select an ID and click the "Get Employee Data Form" button to open an employee data-update form (an Excel spreadsheet). The button also opens a browser window that displays the employee's database record as it appears in the Employees database table.

  • GetEmployeeDataSheet.asp
    This script is activated when you click Form.asp's "Get Employee Data Form" button. The script gets the selected employee's database record, opens an ExcelWriter template, populates the template with the employee's data, and generates an Excel spreadsheet. The generated spreadsheet displays employee data and allows you to modify the data and submit the changes to update the database. When you click "Update" to submit the changes, VBA code uses the XMLHttp client object to send HotCell name/value pairs to Update.asp.

  • Update.asp
    Update.asp takes the new values sent from GetEmployeeDataSheet.asp and updates the employee's database record.

  • DatabaseView.asp
    DatabaseView.asp opens a browser window that displays the selected employee's database record in a table. The database view window opens when you click "Get Employee Data Form" - before any modifications are made. After you modify the employee data, and the database is updated, refresh the database view window to see the changes.

  • BasicFormUpdateTemplate.xls
    Template Excel workbook.
    To see the VBA code go to Tools > Macros > Visual Basic Editor.

Form.asp

<%@ Language="VBScript" %>
<% Option Explicit %>
<%
'--- ErrRecSet will be handled manually in the code
'--- For troubleshooting, comment out this line 
'--- to see the default ASP error output
On Error Resume Next

'--- Declarations
Dim Conn, RecSet

'--- Instantiate and open a database connection
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("connstring")
	
'--- Query the database to get a list of employee ids
Set RecSet = Conn.Execute("SELECT EmployeeID, (LastName+', '+FirstName) _
	As Name FROM Employees ORDER BY EmployeeID")
%>
<HTML>
<HEAD>
<OBJECT 
classid="clsid:381A579F-C095-433d-8AEF-96CED6EC227F" 
codebase="OWAssist.cab" ID="Object1">
</OBJECT>
</HEAD>
<BODY topmargin="0" leftmargin="0" marginwidth="0" marginheight="0" bottommargin="0">
<P>This application allows you to edit data about employees 
of the SoftArtisans Traders Company through an online Excel 
workbook. Select an Employee ID for the employee whose information 
you wish to edit.</P>
<p><b>Note:</b> When you click the button, a new browser window will 
open to allow you to monitor the changes that you are submitting 
to the database with the HotCell-enabled workbook. </p>
<BR><BR> 
<FORM ACTION="GetEmployeeDataSheet.asp" METHOD="POST">
<SELECT NAME="EmployeeID">
<%
'--- For each employee ID, write a option for our drop-down box
Do While Not RecSet.EOF
  Response.Write "<OPTION VALUE=""" & RecSet.Fields("EmployeeID").Value & _
		""">" & RecSet.Fields("Name") & "</OPTION>" 
  RecSet.MoveNext
Loop

'--- Clean up
RecSet.Close
Conn.Close
Set RecSet = Nothing
Set Conn = Nothing
%>
</SELECT>
<BR><BR>
<INPUT TYPE="submit" onClick="javascript:window.open('DatabaseView.asp');" 
VALUE="Get Employee Data Form">
</FORM>
</BODY>
</HTML>

GetEmployeeDataSheet.asp

<%@ Language="VBScript" %>
<% Option Explicit %>
<!--METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
On Error Resume Next

'--- Declarations
Dim Conn
Dim RecSet
Dim XlwTemplate
Dim EmployeeID
Dim SQL

'--- Get the submitted Employee ID from the form
EmployeeID = Request.Form("EmployeeID")

SQL = "SELECT EmployeeID, FirstName, LastName, Title, TitleOfCourtesy, " & _
"BirthDate, City, Country, Region, Address, PostalCode, HomePhone, " & _
"Extension, Notes FROM Employees WHERE EmployeeID=" & EmployeeID

'--- Open a database connection
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("connstring")
CheckError Err, "The database connection could not be opened."

'--- Query the database to get information about the chosen employee
Set RecSet = Conn.Execute(SQL)
CheckError Err, "The database query has failed."

'--- Instantiate the ExcelTemplate object and open the template workbook
Set XlwTemplate = Server.CreateObject("SoftArtisans.ExcelTemplate")
XlwTemplate.Open Server.MapPath("./BasicFormUpdateTemplate.xls")
CheckError Err, "ExcelTemplate could not open the template workbook."

'--- Tell Excel to preserve strings that are numeric by nature as strings. 
'--- This will avoid losing leading "0"s from data such as zip codes.
XlwTemplate.PreserveStrings = True

'--- Set the ExcelTemplate datasource to the recordset
XlwTemplate.DataSource("Emp") = RecSet

'--- Set the postURL in a hidden cell in the workbook
'--- This is the URL to which the HotCell workbook should post
'--- database updates.  Use this technique to be sure that the
'--- HotCell workbook always posts back to the server on which
'--- it was generated
XlwTemplate.DataSource("HotCellPostUrl") = "http://" &_
	Request.ServerVariables("SERVER_NAME") & _
	Application("vroot") & _
	"/HotCells/BasicFormUpdate/ASP/update.asp"

'--- Save and stream the populated workbook
'--- "EmployeeForm.xls" name that will appear in Save As dialog
'--- "saProcessOpenInExcel" option to open the workbook in a new instance of Excel
'--- "False" exclude macros - HotCell templates require that VBA macros be enabled
XlwTemplate.Process "EmployeeForm.xls", saProcessOpenInExcel, False
CheckError Err, "The template could not be processed."

'--- Close ExcelTemplate
XlwTemplate.Close : Set XlwTemplate = Nothing

'--- Close ADO objects
RecSet.Close : Set RecSet = Nothing
Conn.Close : Set Conn = Nothing

'--- This subroutine is called after every major operation
'--- to see if an error has occurred.
'--- If an error has occurred, display a message and stop the script
Sub CheckError(ByRef ThisErr, ByRef msg)
  If ThisErr.number <> 0 Then
    Response.Clear
    Response.Write "<b>" & msg & "</b><br>" & _
      ThisErr.Description & " (" & ThisErr.Source & ")"
    Response.End
  End If
End Sub
%>

Update.asp

<%@ Language="VBScript" %>
<% Option Explicit %>
<!-- #include file="adovbs.inc" -->
<%
On Error Resume Next

Dim Conn
Dim oCmd
Dim strSQL
Dim employeeID
Dim strAddress 
Dim strCity 
Dim strRegion
Dim strPostalCode
Dim strCountry 
Dim strHomePhone 
Dim strExtension 
Dim strNotes
Dim intRowsAffected
Dim p1, p2, p3, p4, p5, p6, p7, p8, p9

'--- Get the values from the form that was submitted by 
'--- the HotCell workbook.
With Request
  employeeID = .Form("employeeid")
  strAddress = .Form("address")
  strCity = .Form("city")
  strRegion = .Form("region")
  strPostalCode = .Form("postalcode")
  strCountry = .Form("country")
  strHomePhone = .Form("homephone")
  strExtension = .Form("extension")
  strNotes = .Form("notes")
End With

'--- Form the SQL statement using the values submitted by 
'--- the Excel macro, via the Request.Form object.
'--- Create the ADODB.Connection object and open the 
'--- connection with the appropriate connection string.
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("connstring")
If Err.number <> 0 Then
  Response.Write Err.Description
  Response.Status = 500
  Response.End
End If

'--- Create an ADO command to handle the update
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = Conn

'--- The command will execute this SQL query
strSQL = "UPDATE Employees SET " &_
  "Address=?, City=?, Region=?, PostalCode=?, " &_
  "Country=?, HomePhone=?, Extension=?, Notes=? " &_
  "WHERE EmployeeID=?"

oCmd.CommandText = strSQL
	
Set p1 = oCmd.CreateParameter("@Address",_
	adVarChar, adParamInput, 100, strAddress)
oCmd.Parameters.Append p1
Set p2 = oCmd.CreateParameter("@City",_
    adVarChar, adParamInput, 100, strCity)
oCmd.Parameters.Append p2
Set p3 = oCmd.CreateParameter("@Region",_
    adVarChar, adParamInput, 100, strRegion)
oCmd.Parameters.Append p3
Set p4 = oCmd.CreateParameter("@PostalCode",_
    adVarChar, adParamInput, 100, strPostalCode)
oCmd.Parameters.Append p4
Set p5 = oCmd.CreateParameter("@Country",_
    adVarChar, adParamInput, 100, strCountry)
oCmd.Parameters.Append p5
Set p6 = oCmd.CreateParameter("@HomePhone",_
    adVarChar, adParamInput, 100, strHomePhone)
oCmd.Parameters.Append p6
Set p7 = oCmd.CreateParameter("@Extension",_
    adVarChar, adParamInput, 100, strExtension)
oCmd.Parameters.Append p7
Set p8 = oCmd.CreateParameter("@Notes",_
    adVarChar, adParamInput, 1000, strNotes)
oCmd.Parameters.Append p8
Set p9 = oCmd.CreateParameter("@EmployeeID",_
    adInteger, adParamInput, , employeeID)
oCmd.Parameters.Append p9

'--- Execute the SQL statement we just formed above
If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
  '--- The "128" is an ADO contant that tells the object
  '--- that we're not expecting any recordset back from this
  '--- call to the Execute method
  oCmd.Execute intRowsAffected
  If Err.number <> 0 Then
    Response.Write Err.Description
    Response.Status = 500
    Response.End
  End If
 End If

Conn.Close
Response.End

'--- Use this code to figure out what error occurred, if any.
'--- An error decsription log will be written to the script directory
'--- This log can be very useful in debugging a HotCell application because
'--- it will capture the error that occurred on the server.
'--- The web server needs write access to the log directory in order for
'--- this to function properly
If Err.Number <> 0 Then
  Dim db
  Set db = Server.CreateObject("EZDebug.DebugString")
  db.OutputDebugString strSQL
  db.OutputDebugString "RA: " &intRowsAffected
  db.OutputDebugString Err.number &" : " &Err.Description &_
      " : " &Err.Source
  Set db = Nothing
  Response.Status = 500
End If

'--- Close the connection
Conn.Close
Set Conn = Nothing

'--- This subroutine is called after every major operation
'--- to see if an error has occurred.
'--- If an error has occurred, display a message and stop the script
Sub CheckError(ByRef ThisErr, ByRef msg)
  If ThisErr.number <> 0 Then
    Response.Clear
    Response.Write "<b>" &msg &"</b><br>" &_
        ThisErr.Description &" (" &ThisErr.Source &")"
    Response.End
  End If
End Sub
%>

DatabaseView.asp

<html>
<head>
<title>OfficeWriter - Database Viewer</title>
</head>
<body>
<form>
<input type="button" value="Refresh to View Changes" 
onclick="javascript:window.location.reload(true);">
</form>
<h3>Database Viewer: Employees table</h3>

<p>Click "Refresh" to see your changes after you submit them from 
the HotCell worksheet.</p>

<table border=1>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("connstring")

	SQL = "SELECT EmployeeID As [Employee ID], FirstName As [First Name], " &_
	    LastName as [Last Name], Title, " & _
	    "TitleOfCourtesy As [Title Of Courtesy], " & _
	    "BirthDate As [Birth Date], City, Country, Region, Address, " & _
	    "PostalCode As [Postal Code], HomePhone As [Home Phone], " & _
	    "Extension, Notes FROM Employees ORDER BY EmployeeID"

Set RecSet = Conn.Execute(SQL)

Response.Write "<TR>"
For i=0 To RecSet.Fields.Count-1
	Response.Write "<TH>" & RecSet.Fields(i).Name & "</TH>"
Next
Response.Write "</TR>"
	

Do While Not RecSet.EOF
	Response.Write "<TR>"
	For i=0 To RecSet.Fields.Count-1
		Response.Write "<TD> " & RecSet.Fields(i).Value & "</TD>"
	Next
	Response.Write "</TR>"	
	RecSet.MoveNext
Loop 
%>
</table>
</body>
</html>



Copyright 2005 © SoftArtisans, Inc. All Rights Reserved.