@DbColumn lookup from embedded Excel Workbook

Posted on 2004-10-06
Last Modified: 2013-12-18
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!

Question by:rognyn
  • 2
LVL 24

Accepted Solution

HemanthaKumar earned 500 total points
Comment Utility
You have to import the excel data into a lotus notes view.. and then use DBColumn on this view..


Expert Comment

Comment Utility

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

Expert Comment

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

LVL 19

Expert Comment

Comment Utility
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.

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
This is an old article, please see an updated version of this article, located here:
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now