Import Lotus Notes Documents from Excel - a reference guide

A Sample fragment of lotus script code that can be used as a reference to get an idea about how to import documents from datas present in an excel sheet

Note: For more info on manipulation using excel objects, you can go through the following site, which holds the complete dom information on excel object:
http://msdn.microsoft.com/en-us/library/syyd7czh%28VS.80%29.aspx

Sub Click(Source As Button)
    'initiaition
    'handle errors in case of abrupt termination   
    On Error Goto errHandler
   
    'declare all the necessary variables and objects required for further manipulation
    Dim workspace As New NotesUIWorkspace
    Dim session As New NotesSession
    Dim currDB As NotesDatabase
    Dim importedDoc As NotesDocument
   
    'declaring variants
    Dim workbookPath As Variant
    Dim excel As Variant
    Dim xlSheet1 As Variant
    Dim xlSheet2 As Variant
    Dim xlSheet3 As Variant
    Dim xlSheet4 As Variant
   
    'declaring arrays
    Dim proposalDetails(0 To 6) As String
    Dim companyDetails(0 To 14) As String
    Dim mandateInformation(0 To 2) As String
    Dim proposalOutcome(0 To 8) As String
   
    'declaring strings
    Dim cellValue As String
    Dim validFlag As String
   
    'declaring integers
    Dim itr As Integer
    Dim rowCount As Integer
    Dim colCount As Integer
    Dim column As Integer
    Dim importedDocumentCount As Integer
    Dim userChoice As Integer
   
    'declaring booleans
    Dim continue As Boolean
   
    'initializing variables and arrays
    continue=True
    itr=0
    rowCount=6
    colCount=2
    importedDocumentCount=0
   
    proposalDetails(0)="CompleteCode"
    proposalDetails(1)="PTitle"
    proposalDetails(2)="PDateOfProposal"
    proposalDetails(3)="PEstimateEffort"
    proposalDetails(4)="PAnalyst"
    proposalDetails(5)="ShowPAnalyst"
    proposalDetails(6)="PBranch"
   
    companyDetails(0)="PCompanyName"
    companyDetails(1)="PLocation"
    companyDetails(2)="PCompanyType"
    companyDetails(3)="PPIC"
    companyDetails(4)="PAddressLine1"
    companyDetails(5)="PAddressLine2"
    companyDetails(6)="PCity"
    companyDetails(7)="PCountry"
    companyDetails(8)="PZipCode"
    companyDetails(9)="PTelephone"
    companyDetails(10)="PFax"
    companyDetails(11)="PEmail"
    companyDetails(12)="PContactPerson"
    companyDetails(13)="PPFC"
    companyDetails(14)="PDesignation"
   
    mandateInformation(0)="PMandateType"
    mandateInformation(1)="PMandateValue"
    mandateInformation(2)="PMandateDetails"
   
    proposalOutcome(0)="PProposalStatus"
    proposalOutcome(1)="PLost"
    proposalOutcome(2)="PWon"
    proposalOutcome(3)="PFinalClearancePersonTech"
    proposalOutcome(4)="PNameOfBidders"
    proposalOutcome(5)="PDraftStatus"
    proposalOutcome(6)="PKeyDiscussions"
    proposalOutcome(7)="PFinalClearancePersonComm"
    proposalOutcome(8)="PIMaCSStatus"
   
    userChoice=Messagebox("Please ensure that you have not left any empty rows in between the data in excel sheet" & Chr(13) & _
    "and make sure that the Excel file is created using the design template present in the profile document" & Chr(13) &_
    "Click yes to proceed",4+32,"Caution")
    If userChoice=7 Then Exit Sub
   
    'create the excel object reference
    Set excel=CreateObject("Excel.Application")
    'if the excel object is not set then inform the user and exit
    If excel Is Nothing Then
        Msgbox "Unable to create object reference for the Excel Application"  & Chr(13) &  _
        "Please make sure that the Excel appication is properly installed on your machine",,"Operation failed"
        Exit Sub
    End If
   
    'do not display the file and use it from the backend
    excel.visible=False   
   
    'bring up an open file dialog to let the user choose an excel file to be imported from
    workbookPath=workspace.OpenFileDialog(False)
   
    If Isempty(workbookPath) Then Exit Sub
   
    'open the excel file
    Call Excel.Application.Workbooks.Open(Trim(WorkBookPath(0)))
    Set xlSheet1=Excel.Application.Workbooks(1).WorkSheets(1)   
    Set xlSheet2=Excel.Application.Workbooks(1).WorkSheets(2)   
    Set xlSheet3=Excel.Application.Workbooks(1).WorkSheets(3)   
    Set xlSheet4=Excel.Application.Workbooks(1).WorkSheets(4)   
   
    'if any one of the sheet is found missing then inform the user that the file chosen does not follow the
    'template in the profile document and hence the process shall be aborted
    If (xlSheet1 Is Nothing) Or (xlSheet2 Is Nothing) Or (xlSheet3 Is Nothing) Or (xlSheet4 Is Nothing)  Then
        Msgbox "Please chose a valid Excel file that uses the design template provided in the configuration document",,"Invalid Excel File"
        Exit Sub
    End If
   
    'set the handle for the current database
    Set currDB=session.CurrentDatabase
   
    'loop through all the 4 sheets in the excel file and create a equallent document in the current database
    While continue    'start of main while
       
        'initialize the cell value to null
        cellValue=""   
        'initialize the valid flag to null
        validFlag=""
       
        'create a new document
        Set importedDoc=currDB.CreateDocument
       
        'create the document using the proposal form
        Call importedDoc.ReplaceItemValue(FORM_ITEM,PROPOSAL_FORM_NAME)
       
        'populate the values from the excel work sheets to the document
        'get values from work sheet 1
        For itr=0 To 6
            column=colCount+itr
            cellValue=xlSheet1.Cells(rowCount,column).value
            validFlag=ValidFlag+cellValue
            Call importedDoc.ReplaceItemValue(proposalDetails(itr),cellValue)
        Next
       
        'if there aren't any values in the row of interest in the first sheet then stop proceeding
        If Trim(validFlag)="" Then
            continue=False       
            Goto skipLoop
        End If
       
        'get values from worksheet 2
        For itr=0 To 14
            column=colCount+itr
            cellValue=xlSheet2.Cells(rowCount,column).value
            Call importedDoc.ReplaceItemValue(companyDetails(itr),cellValue)
        Next
       
        'get values from work sheet 3
        For itr=0 To 2
            column=colCount+itr
            cellValue=xlSheet3.Cells(rowCount,column).value
            Call importedDoc.ReplaceItemValue(mandateInformation(itr),cellValue)
        Next
       
        'get values from work sheet 4
        For itr=0 To 8
            column=colCount+itr
            cellValue=xlSheet4.Cells(rowCount,column).value
            Call importedDoc.ReplaceItemValue(proposalOutcome(itr),cellValue)
        Next
       
        'save the imported document
        Call importedDoc.Save(True,False)
       
        'increase the importedDocumentCount to be displayed in the process report at the end
        importedDocumentCount=importedDocumentCount+1
       
        'increase the row count to create new documents from the values present in the next row
        rowCount=rowCount+1
skipLoop:       
    Wend    'end of main while
   
    'refresh the view to help the user see the changes instantaneously
    Call workspace.ViewRefresh
   
    'inorder to prevent memory overload or memory heap
    'ensure that neither the excel document nor the excel application is open or running in the backend
'    Call excel.close
   
    Call excel.quit
    'Set excel=Nothing
   
    'inform the process report to the user
    Msgbox "A total of " & Cstr(importedDocumentCount) & " documents has been imported from the chosen excel Document",,"Process Report"   
    Exit Sub
   
    'imform the user about the errors that has resulted in abrupt termination
errHandler:
    'if excel is not installed in the machine then inform the user to install the same before proceeding
    If Err=208 Then
        Msgbox "Microsoft Excel is not installed in your machine ",,"Aborting process"
        Exit Sub
       
    'if the user has chosen a file other that an excel file inform the user about what type of file he must choose   
    Elseif Err=213 Then
        Msgbox "Please choose a valid Microsoft Excel file developed based on the template provided in the profile document",,"Aborting process"
        Exit Sub
    End If
   
    'populate the error message in the status bar
    Print "Error : ***" & Error & "*** has occured on line ***" & Cstr(Erl) & "*** with error number ***" &  _
    Cstr(Err) & "***"
   
    'inform the user about the where about the information on the error that has occured
    Msgbox "An error has occured while attempting to import details from the excel document" & Chr(13) &  _
    "The technical details of the error are as printed in the status bar" & Chr(13) &  _
    "Please inform your administrator to contact the support team immediately to fix the issue"
    Exit Sub
End Sub