How to import data from Microsoft Excel to a Lotus Notes form with LotusScript
SearchDomino.com member Nithya K. explains how to quickly and easily import data from a Microsoft Excel spreadsheet to a Lotus Notes form using 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:
- 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.
- 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.