@DbColumn lookup from embedded Excel Workbook

I would like create a Dialog List field (field name: DktDest).  The field choices will be  obtained by doing an @DbColumn lookup using data from a form-embedded Excell Workbook.  The embedded Workbook is named "Datakit Worksheet", the worksheet is titled "Alpha Dest" the column is "Dkt Dest" (Column A).  In my efforts to make this work, I tried referencing a "named range" (this range is named "DktDestProp") in the embedded spreadsheet, but was still unsucessful!

Who is Participating?

Improve company productivity with a Business Account.Sign Up

HemanthaKumarConnect With a Mentor Commented:
You have to import the excel data into a lotus notes view.. and then use DBColumn on this view..


Yes, the efficient way to accomplish this is to import the excel document into a view.

Create a form named Import. Create all the fields you want to import from the excel document in this form.

Only keep the relevant data in the excel that you want to import. Lets say you are importing two columns from the view.
ColumnA , ColumnB. (These are the titles in the 1st row in the excel document)

Create two text fields in the form called ColumnA and ColumnB in the import form.
Now go to the excel document and do a save as and then save it as a .wk4 file.

Now create a view in Lotus Notes which has Select FORM = "Import".  Create two columns ColumnA, ColumnB in the view.

Then go to the view, click on file , import.

choose file types as Lotus 1-2-3 and then select the .wk4 file and click on import.
You should get a window called... 123Worksheet Import..
Select import as Main document.
Using form as Import form.
Column format : using view
Click ok.

Once the import is done.. You can view the data in the view.
Now you can use the @DBcolumn based on this view...

If you want to do this using Lotus Script.. Check this question...


Good Luck
The Lotus script in the link above basically shows you how to read data from an excel document and import into a form.

If this is a one time activity and the document has fixed values, the above two suggestions are your best bet. If the contents of the attached document may vary, the above method wont help. In that case, use this approach.

(1) Detach the embedded file to a temproary location
(2) Read the range using OLE Automation and populate your list
(3) Or in lieu of step 2, connect to the excel sheet using ODBC and read the data dynamically. With this method, you can even use DBLookup/DBColumn on the excel sheet.

Read the topic "Accessing external databases through LS:DO using @functions" in the designer help for more info on this.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.