Just a very quick tip to show how you can dynamically pull your latest Notes / Domino data into an Excel Spreadsheet.
Note that this method will only work for domino applications which do not require authentication
Basic Idea
In Excel go to Data and click on the Data and then “From Web” options
Next paste in the URL of your Domino XML feed ( more on that later )
Accept all of the options and your data will be added in a nicely formatted table.
The table is also given a name so that when you reference it from pivot charts etc.. the chart will always contain all of the data from the XML feed.
Creating the XML Feed
I knocked up a simple agent that will generate XML based on a search string or a view name and some other configuration.
The code is not great and should really have been done using classes but it works well enough. I have attached a demo database to the end of this article.
The config looks like this :
selection##view##vActionsAll##
reference##field text##Reference_Tx##TBA
days_old##field delta date days##IssuedDate_Tx##@now
unid##formula##docalcres = “this bit is calculated => ” + doc.universalid##
The construction of the configuration varies. For line 2
part1 = XML property name
part2 = “the method”
part3 = the Notes filed name
part4 = the value to publish if the field name is empty
for line 3
part1 = XML property name
part2 = “the method”
part3 = the first date
part4 = the second date
for line 4
part 4 is a lotusscript expression that will be evaluated.
You can see the output here :
http://deliverytoolkit.com/public/fdt/moc/mocdemo.nsf/xmlagent?openagent&XML_Feed_Test
Numbers as text and modifying the schema
Although Excel is very clever in automatically generating a schema for the XML feed it does not allow you to edit it. This is where the new .xlsx format comes into its own.
If you change the .xlsx to .zip you can open the Excel file and modify the schema.
Note : you will need to delete the affected column in Excel and re-create it for the new schema to kick in
Thanks to Gary Devendorf
I have been using variations of this technique for some time. The idea originally came from Gary Devendorf who has published examples of how it can be done with earlier versions of Excel.
Download
The download link is below. You can see examples of the same database here :
http://seancull.co.uk/public/examples/SC/XML_Excel_Demo.nsf/xmlagent?openagent&XML_DEMO_View