PostOne Employee Portal

Learn More About:

The Retirement Fund Deferral WordWriter Demo

System Requirements
SoftArtisans OfficeWriter Links
 Website

 Brochures

 Features 		 Download Eval

 Order Now

 More Demos

About the Demo

The Retirement Fund Deferral demo takes advantage of some complex Microsoft Word functionality. In this demo, the Word document is populated with data from a SQL Server database. Upon file open, the MailMerge fields automatically update with the values that have been inserted by WordWriter. The user can then modify values, using the familiar interface of Microsoft Word, and upon document close, the user can choose to send the new values back to the database.

This icon denotes a link to the WordWriter Online Documentation. Documentation opens in a separate window.

Server-side code is marked in gray Courier font.

Note: This explanation contains only portions of the code necessary to run this application.
To view the entire ASP.NET page, use the corresponding "Source" link on the demo index page.


How It Works

Step 1: Create a Template

Use Microsoft Word to create a template that will be used by SoftArtisans WordWriter.

Creating a WordWriter Template

The Retirement Fund Deferral template contains very advanced Microsoft Word functionality, such as graphics and drawing elements, mail merge fields, text and checkbox form fields, footnotes, a footer with nested table, and complex VBA code:

    Graphic & Drawing Elements
       Graphic & Drawing Elements
    Form Fields and MailMerge Fields
       Form Fields and MailMerge Fields
    Footnotes
       Footnotes


Step 2: Add MailMerge Fields - Allow WordWriter To Populate Document With Data

WordWriter uses the Microsoft Word MailMerge feature in order to populate the template with data. Ensure that the values inside the MailMerge fields can be accessed from macros, and from the server-side application, by nesting the MailMerge field inside a simple text form field. Form fields automatically have bookmarks associated. The bookmark value will help to identify the MailMerge field in the VBA code.

Using Microsoft Word to modify the template, create a WordWriter MailMerge field:

  1. Add A Text Form Field
      1. Select "View" from the menu bar

      2. Choose "Toolbars" from the drop-down menu

      3. Click on "Forms" in the Toolbars fly-out

      4. Add a text form field (the button looks like: [ab|]) to the document

      5. Right-click on the new text field and choose "Properties" to view the bookmark value. In order to fetch the value of the Merge Field to be created in the next step, you must associate a name in the Bookmark field in this screen. Leave the Type set to "Regular Text". Note the "Run macro on" property - you can also connect macros to form fields.

        The result should look like this:

  2. Insert a MailMerge MergeField into the Text Form Field
      1. Select "View" from the menu bar

      2. Choose "Field..." from the drop-down menu

      3. Choose the "Mail Merge" category and the "MergeField" field name. In the textbox, after the text "MERGEFIELD", add the same name you gave to the Text Form Field bookmark.

        The result should look like this:

  3. Protect the document to ensure that end-users won't mistakenly delete the MailMerge fields

    Writing a macro is the best way to ensure that fields are not deleted from the template document.

    VBA Code:

    Sub Protect()
    '
    ' Protect Macro
    '
    ActiveDocument.Protect wdAllowOnlyFormFields, True

    End Sub


Step 3: Enhance Document Functionality With Macros

The Retirement Fund Deferral demo makes liberal use of macros in order to provide the user with advanced features, such as the ability to update the server-side database from a client-side Microsoft Word document.

Values from form fields are gathered and passed to HTTP in order to update the database with the new values.

    VBA Code:

    Sub update()
    '
    ' update Macro
    '
      '--- Declare some variables here
      Dim postURL As String
      postURL = "http://localhost/WWDemo/RetirementDeferral.aspx"

      Dim LastName As String
      Dim FirstName As String
      Dim EmployeeID As String
      Dim DeferMethod As String
      Dim strDererAmount As String
      Dim DeferAmount As Currency
      Dim DeferPercent As String

      Dim oHTTP As Object
      Dim strRequest As String

      '--- Grab the values from the active worksheet
      LastName = Trim(ActiveDocument.FormFields.Item("LastName").Result)

      FirstName = Trim(ActiveDocument.FormFields.Item("FirstName").Result)
      EmployeeID = ActiveDocument.FormFields.Item("EmployeeID").Result

      If (ActiveDocument.FormFields.Item("chkA").CheckBox.Value = True) Then
        DeferMethod = "1"
      Else
      If (ActiveDocument.FormFields.Item("chkB").CheckBox.Value = True) Then
        DeferMethod = "2"
      Else
      If (ActiveDocument.FormFields.Item("chkC").CheckBox.Value = True) Then
        DeferMethod = "3"
      End If
      End If
      End If

      strDererAmount = Trim(ActiveDocument.FormFields.Item("DeferAmount").Result)
      If (Len(strDererAmount) > 0) Then
        DeferAmount = CCur(strDererAmount)
      Else
        DeferAmount = 0
      End If

      DeferPercent = Trim(ActiveDocument.FormFields.Item("DeferPercent").Result)

      Dim vals(5) As Variant
      '--- The XMLHTTP object needs form values to be sent in this form:
      '--- "name1=value1&name2=value2&name3=value3", etc
      '--- we're forming a request of that format here
      vals(0) = "FirstName=" & FirstName
      vals(1) = "LastName=" & LastName
      vals(2) = "EmployeeID=" & EmployeeID
      vals(3) = "DeferMethod=" & DeferMethod
      vals(4) = "DeferAmount=" & DeferAmount
      vals(5) = "DeferPercent=" & DeferPercent

      Dim httpstatus As Integer
    On Error Resume Next
      httpstatus = HotDocRequest.SendRequest(postURL, "RetirementDeferralUpdate", Rows)
      If Err.Number <> 0 Then
        MsgBox "Error sending HotDoc request. Could not contact server database update page." & _
          vbCrLf & "Details: " & Err.Description, _
          vbCritical, "HotDoc Request Failed"
        Exit Sub
      End If
    On Error GoTo 0

      If httpstatus = 200 Then
        UpdateDatabase.Enabled = False
        UpdateDatabase.Caption = "Update Successful"
        MsgBox "You have successfully submitted your retirement deferral preferences.", _
          vbOKOnly, "HotDoc Update Succeeded"
      Else
        MsgBox "The HotDoc database update did not succeed. The server-side database update " & _
          "page returned an error. The server returned status code: " & httpstatus, _
          vbCritical, "HotDoc Update Error"
      End If


    End Sub

On document close, the user is prompted to update the database with the values they have chosen in the Word document.

    VBA Code:

    Sub AutoClose()
    '
    ' AutoClose Macro
    '
      vResult = MsgBox("Update backend database?", vbYesNo + vbQuestion)
      If (vResult = vbYes) Then
        Call update
      End If
    End Subvar_Currency



Step 4: WordWriter Processes and Populates the Word Template

Upon request of the document, WordWriter is called from an ASP.NET page, which dynamically populates the template created in the previous steps.

Using a Database as a Data Source

    ASP.NET Code:

    //--- Instantiate the WordWriter.WordTemplate object
    oWW = new WordTemplate();

    //--- Open the template file
    oWW.Open(templatepath);

    //--- Set the datasource with the DataSet containing the recipient information
    oWW.SetDataSource(oDS);

    //--- Process the template
    oWW.Process();

    //--- Begin streaming the processed template
    oWW.Save(Page.Response, String.Format("{0}{1}.doc", strLastName, strFirstName), true);
    Response.Flush();
    Response.Close();


Step 5: Server-Side Code Updates SQL Server Database

Upon document close, the user is prompted to update the data source. The following ASP.NET code pushes the values from the client-side Word document into the SQL Server database.

    ASP.NET Code:

    private void DoHotDocUpdate()
    {
      System.Diagnostics.Debug.Write("DoHotDocUpdate RetirementDeferral");
      string strFirstName = Request.Form["FirstName"];
      string strLastName = Request.Form["LastName"];
      string strEmployeeID = Request.Form["EmployeeID"];
      string strDeferMethod = Request.Form["DeferMethod"];
      string strDeferAmount = Request.Form["DeferAmount"];
      string strDeferPercent = Request.Form["DeferPercent"];

      string connstring = "Provider=sqloledb;" +
        "Data Source=thalia;" +
        "Initial Catalog=OfficeWriterEmployeePortal;" +
        "User Id=owuser;" +
        "Password=owuser";

      //--- Declare some object references
      OleDbConnection oConnection = null;
      OleDbCommand oCmd = null;

      String strUpdateStatement = null;

      try
      {
        // validate parameters
        if (null == strEmployeeID || strEmployeeID.Length == 0)
          throw new System.ArgumentNullException ("EmployeeID");

        if (null == strDeferMethod || strDeferMethod.Length == 0)
          throw new System.ArgumentNullException ("DeferMethod");


        //--- update RetirementFund table
        if (null == strDeferAmount || strDeferAmount.Length == 0)
          strDeferAmount = "NULL";

        if (null == strDeferPercent || strDeferPercent.Length == 0)
          strDeferPercent = "NULL";

        strUpdateStatement = "UPDATE RetirementFund SET DeferMethod=" + strDeferMethod +
          ", DeferDollars=" + strDeferAmount + ", DeferPercent=" + strDeferPercent +
          " WHERE EmployeeID = " + strEmployeeID;

        oConnection = new OleDbConnection(connstring);
        oCmd = new OleDbCommand(null, oConnection);
        oCmd.Connection.Open();

        oCmd.CommandText = strUpdateStatement;
        oCmd.ExecuteNonQuery();

        oConnection.Close();

      }

      catch (Exception ex)
      {
        // log exception to a file

        System.IO.StreamWriter sw = System.IO.File.AppendText (Page.MapPath("filedump/WWDemo.log"));
        sw.WriteLine ("----------------------------------------");
        sw.WriteLine ("Error occured: " + ex.Message);
        sw.WriteLine ("Query: " + strUpdateStatement);
        sw.WriteLine ("FirstName: " + strFirstName + "Last Name: " + strLastName);
        sw.WriteLine ("Employee ID: " + strEmployeeID + "Defer Method: " + strDeferMethod);
        sw.WriteLine ("DeferAmount: " + strDeferAmount + "DeferPerecnt" + strDeferPercent);
        sw.WriteLine(ex.ToString());
        sw.Close();
        System.Diagnostics.Debug.Write(ex.ToString());
        Page.Response.Write(ex.ToString());
        Response.StatusCode = 500;
      }
      finally
      {
        if (oConnection != null)
          oConnection.Close();
      }

    }



End Result:

WordWriter dynamically populates MergeFields from the SQL database into the generated document. The document is then sent to the client, where the end-user can modify values in the document's unprotected form fields. When the end-user closes the document, the client-side VBA script posts the update back to the SQL database, keeping the document in sync with the database.

Click on the graphic below to view a larger version.


This application demonstrates the functionality of SoftArtisans OfficeWriter. The data presented in this application does not represent actual company or employee information. This application is for demonstration purposes only.