|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 --
SECURTRAC - MONITOR AND CONTROL YOUR DOMINO ENVIRONMENT
When it comes to your business, how do you ensure compliance with SOX, HIPAA or other industry driven regulations? Use SecurTrac to monitor and audit the life cycle of all objects in your Domino environment.
- Database Monitor
- Mail Monitor
- Domino Directory Monitor
- Notes.ini File Monitor
- Intrusion Detection Monitor
Click here for details and a free evaluation copy. |
-- Advertisement --
Good Practices... Better Practices... Teamstudio.
Implementing good practices in your Notes environment doesn't have to be complicated.
Teamstudio provides software and services for efficient Notes development and simple, secure administrator control. Our new website also provides users with a library of resources to help, including:
- Ready-to-implement policies for good practice development and deployment in Lotus Notes
- On-demand webinars on topics ranging from tips for better coding, to securing your applications, managing agents, and streamlining your application deployment process
- Free utilities for download to help you more more efficiently tackle several specific tasks in Notes development
Visit our library of white papers to help you take on difficult issues in your Notes environment.
Drop by our new website and take a look! |
|
|
|
|
|
|
|
|
|
|
