Home > Ask the Domino Experts > Domino Designer Questions & Answers > Importing data from Microsoft Excel to a Lotus Notes form
Ask The Domino Expert: Questions & Answers
EMAIL THIS

Importing data from Microsoft Excel to a Lotus Notes form

Brad Balassaitis EXPERT RESPONSE FROM: Brad Balassaitis

Pose a Question
Other Domino Categories
Meet all Domino Experts
Become an Expert for this site


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   


>
QUESTION POSED ON: 19 January 2006

I would like to import data from fields in a Microsoft Excel spreadsheet into a Lotus Notes form. I would like the first field of my form to be a dialog list. This dialog list will be derived from each of the fields in the first column of my Excel spreadsheet.

The dialog list will allow me to select which row of data I wish to import from my spreadsheet to the Lotus Notes form. The data from the second field (column) of the Excel spreadsheet row is going to field two of the Lotus Notes form, and data from the third field of the row chosen is going to field three of the Lotus Notes form -- and so on.

SWO#  Priority Est Hrs

123         1                    1
124         2                    4
125         1                    3

In the above example, my dropdown list would consist of 123,124 and 125. If I selected 124, field two of my Lotus Notes form would have the value of two and field three of my Lotus Notes form would have the value of four.


>
EXPERT RESPONSE

I am not currently aware of a way to do this dynamically, with a dialog list formula. However, with some LotusScript, you could make it work.

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.

'Create the COM object for 
the Excel file to import and hide it
Set varXLFile = CreateObject("Excel.Application")
varXLFile.Visible = False
Set varXLWorkbook = Nothing
        
'Prompt for the name of the file and try to open it
strXLFilename = "EXCEL_FILE_NAME.xls"
varXLFile.Workbooks.Open strXLFilename
        
Set varXLWorkbook = varXLFile.ActiveWorkbook
Set varXLSheet = varXLWorkbook.ActiveSheet
               
'Loop through all valid rows and call the 
function to read the values into lists
lngRow = 2
While Not (varXLSheet.Cells(lngRow, 1).Value = "")
strValue = varXLSheet.Cells(lngRow, 1).Value
lngRow = lngRow +1
Wend
               
'Close the Excel objects
varXLWorkbook.Close False
varXLFile.Quit
Set varXLFile = Nothing

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.

<Do you have comments on this Ask the Expert Q&A? Let us know.


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   


RELATED CONTENT
LotusScript
Display a custom message box using a LotusScript-generated button
Debug Lotus Notes documents using extracted data
Extracting attachments from a Lotus Notes rich-text field
Programmatically replace the design of Lotus Notes databases
Reading a binary field in an Oracle database with LotusScript
LotusScript equivalent of @Picklist for Lotus Notes
Launch large attachments within an email from a Notes database
How to find files on a hard drive or mapped network with LotusScript
Update the ACL from the Roles view with LotusScript
LotusScript agent moves tagged spam email to junk mail folder

Domino Designer
Lotus Notes access error: 'database is not opened yet'
Stop response documents from showing in a Lotus Notes form
Set a value in a field existing in another Lotus Notes database
Create an automatic scheduled view export in Excel
Display Lotus Notes fields as separate entries in one column
Creating custom Lotus Notes Domino login forms
Using LotusScript to retrieve names of fields on a Lotus Notes form
Error creating product object
Is LotusScript needed to validate fields on form?
Can the @Weekday function be manipulated for 14 days?

Lotus Domino Designer
Stop response documents from showing in a Lotus Notes form
Set a value in a field existing in another Lotus Notes database
Display Lotus Notes fields as separate entries in one column
An introduction to Lotus Domino Designer
Modifying LotusScript code for date and time handling
Creating custom Lotus Notes Domino login forms
Using LotusScript to retrieve names of fields on a Lotus Notes form
Top 10 Lotus Notes Domino programming and development tips of 2007
A bevy of Notes/Domino development tips
A smorgasbord of Notes/Domino development tips

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary



Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.
Browse our Expert Advice

HomeNewsTopicsITKnowledge ExchangeTipsAsk the ExpertsMultimediaWhite PapersDomino IT Downloads
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Reprints  |  Site Map




All Rights Reserved, Copyright 1999 - 2008, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts