Skip to main content
This forum is closed to new posts and responses. New discussions are now taking place in the IBM Developer Answers forum.
Information Mgmt
New to Lotus
How to buy
Live demos
Technical library
Forums & community
Java™ technology
Open source
SOA and Web services
Web development
My developerWorks
About dW
Submit content

developerWorks  >  Lotus  >  Forums & community  >  Notes/Domino 6 and 7 Forum

Notes/Domino 6 and 7 Forum



PreviousPrevious NextNext

RE: Import contacts from Excel sheet to Notes 6.5
Rohit Wali 13.Feb.12 12:24 AM a Web browser
Notes Client 6.0.3 Windows XP

Hi Pablo
Here is code to import from excel,provided you have to keep field name same as column name..
Sub Click(Source As Button)
Dim astrFields As Variant
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
Dim fn As Variant
Dim msg As String
Dim i As Long
Dim formname As String
On Error Goto ErrorHandler
Set db = session.CurrentDatabase
fn= uiws.Prompt(1, "Reminder- Excel Worksheet Setup", "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
Print "Preparing List of Database Forms ..."
Redim formlist(Ubound(db.Forms))
For x = 0 To Ubound(db.Forms)
Print "Preparing List of Database Forms ..."& Cstr(x)
'Sort the form names for the dialog box
indexLo= Lbound(formlist)
indexHi= Ubound(formlist)
Call QuickSort(formlist , indexLo, indexHi)
'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 Exit Sub
'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 formname = form.Aliases(Ubound(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
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
astrFields = form.Fields
Redim fd(1 To cols) As String
For i=1 To cols
'the replace function used here removes spaces from the field definitions in the first row
fd(i) = xlsSheet.Cells( row, i ).Value
If Len(fd(i)) Then
fd(i)= Replace(fd(i), " ", "")
If Isnull(Arraygetindex(astrFields, fd(i))) Then
msg="The field name "& fd(i) &" does not appear in the form you have chosen."
Goto Done
End If
End If 'flag=1
End If
Next 'For i=1 To cols
Else 'row isn't = 1
'Import each row into a new document
'Create a new doc
Set doc = db.CreateDocument
doc.Form = FormName
For i= 1 To cols
If Len(fd(i)) Then _
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
On Error Resume Next 'protect against infinite error handing loops
Print "Disconnecting from Excel..."
If Not xlsWorkbook Is Nothing Then
xlsWorkbook.Close False
End If ' Not xlsWorkbook Is Nothing
If Not xlsApp Is Nothing Then
xlsApp.DisplayAlerts = False
Set xlsApp = Nothing
End If 'Not xlsApp Is Nothing
'Clear the status line
Exit Sub
Select Case Err
Case 184
Msgbox "No file chosen. Exiting Import."
Print "No file chosen. Exiting Import."
Resume Done
Case 6
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..."
Resume Done
Case Else
Msgbox "Lotus Notes Error # " & Err & ". Please contact your Notes administrator for help. Exiting Import."
Print "Error # "& Err & " on line " & Erl & ": " & Error$
Resume Done
End Select
End SubError$
Resume Done
End Select
End Sub

Import contacts from Excel sheet to... (Pablo Perez 30.Apr.09)
. . RE: Import contacts from Excel shee... (Alex Elliott 1.May.09)
. . RE: Import contacts from Excel shee... (Mudit Pandit 30.Apr.09)
. . RE: Import contacts from Excel shee... (Melissa L Snell... 30.Apr.09)
. . RE: Import contacts from Excel shee... (Pablo Perez 30.Apr.09)
. . . . RE: Import contacts from Excel shee... (Melissa L Snell... 30.Apr.09)
. . RE: Import contacts from Excel shee... (Rohit Wali 13.Feb.12)

Document Options

  Document options
Print this pagePrint this page

Search this forum

Forum views and search

  Forum views and search
Date (threaded)
Date (flat)
With excerpt
Advanced search

Member Tools

RSS Feeds

 RSS feedsRSS
All forum posts RSS
All main topics RSS
More Lotus RSS feeds


Forum use and etiquette
Native Notes Access
Web site Feedback

Lotus Support

 Lotus Support
IBM Support Portal - Lotus software
Lotus Support documents
Lotus support by product
Lotus support downloads
Lotus support RSS feeds


IBM Composite Applications
IBM Mashup Center
IBM Connections
IBM Connections Cloud Developers
IBM Docs
IBM Forms
IBM Mobile Connect
IBM Sametime
IBM SmartCloud for Social Business
IBM Web Experience Factory
Lotus Domino
Lotus Domino Designer
Lotus Expeditor
Lotus Foundations
Lotus iNotes
Lotus Instructor Community Courseware
Lotus Notes
Lotus Notes & Domino Application Development
Lotus Notes Traveler
Lotus Protector
Lotus Quickr
Lotus Symphony
IBM Web Content Manager
WebSphere Portal

Lotus Forums

 Lotus Forums
Notes/Domino 9.0
Notes/Domino 8.5 + Traveler
Notes/Domino XPages development forum
Notes/Domino 8
Notes/Domino 6 and 7
IBM Connections
IBM Mobile Connect
IBM Sametime
IBM SmartCloud Notes
Lotus Enterprise Integration
Lotus Protector
Lotus Quickr
Lotus SmartSuite