Inserting Awasu Data Into Microsoft Excel

From AwasuWiki
Revision as of 01:57, 5 April 2010 by Kevotheclone (talk | contribs) (New page: Microsoft Excel features a Field named '''INSERTTEXT''' that can be used to insert text from an external file or URL into the current Workbook. ''Beginning with Excel 97, the '''INCLUDETE...)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Microsoft Excel features a Field named INSERTTEXT that can be used to insert text from an external file or URL into the current Workbook. Beginning with Excel 97, the INCLUDETEXT Field also provides the ability to process the contents of the inserted text with an XSLT file.

A "live" link to the text

The text inserted by the INCLUDETEXT Field is typically a "live" link to the associated data source, so that any changes made to the original data source will automatically be reflected in your Excel Workbook. This "live" link can easily be disabled, or permanently broken altogether; depending upon your goal, a live link to the current data could be a good feature or a bad feature.

Retrieving the Awasu data

Using Awasu 2.4.2, type an API URL to retreive the data that you want inserted into the Excel Workbook. As an example, if you want to retrieve all of a specific Workpad's items from a Workpad whose Workpad ID is 784FCEFF-BBB5-497A-B0C2-7415F5B4C50B, you would construct a URL similar to this:

http://localhost.:2604/workpads/list?token=MyToken&items=true&id=784FCEFF-BBB5-497A-B0C2-7415F5B4C50B&format=HTMLTable

Note: Make sure to replace MyToken with your Awasu Token. Also your TCPIP port may not be 2604, and the server doesn't have to be localhost, if Remote access has been enabled in Awasu 2.4.2 Professional, you can retrieve Awasu data across a network connection. And note that I've created a custom Awasu API template that formats a Workpad's items into an HTML table and I'm using my custom template by adding "&format=HTMLTable" to the URL.

My custom template is listed here. If you copy and paste this text make sure you save it in your API directory with the file name of workpads-list.HTMLTable. The file extension of API templates must match the value of the format paramter in the URL.

{%REPEAT% Workpads}
<table rules="all" style="border-collapse:collapse;">
  <caption>{%WORKPAD-NAME%}{%?WORKPAD-DESCRIPTION%} - {%WORKPAD-DESCRIPTION%}{%ENDIF%}</caption>
  <thead>
    <tr>
      <th>Title</th>
      <th>URL</th>
      <th>Description</th>
      <th>Notes</th>
    </tr>
  </thead>
  <tbody>
    {%REPEAT% WorkpadItems}
    <tr>
      <td>{%ITEM-TITLE%}</td>
      <td>{%ITEM-URL%}</td>
      <td>{%ITEM-DESCRIPTION%}</td>
      <td>{%ITEM-NOTES%}</td>
    </tr>
    {%/REPEAT%}
  </tbody>
</table>
{%/REPEAT%} {%SET-CONTENT-TYPE% text/html}

Inserting the INCLUDETEXT field

Note: These instructions are from Excel 2003 and should work similarly with previous Excel version. Excel 2007's "Ribbon" has dramatically changed Excel 2007's user interface, so the steps may be different in Excel 2007.

The easiest method to insert and INCLUDETEXT field is to:

  1. Position the insertion point at the location where you want the inserted Awasu data to appear.
  2. Select Field... from the Insert menu.
  3. Select Links and References in the Categories drop down list box to filter the selectable list of Fields.
  4. Select IncludeText from the Field names box.
  5. Paste the Awasu API URL that you copied in the able step into the Filename or URL box.
  6. Optional: If you do not want the inserted Awasu data to automatically refresh, tick the Prevent fields from being updated checkbox.
  7. Click the Ok button.

Updating the included text

There are several actions that can cause the included text to be updated from the data source (Awasu):

  1. Right clicking on the field and selecting "Update Field" (updates a single field)
  2. Pressing the F9 key in Excel (updates all fields)
  3. Printing the Workbook if the "Update Fields" checkbox is ticked in the Tools, Options dialog box ("Print" tab)
  4. Opening the Workbook if the "update automatic links at Open" checkbox is ticked in the Tools, Options dialog box ("General" tab)
  5. Clicking the "Update Now" button in the Edit, Links dialog box

Breaking the link to the text

To permanently break the link between Excel and Awasu:

  1. Select Links... from the Edit menu.
  2. Select the link you want to break from the Source file list box.
  3. Click the Break Link button
  4. Click Yes in the confirmation dialog box.
  5. Click the OK button.