|
|
|
|
|
|
|
|
|
|
|
|
|
|
ADVANCED WEB DESIGN COURSE
How to query Domino from within Excel
By Jeffrey R. Burrows
In my "Linking Domino to Excel via XML" article in October's DominoPower, I demonstrated some techniques for delivering Excel spreadsheets directly from Notes views. I also showed Domino agents that could create Excel pivot tables. In this article, I'll show you how to make querying easier and more intuitive for Excel users. I'll also show you how you can deliver your corporate data to your Excel users by removing the need for middleware.
There are several ways to get a view served up by Domino into Excel. The simplest way, described in the previous article, is to save the page from the Web browser, and then open the HTML page in Excel. The downside to this method is that your user must switch between a browser and Excel, and must change the selection options in the file open dialog box to search for all or HTML files. Not all users can be expected to cope with opening non-.XLS files in that manner. Cutting and pasting between the browser window and Excel works well, but is inconvenient for large amounts of data and can also be difficult for inexperienced users.
You might think that an easy solution would be sending out Excel worksheets with built in hyperlinks, using Excel's =HYPERLINK function (or the equivalent in VBA). However, Excel will not open these as worksheets, but will open the default Web browser to display the page.
There are only two ways to directly open Web pages in Excel itself (as opposed to in a browser window). The first is to type the URL of the Domino view page directly into the File Open dialog. This will load the page and display it inside Excel as a worksheet in one operation. However, typing URLs (and especially Domino URLs!) is difficult and prone to error.
The other way is to use Excel Web Queries.
Excel Web Queries
You may have noticed the new Run Web Query option on the Excel Data menu. However, unlike the Microsoft Query option that has been in Excel for years, there is no means within the Excel or MS Query interface to create or edit Web queries. You are stuck with a selection of three stock quote queries, and that's as far as most users ever get with Web Query. That's fine if you want to know the price of IBM stock, but it doesn't help if you want to query your own corporate Domino database.
Running a Web query will open up a Web page and bring the information directly into an Excel worksheet. Further, like traditional Excel data queries, there's a Refresh function, so that users can quickly update their sheets with minimum fuss. So, if we could do Web queries for other URLs, it would solve our problem nicely.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- Advertisement --
Learn Notes and Domino 8 at your place and pace!
Learn Notes and Domino in your office and/or home! TLCC's highly acclaimed distance learning courses for users, developers, and admins will enhance your career and your resume.
The many included activities and demos will make you a pro! Expert instructor help is a click away.
Click here to try a FREE demo course!! |
-- Advertisement --
Mark your calendar for in-depth Lotus training, May 12-14, Boston
Join experts and peers May 12-14 in Boston for educational and networking events that deliver real-world Lotus training so you can increase productivity and efficiency in your company, advance your skills, and squeeze the most from your current environment. One registration gets you into THE VIEW's Admin2010 and Lotus Developer2010.
Register by April 10 to save $200. |
|
|
|
|
|
|
|
|
|
|
