@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
ID: 12241264
You have to import the excel data into a lotus notes view.. and then use DBColumn on this view..


Expert Comment

ID: 12244233

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

ID: 12244251
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

ID: 12253006
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Encrypted Mail message in a NSF 10 30
Updating Lotus Notes workspace icons with new server 1 130
Lotus notes - Follow up notes mail missing 15 73
Lotus Notes Calendar Help 6 100
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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