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