How to import data from Microsoft Excel to a Lotus Notes form with LotusScript

Here is what I feel is the simplest and quickest LotusScript code to import data from Microsoft Excel to a Lotus Notes form. Make sure that the first row of your Microsoft Excel spreadsheet has the field names from the Lotus Notes form that you'd like to use for the import.

To implement this LotusScript code:

1. Insert the LotusScript below into an action button in a view, or make it available from the action menu, because it uses the NotesUIWorkspace class.
2. Write the code in an agent and run it from the Actions menu. This will import up to 256 columns by 65,536 rows from Excel to your Lotus Notes form.

Sub Initialize
  
Dim session As New NotesSession
Dim uiws As New NotesUIWorkspace
Dim form As NotesForm
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim item As NotesItem
Dim row As Integer

Dim xlFilename As String 
Dim xlsApp As Variant 
Dim xlsWorkBook As Variant 
Dim xlsSheet As Variant 
Dim rows As Long 
Dim cols As Integer 
Dim x As Integer
Dim itemName As String
Dim flag As Integer
Dim formAlias As String
Dim sortEval As String
Dim sortedList As Variant
Dim indexLo As Long
Dim indexHi As Long


On Error Goto ErrorHandler

Set db = session.CurrentDatabase

fn= uiws.Prompt(1, "Alert", 
"Make sure that the first row of your 
worksheet contains the EXACT 
Notes document field names from your form.")
 
'Get Excel file name 
fn =uiws.OpenFileDialog(False, 
"Select the Excel File to Import", 
"Excel files | *.xls", "c:My Documents")
xlFilename = Cstr(fn(0)) ' This is the 
name of the Excel file that will be imported
 
'Get list of form names
x=0

Print "Preparing List of Database Forms ..."

Forall f In db.Forms
Redim Preserve formlist(x)
formlist(x)=f.name
x=x+1 
Print "Preparing List of Database
 Forms ..."& Cstr(x)
End Forall

'Choose the form to use for import
formname = uiws.Prompt(4, 
"Choose Import Form", "Please select 
which form is to be used for this input.", 
formlist(0), formlist)
If formname= "" Then End

'Get the form object so that we can 
check field names 
Set form= db.GetForm(formname)

'If the form has an alias, use it to 
select the form
If Not Isempty(form.Aliases) Then
Forall a In form.Aliases
formname=a
End Forall 'a In form.Aliases
End If 'Not Isempty(form.Aliases) 

'Next we connect to Excel and open the file. 
Then start pulling over the records.
Print "Connecting to Excel..."

' Create the excel object
Set xlsApp = CreateObject("Excel.Application") 
 
'Open the file
Print "Opening the file : " & xlfilename
xlsApp.Workbooks.Open xlfilename
Set xlsWorkBook = xlsApp.ActiveWorkbook 
Set xlsSheet = xlsWorkBook.ActiveSheet 
xlsApp.Visible = False ' Do not 
show Excel to user
xlsSheet.Cells.SpecialCells(11).Activate
rows = xlsApp.ActiveWindow.ActiveCell.Row 
' Number of rows to process
cols = xlsApp.ActiveWindow.ActiveCell.Column 
' Number of columns to process

'Make sure we start at row 0 
row = 0 
Print "Starting import from Excel file..."

Do While True
row = row + 1 
 
'Check to make sure we did not run out of rows
If row= rows+1 Then Goto Done

'field definitions for notes come from first row 
(row, column)
If row=1 Then
For i=1 To cols
Redim Preserve fd(i)
fd(i)=xlsSheet.Cells( row, i ).Value
flag=0

Forall f In form.Fields
If Lcase(fd(i)) = Lcase(f) Then flag=1
End Forall 'f In form.Fields
 
If flag=1 Then 
Goto Skip 
End If ' flag=1
 
If Not flag=1 Then
msg="The field name "& fd(i) &" does not 
appear in the form you have chosen. 
Exiting import."
Msgbox msg
Goto ErrorHandler
End If 'flag=1
   
Skip:
Next 'For i=1 To cols
End If 'row=1
  
'Import each row into a new document
If Not row = 1 Then

'Create a new doc
Set doc = db.CreateDocument 
doc.Form = FormName

For i= 1 To cols
Set item = doc.ReplaceItemValue( fd(i), 
xlsSheet.Cells( row, i ).Value )
Next ' i= 1 To cols
 
'Save the new doc
Call doc.Save( True, True ) 
   
End If 'Not row = 1 Then
  
Print "Processing document number "& 
Cstr(row) & " of " & Cstr(rows) 

Loop 'Do while true
  
Done:
  
Print "Disconnecting from Excel..."
'Close the Excel file without saving 
(we made no changes)
xlsWorkbook.Close False
'Close Excel
xlsApp.Quit
'Free the memory that we'd used
Set xlsApp = Nothing 
  
'Clear the status line
Print " " 
  

ErrorHandler:
If Err = 184 Then
Msgbox "No file chosen. Exiting Import."
Print "No file chosen. Exiting Import."
Resume ErrorOut
End If ' err=184

If Err = 6 Then
Messagebox "Make sure that you 
do not have more than 65,536 rows 
of data to import." ,
MB_OK+MB_ICONINFORMATION,"Error! "
Print "Too many rows in Excel document. 
Exiting Import. Disconnecting from Excel..."
'Close the Excel file without saving
 (we made no changes)
xlsWorkbook.Close False
'Close Excel
xlsApp.Quit
'Free the memory that we'd used
Set xlsApp = Nothing 
Resume ErrorOut
End If ' err=184
  
If (Err) And (Not Err = 184) And 
(Not Err = 6) Then

Msgbox "Lotus Notes Error # " & Err &". 
Please contact your Notes administrator 
for help. Exiting Import."
Print "Error # "& Err

If Not xlsWorkbook Is Nothing Then
xlsWorkbook.Close False 
End If ' Not xlsWorkbook Is Nothing

If Not xlsApp Is Nothing Then
xlsApp.Quit False 
End If 'Not xlsApp Is Nothing
   
Resume ErrorOut

End If '(Err) And (Not Err = 184) 
And (Not Err = 6) 
  
ErrorOut:
  
End Sub

Do you have comments on this tip? Let us know.

This tip was submitted to the SearchDomino.com tip library by member Nithya K. Please let others know how useful it is via the rating scale below. Do you have a useful Lotus Notes, Domino, Workplace or WebSphere tip or code snippet to share? Submit it to our monthly tip contest and you could win a prize.