Halloo I have a big problem because I want import Excel file into database, and nest mapping concrete column for concrete fields in document from my database, and when I later open document I want see contents from excel file, in sort description I want bulk update my data base.
Thanks for help.
Subject: How bulk update database from excel file
Maciej,
unfortunately I do not understand your problem completely.
If you are interested in some sample Lotus Script code for importing Excel-sheets into a Lotus Notes database with a variable field mapping I can help you.
What do you mean with
“in sort description I want bulk update my data base”?
Regards,
Ullrich
Subject: RE: How bulk update database from excel file
Sorry it should be “Short”. Yes, If you able to support me some example how I can import excel file into form it will be great.Thanks for help.
Subject: RE: How bulk update database from excel file
OK… Now I understand you 
Following you can find some sample code for importing data of an excel-sheet into a lotus notes database.
Please keep in mind that
that the name of the form has to be changed
the name of the view has to be changed as well
you need a key value that identifies each recordset
this value has to be the first column in the excel file
your view has to contain one sorted column which shows this value
only new recordsets are imported (i.e. the key value can not be found in the view)
You can change the code so that modified recordsets are imported as well. But this is only important if you want to repeat the import every now and then. If you are only importing once and the lotus notes database does not contain the data which is in the excel sheet this is not necessary.
On Error Goto errmsg
Dim count As double
Dim counter As double
count = 0
counter = 0
Dim session As New NotesSession
Set dbCurr = session.CurrentDatabase
Dim xlFilename As String
xlFilename = Inputbox ( "Please enter the path to the excel-file!" , "Path" , "" )
If xlFilename = "" Then
Exit Sub
End If
Dim One As String
Dim row As Integer
Dim written As Integer
'// We connect to Excel and open the file. Then start pulling over the records.
Dim Excel As Variant
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Print "Connecting to Excel..."
Set Excel = CreateObject( "Excel.Application" )
Excel.Visible = False '// Don't display the Excel window
Print "Opening " & xlFilename & "..."
Excel.Workbooks.Open xlFilename '// Open the Excel file
Set xlWorkbook = Excel.ActiveWorkbook
Set xlSheet = xlWorkbook.ActiveSheet
Print "Starting import from Excel file..."
Dim NumberOfRows As Long
NumberOfRows = xlSheet.UsedRange.Rows.Count
count = xlSheet.UsedRange.Rows.Count
Dim RowPointer As Long
Dim i As Integer
Dim view As NotesView
Set view = dbCurr.GetView ( "(ImportView)" )
If view Is Nothing Then
Exit Sub
End If
Call view.Refresh
Dim antwort As Integer
antwort = Msgbox ( "You are going to import " & NumberOfRows & " recordsets into this database. Continue?" , 4 , "Start import?" )
If antwort <> 6 Then
Exit Sub
End If
Dim schluessel As String
Dim doc As NotesDocument
Dim summe As Double
For RowPointer = 1 To NumberOfRows
schluessel =xlSheet.Cells( RowPointer , 1 ).Value )
Set doc = view.GetDocumentByKey ( schluessel , True )
If doc Is Nothing Then
Set doc = dbCurr.CreateDocument
doc.Form = "ImportForm"
doc.Field1 = xlSheet.Cells( RowPointer , 1 ).Value
doc.Field2 = xlSheet.Cells( RowPointer , 2 ).Value
doc.Field3 = xlSheet.Cells( RowPointer , 3 ).Value
counter = counter + 1
Call doc.Save ( True , True )
End If
print RowPointer & " recordsets processed, " & counter & " documents created!"
Next
Print "Disconnecting from Excel..."
xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
Excel.Quit '// Close Excel
Set Excel = Nothing '// Free the memory that we'd used
Print " " '// Clear the status line
Msgbox RowPointer & " recordsets processed, " & counter & " documents created!"
Dim ws As New NotesUIWorkspace
Call ws.ViewRefresh
Exit Sub
errmsg:
Msgbox ( "Error in ExcelImport: " & Error & " in line " & Erl )
Msgbox "Line in excel-file: " & RowPointer
Print " " '// Clear the status line
Exit Sub
Hope this helps!
Subject: RE: How bulk update database from excel file
Very very much Thank you, it is super especially existing check option.And I have one more question.
Have you any similar code for exporting to excel file.
One more thanks.
Subject: RE: How bulk update database from excel file
Of course I have…
The following code exports all documents which are selected in a view to excel. Please keep in mind that you have to modify the field list () and the dimension of element () and () before using the code.
Hope this helps!
On Error Goto errlbl
Dim count As Double
count = 0
Dim i As Double
i = 1
Dim j As Double
j = 1
Dim lngRow As Double
lngRow = 1
Dim lngCol As Double
lngCol = 1
Dim varApp As Variant
Dim session As New NotesSession
Dim dbCurr As NotesDatabase
Set dbCurr = session.CurrentDatabase
Dim coll As NotesDocumentCollection
Set coll = dbCurr.UnprocessedDocuments
If coll.Count <= 0 Then
Msgbox "No documents found!"
Exit Sub
End If
If coll.Count > 65535 Then
Msgbox ( "Too many documents found!" )
Exit Sub
End If
Dim antwort As Integer
antwort = Msgbox ( "You are going to export " & coll.Count & " documents to excel. Continue?" , 4 , "Start export?" )
If antwort <> 6 Then
Exit Sub
End If
Set varApp = CreateObject("Excel.Application")
If varApp Is Nothing Then
Msgbox "An excel-object could not be created." , 16 , "Error"
Exit Sub
End If
varApp.Workbooks.Add
varApp.ReferenceStyle = 2
Set varSheet = varApp.Workbooks(1).Worksheets(1)
varSheet.Name = "Titel of Excel sheet"
'*
Dim liste (0 To 2) As String
liste ( 1 ) = "Fieldname 1"
liste ( 2 ) = "Fieldname 2"
Dim element As Integer
'**
For element = 1 To 2
lngRow = 1
lngCol = i
varSheet.Cells(lngRow,lngCol).Value = liste ( element )
i = i +1
Next
i = 2
Dim feldname As String
Dim feldwert As String
Dim docCurr As NotesDocument
Set docCurr = coll.GetFirstDocument
While Not docCurr Is Nothing
'***
For element = 1 To 2
lngRow = i
lngCol = j
feldname = liste ( element )
feldwert = docCurr.GetItemValue ( feldname ) ( 0 )
varSheet.Cells(lngRow,lngCol).Value = feldwert
j = j +1
Next
count = count + 1
Print count & " documents = " Cint ( count / coll.Count * 100 ) & " % processed"
Set docCurr = coll.GetNextDocument ( docCurr )
i = i +1
j = 1
Wend
Ende:
varApp.Visible = True
varApp.Rows("1:1").Select
varApp.Selection.Font.Bold = True
varApp.Selection.Font.Underline = True
varApp.Selection.Columns.AutoFit
varApp.Range("A2").Select
varApp.ActiveWindow.FreezePanes = True
varApp.Cells.Select
varApp.Cells.EntireColumn.AutoFit
varApp.Range("A2").Select
Exit Sub
errlbl:
Print ( "Error in ExportData: " & Error & " in line " & Erl & ", fieldname: " & liste ( element ) )
Exit Sub