I find that the normal Notes export functions often are not adequate. Long text strings and formatted text often do not work. The same hold true for importing data.
The solution to all of these problems is to use Excel. Scripts have been written to use Excel for supporting this functionality.
Code
Some limitations of the export to 1-2-3 can be overcome by supplying a WK4 extension, but the 254-character cell limitation still exists, I believe.
There is a capability provided by Microsoft to add Excel option to the import/export options in Notes, but IBM does not support this "feature."
See Technote #164273 on the IBM Notes Technical Support Site.
D. Hasa has published a free script (ExportToExcel-Script v2) that can be used to export documents to Excel. Go to http://www-10.lotus.com/ldd/sandbox.nsf/85d5b6903071400e8525676d0079b3ae/e2a31b9d2a77f41685256b260050b64d?OpenDocument
The following is an example of a script to import an XLS file into Notes from a view:
Sub Click(Source As Button)
'The action button is located on a view named Import1
Dim ws As New NotesUIWorkspace
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim temp1 As String
Dim temp2 As String
Dim temp3 As String
Dim temp4 As String
Dim temp5 As String
Dim temp6 As String
Dim xlApp As Variant
Dim xlSheet As Variant
Dim cursor As Integer
Dim recordcheck As String
On Error Goto ERRORLABEL
Dim FileName As String
Dim DefaultFileName As String
DefaultFileName="c:tempsheet.xls"
NamePrompt$="Enter the complete Path and File Name of the Excel
file to be imported:" &Chr(13)
FileName=Inputbox(NamePrompt$,"Import File Name
Specification",DefaultFileName,100,100)
If FileName="" Then Goto SubEnd
'Create an Excel object for the spreadsheet
'It may be necessary to have the proper MS Office DLLs installed
on the PC running this application
'That is, the person must be able to open the XLS file with Excel
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Workbooks.Open Filename
'File not found will return ERR=213 and the routine will be ended
With xlApp.workbooks.Add
'Not sure what this line is for, unless it's to be sure that at
least one workbook is present in the XLS file
End With
'Stop
Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
recordcheck="x"
r=1 ' Row counter - first row contains fieldnames from the database;
'these fieldnames are not needed as long as the spreadsheet follows a specified format
While recordcheck <>"0" And recordcheck <> "" And r<100
'Recordcheck = "0" when there is no value in the first cell of the row,
if integer, or "", if string
'Important - Set r to a value that will stop the import routine without
missing any data, if the other criteria do not
'For this example, six columns are imported
cursor=0
r=r+1
temp1=xlSheet.Cells(r,1).value
recordcheck=Cstr(temp1)
If recordcheck="0" Then Goto Finished ' Avoid generating empty record
temp2=xlSheet.Cells(r,2).value
temp3=xlSheet.Cells(r,3).value
temp4=xlSheet.Cells(r,4).value
temp5=xlSheet.Cells(r,5).value
temp6=xlSheet.Cells(r,6).value
'The above lines lines could be combined with those below, but keep
separate for ease in debugging until ready to finalize the code
Set doc = New NotesDocument(db)
doc.Form = "frmName"
doc.Field1 = temp1
doc.Field2 = temp2
doc.Field3= temp3
doc.Field4 = temp4
doc.field5 = temp5
doc.field6 = temp6
Call Doc.Save(True, False)
Finished:
Wend
Goto SubClose
ERRORLABEL:
'Msgbox "An error was encountered."
'Print "Error: " Error(), " Err:" Err(), " Erl:" Erl()
If Err=213 Then
Messagebox Filename & " was not found. Verify Path and Filename, then try the Import again."
Resume SubEnd
Else
Messagebox "Error" & Str(Err) & ": " & Error$
End If
Resume Next
SubClose:
xlApp.activeworkbook.close
xlApp.Quit
Set xlapp = Nothing
Set view=db.getview("import1")
Call ws.viewrefresh
SubEnd:
End Sub
Hopefully, IBM will add Excel Import/Export options by default, since 1-2-3 is no longer being upgraded, and current version have serious limitations.
