This and that...

This and that...

Importing data from Microsoft Excel to a Lotus Notes form

Notes ProgramingPosted by Martin Tue, February 17, 2009 18:34:17

Below is a sample shell of code to get a handle to an Excel file, and read values in the first column. You could adapt it to build an array of values in the first column, and set that value on a user profile document or hidden field on your form. You could then reference that profile or hidden field as the formula for the dialog list.

Use following code:

The next step is reading the rest of the row. After the dialog list value is selected, another script would need to run to look for the selected value in the Excel row and read the rest of the columns in the row.

The cleanest way to do this would probably be to pop up a separate form to let the user choose the SWO#, and then trigger the second script when the prompt form is closed.

You have to be careful about dealing with Excel objects this frequently though, because they are locked while the object is open. This may cause a problem if multiple users are trying to access the same Excel file on a shared drive.

In my opinion, it is a better idea to write a script to import all of the Excel rows into Lotus Notes (one document per row) on a scheduled basis, and use Lotus Notes formulas for the dialog list and subsequent row values. This will perform better and allow it to work for multiple users concurrently.