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