' the replace function used here removes spaces from the field definitions in the first row
fd ( i )= Replace ( xlsSheet.Cells ( row, i ).Value, " ", "")
j'ai une erreur de compilation sur cette ligne
merci christian

Nice straightforward example. Dim rows As Long instead of Integer to import the maximum rows excel can contain (65536)

Thank you Jared Jones.

The form list in the dialog box is now sorted (using the QuickSort function). Once the form is chosen, if the form has an alias, we use that as the form name.

nice work.
i would recommend to use aliases if existing instead of the 'full from name'
just replace the form-colletion with this:
-----------
Forall f In db.Forms
Redim Preserve formlist(x)
If Isarray(f.Aliases) Then
If Trim(f.aliases(Ubound(f.aliases)))="" Then
formlist(x)=f.name
Else
formlist(x)=f.name + " | " + f.aliases(Ubound(f.aliases))
End If
Else
formlist(x)=f.name
End If
x=x+1
Print "Preparing List of Database Forms ..."& Cstr(x)
End Forall
------------
and after the form - prompt replace the 'if fromname="" then end' by this
------------
If formname= "" Then
End
Elseif Instr(1, formname, "|")>1 Then
formname = Trim(Strright(formname,"|"))
End If

Saved me a lot of time. I thought I'd pay it back with some improvements... namely:
- If you already know the size of the array, redim it once before the loop instead of incrementing it within the loop.
- To get the last element of an array, get array(ubound(array)) instead of looping through the whole thing using forall
- Consolidate your code by putting the code cleanup stuff after your "Done" label, and all error checking can do Resume Done
- instead of doing LCase() comparisons, you can use Option Compare NoCase
- ALWAYS use Option Declare. It's just a good habit.
- I had an issue where there were blank columns in the middle. I changed the code to skip them.
- I also changed it so fields that don't exist on the form give an Ok-Cancel box, so users can click "ok" and continue.
Here it is.....................................................
Option Public
Option Declare
Option Compare Nocase
%INCLUDE "LSCONST.LSS"
Sub Initialize
'from http://www.openntf.org/Projects/codebin/codebin.nsf/CodeSearch/DCD5A132F75581698625726700715FCA
'Version 1.2, plus a few improvements
'code from David Moore david.james.moore@gmail.com
'This code works with Win 98, Win2000, WinXP
'Insert the code into an action button in a view or make it available
'from the action menu because it uses NotesUIWorkspace.
'will import from Excel up to 256 columns by 65,536 rows
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)
formlist(x)=db.Forms(x).name
Print "Preparing List of Database Forms ..."& Cstr(x)
Next
'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
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
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."
If Msgbox(msg, MB_OKCANCEL + MB_ICONEXCLAMATION + MB_DEFBUTTON2) <> 1 Then
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
Done:
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
xlsApp.Quit
Set xlsApp = Nothing
End If 'Not xlsApp Is Nothing
'Clear the status line
Print
Exit Sub
ErrorHandler:
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 Sub
Function QuickSort( anArray As Variant, indexLo As Long, indexHi As Long) As Variant
Dim lo As Long
Dim hi As Long
Dim midValue As String
Dim tmpValue As String
lo = indexLo
hi = indexHi
If ( indexHi > indexLo) Then
'get the middle element
midValue = anArray( (indexLo + indexHi) /2)
While ( lo <= hi )
'find first element greater than middle
While (lo < indexHi) And (anArray(lo) < midValue )
lo = lo+1
Wend
'find first element smaller than middle
While ( hi > indexLo ) And ( anArray(hi) > midValue )
hi = hi - 1
Wend
'if the indexes have not crossed, swap
If ( lo <= hi ) Then
tmpValue = anArray(lo)
anArray(lo) = anArray(hi)
anArray(hi) = tmpValue
lo = lo+1
hi = hi -1
End If
Wend
' If the right index has not reached the left side of array, sort it again
If( indexLo < hi ) Then
Call QuickSort( anArray, indexLo, hi )
End If
'If the left index has not reached the right side of array, sort it again
If( lo < indexHi ) Then
Call QuickSort( anArray, lo, indexHi )
End If
End If
QuickSort = anArray
End Function

Implementing the code was very easy and it spared me a lot of time, so thank you for sharing!
Regards,
Martin

This is really nice code.
But opening file dialog to select a file to import data will happen only through client.
What about if we can have same file dialog for the user in web interface.
Any help here.Please

Sameer,
Your best bet is to split this function up into two functions.
The first one (named something like "PromptAndImportExcel" would contain everything before the comment line "'Next we connect to Excel and open the file. Then start pulling over the records.", and would then call the second function, passing all the parameters needed.
The second (named something like "ImportExcel" would contain everything after that comment line. This would be the meat of the function that does the real work.
Don't use global variables. Make sure all variables that the second function needs are parameters.
Once that's working, you can write a web form that collects the information that the second one needs including a file upload control and a WebQueryOpen agent (assuming you're not using XPages) that would detach the submitted Excel file to a temp directory and calls the second function directly.

Thanks for the suggestion. I did it and it's working now.
I have got another problem.
When importing starts, The Excel file I'm having has many spreadsheets in which first sheet is master and rest i will update the fields according to the Id Column on the first sheet, and like wise I'm doing for all the sheets I'm having.
But if I have file of more than 100 records then it gives me this Error.
Error:"maximum execution time exceeded" as lot of processing is going on that's why this is happening.
However I checked server documents for agent timing which is set to 10 mins. In my case the above error is coming only after 2 min's.
So please suggest a way out.

Sameer: The Agent Manager only handles scheduled agents. Web agents, or NotesAgent.RunOnServer calls are not handled by the agent manager.
You want the fields within server document -> Internet Protocols -> HTTP -> Timeouts... I'm sorry I'm not 100% sure, but I _think_ you want to change the CGI Timeouts field. (on the left side, not the R5 Timeouts section of fields on the right).

Another way out is to accept the form and say "thanks, you'll receive a confirmation email when I'm done", then process it using a new/modified agent without making the user wait.

Thanks folks, it saved me a lot of work.

This is useful for new imports.
Is there any code to update existing documents that need specific fields that are empty in Notes that need to be populated with Excel. The first column will be the search key.
Thank you

Jaz - check out the Data Moving Animal project on OpenNTF. It has the ability to update existing document with new fields, and much more --> http://www.openntf.org/internal/home.nsf/project.xsp?action=openDocument&documentId=7C96B84B9E5CF50A8625752F003D4BA7

Hi Friends,
Any one of you help me to import particular column from excel ?

Check out the line
....
For i=1 To cols
'the replace function used here removes spaces from the field definitions in the first row
...
i is the column number (1=A / 2=B ...)
The easiest, if you need to import only column F change it to
For i = 6 to 6
Otherwhise change the For-Loop to something like this:
...
dim impcol(4) as string
dim ic as integer
impcol = split("2,6,22,27,32",",") '=cols B/F/V/AA/AF
for ic = 0 to 4
i = impcol(cint(ic))
....

View has documents that have two different forms. Thought I could hardcode the fields instead of pick the form. Still only one form updates but not the other.

The forms shouldn't be a problem, because the script takes the column values. What might cause the problem it that the other form is a response document. Did you try to answer the response question with 'yes'?
Since Notes8 I recommend using the built-in function 'copy as table'. It's limmited in the amount of lines, but it should be enough i most cases

sorry mixed this with my export-script, which you can find here: http://www.yel.ch/yelhome.nsf/vwwr/2952F16FD862E528C1256B90007A01C7?Opendocument
The site is in german, but the intructins in the zip are english...

Login to add your comment