This is the last of three postings that show how to 1) capture RSSBus service data in Excel, 2) update the data when the worksheet is opened, and 3) update the data when cell values change.

In order to run the examples, you will have to download the zip file as explained in Part 1.  You should also work through Part 2 as we pick up from there. 

Here is the service definition that we will be using today, FileList3.rsb:

<rsb:info title="FileList3" desc="Feed with required path and mask">

  <input name="path" desc="Directory to list" default="." required="true" />

  <input name="mask" desc="File match pattern" default="*" required="true" />

</rsb:info>

 

<rsb:call op="fileListDir" output="out">

  <rsb:push title="[rss:title | def('untitled item')]">

    [out.*]

  </rsb:push>

</rsb:call>

Recall that required="true" in the info statement gets Excel to prompt for a value for path.  Try this URL:

http://localhost:1110/FileList3.rsb?@xls

Select 'open' on the pop-up, and when prompted enter . (period) for the path and * for the mask.  Excel should load the directory listing for you.

Update data when cells change:

Click the URL above again, but this time select 'save' and save the .iqy file on your desktop.

Now open Excel manually and create a worksheet like this, which will serve as our input form.  The values in column B are period and asterisk, in case you can't read them:

While still in Excel, open  Data -> Import External Data -> Import Data...    In the file open dialog box that appears, navigate to the IQY file you saved above and double click.  Excel will ask you where you want to put the data.  Click an empty cell a little further down on your worksheet and then click 'OK'.

When you get the 'Directory to list' popup, click inside cell B1, where the path value (.) is.  Check the 'Use this value…' and 'Refresh automatically…' checkboxes, then click 'OK':

Do the same for the 'File match pattern' popup, but point to cell B2 where the asterisk is. After you click 'OK', Excel should fetch the data.  Try changing the mask value to *.rsb, or the path to demos, and hit carriage return.  Excel should refresh the data automatically.

For a more interesting example, try building an Excel form for AmazonSearch.rsb, included in the zip file.  You may have to download AmazonOps if you don't have it in your connector library.

This is the end of our series of getting business information into Excel.  To learn more about RSSBus, read the RSSBus Feed Server Quick Start Guide.  To learn more about IQY files, read XL97: How to Create Web Query (.iqy) Files.