Exporting and importing to and from Excel

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.

Download this free guide

Download: IT Certifications 101

Inside this exclusive essential guide, our independent experts break down which IT certifications are worth your time and effort, and how to get started obtaining them to further your career— including specific certifications that any cloud or desktop pro should seriously consider.

Start Download

• Corporate E-mail Address:

  You forgot to provide an Email Address.

  This email address doesn’t appear to be valid.

  This email address is already registered. Please login.

  You have exceeded the maximum character limit.

  Please provide a Corporate E-mail Address.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

The solution to all of these problems is to use Excel. Scripts have been written to use Excel for supporting this functionality.
 

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.