Q

Importing data from Microsoft Excel to a Lotus Notes form

SearchDomino.com expert Brad Balassaitis explains how to use LotusScript to dynamically import data from an Excel spreadsheet to a Lotus Notes form with a dialog list formula.

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.

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.

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

This was first published in January 2006

Dig deeper on Lotus Domino Designer

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchWinIT

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchEnterpriseLinux

SearchVirtualDataCentre.co.UK

Close