Lotus Notes File - To import Or update the data from Excel file to Lotus Notes View

Sub Initialize
On Error Goto skips

Dim session As New NotesSession
Dim ws As New NotesUIWorkspace

FileName:
Dim xlFilename As Variant
xlFilename = ws.OpenFileDialog( False, "Select the import file",, "c:\")
'''xlFilename = Inputbox$("Please enter the complete Drive\Path\FileName.XLS for the file you wish to Import.", "Enter Import File Information", "C:\Documents and Settings\Administrator\Desktop\ProjectRecordforUpdation112.xls") '// This is the name of the Excel file that will be imported
If Isempty(xlFilename) Then
Print "No file selected, exiting."
Exit Sub
End If

Dim view As NotesView
Dim doc As NotesDocument
Dim Temp As String
Dim CorrectName As String
Dim CorrectNumber As String
Dim CorrectDate As String
Dim Converted As String
Dim CorrectProjectName As String
temp1=Evaluate("@V3username")
temp2=Evaluate("@Now([ServerTime])")
Set db = session.CurrentDatabase
Set logdoc= db.createdocument
logdoc.form="BulkUpdateLog"
Call logdoc.computewithform(True, False)
Call logdoc.save(True, False)
Set logitem= New notesrichtextitem(logdoc, "RTlog")

Dim row As Integer
Dim written As Integer
Dim TempNumber As String
Dim number As Integer
number = 0
numberofrow:
TempNumber = Inputbox$("Please enter the number of rows in spreadsheet", "Enter the Number of Rows in the Spreadsheet") '//Start import of Excel file
If TempNumber = "0" Then
Msgbox "number of rows in spreadsheet should be more than Zero"
Goto numberofrow
End If
If TempNumber = "" Then
Exit Sub
End If
number = Cint(TempNumber)
Dim Excel As Variant
Dim test As String
''''test = Cstr(xlFilename)
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Dim xlCells As Variant
Print "Connecting to Excel..."
Set Excel = CreateObject("excel.application")
Excel.Visible = False '// Don't display the Excel window
''''Print "Opening " & test & "..."
Excel.Workbooks.Open xlFilename '// Open the Excel file
Set xlWorkbook = Excel.ActiveWorkbook
Set xlSheet = xlWorkbook.ActiveSheet
Set xlCells = xlSheet.Cells
row = 1
written = 0
Print "Starting import from Excel file..."
Dim strName As String
Dim x As Integer
x = Messagebox ("Do you want to update the records in database", MB_YESNO+4, "Document" )
If (x = 6) Then
Dim y As Integer
y = Messagebox ("Agent is now ready to update , Do you really want to update the records in database", MB_YESNO+4, "Document" )
If (y = 6) Then
Call Addlog("Starting Agent...")
totalcount = 0
Add:
row = row + 1
written=written+1
Print ("Importing row: "& Cstr(row) & " of: " &Cstr(number))'//prints the number of the record being imported

If Trim(xlCells(1, 1).Value) <> "Project ID" Then
Addlog("Error during agent run......... ")
Addlog("Error Description: "+ "First Column in Spreadsheet should be the Project ID ")
Msgbox " An Error has been generated by the agent and it has been updated in log file , Please check log file to rectify the error "
Goto Last
Elseif Trim(xlCells(row, 1).Value) = "" Then
Addlog("Error during agent run......... ")
Addlog("Error Description: "+ "Agent found a blank entry in Project ID Column and Project ID column should not have blank entry")
Msgbox " An Error has been generated by the agent and it has been updated in log file , Please check log file to rectify the error "
Goto Last
End If
Set view = db.GetView("Main View")
Set doc = view.GetDocumentByKey(Trim(xlCells(row, 1).Value))
If doc Is Nothing Then
Call Addlog("Project With Project ID " + Trim(xlCells(row, 1).Value) + "not found in database")
Msgbox " An Error has been generated by the agent and it has been updated in log file , Please check log file to rectify the error "
Exit Sub
End If
totalcount = totalcount + 1

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Updating Records '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Trim(xlCells(1, 2).Value) <> "Project Name" Then
Addlog("Error during agent run: Project ID- " + Trim(xlCells(row, 1).Value) )
Addlog("Error Description: "+ " Column name in spreadsheet not matching with the specified field Or Column not in proper sequence " + " Column:- 2 ")
Msgbox " An Error has been generated by the agent and it has been updated in log file , Please check log file to rectify the error "
totalcount = totalcount-1
Goto Last
End If
If Trim(xlCells(row, 2).Value) <> "" And Trim(xlCells(row, 2).Value) <> "Blank" Then
CorrectProjectName = Checkforcommaandsemicoln(Trim(xlCells(row, 2).Value))
If CorrectProjectName = "1" Then
Addlog("Error during agent run: Project ID- " + Trim(xlCells(row, 1).Value) )
Addlog("Error Description: "+ " Project Name should not include Comma and Semi-colon")
Msgbox " An Error has been generated by the agent and it has been updated in log file , Please check log file to rectify the error "
totalcount = totalcount-1
Goto Last
End If
doc.fldProjectName = Trim(xlCells(row, 2).Value)
If Temp = "" Then
Temp = "Project Name"
Else
Temp = Temp + "," + "Project Name"
End If
Elseif Trim(xlCells(row, 2).Value) = "Blank" Then
doc.fldProjectName = ""
If Temp = "" Then
Temp = "Project Name"
Else
Temp = Temp + "," + "Project Name"
End If
End If

If Trim(xlCells(1, 3).Value) <> "Project Type" Then
Addlog("Error during agent run: Project ID- " + Trim(xlCells(row, 1).Value) )
Addlog("Error Description: "+ " Column name in spreadsheet not matching with the specified field Or Column not in proper sequence " + " Column:- 3 ")
Msgbox " An Error has been generated by the agent and it has been updated in log file , Please check log file to rectify the error "
totalcount = totalcount-1
Goto Last
End If
If Trim(xlCells(row, 3).Value) <> "" And Trim(xlCells(row, 3).Value) <> "Blank" Then
CorrectProjectType = CheckProjectType(Trim(xlCells(row, 3).Value))
If CorrectProjectType = "1" Then
Addlog("Error during agent run: Project ID- " + Trim(xlCells(row, 1).Value) )
Addlog("Error Description: "+ " Project Type not matching with the profile values in database")
Msgbox " An Error has been generated by the agent and it has been updated in log file , Please check log file to rectify the error "
totalcount = totalcount-1
Goto Last
End If
doc.ProjectType = Trim(xlCells(row, 3).Value)
If Temp = "" Then
Temp = "Project Type"
Else
Temp = Temp + "," + "Project Type"
End If
Elseif Trim(xlCells(row, 3).Value) = "Blank" Then
doc.ProjectType = ""
If Temp = "" Then
Temp = "Project Type"
Else
Temp = Temp + "," + "Project Type"
End If
End If

Call doc.Save( True, False )
Set doc = Nothing
Temp = ""
If written < number Then Goto Add

Print "Disconnecting from Excel.."
excel.quit
Call Addlog("Agent run successfully ")
Msgbox (" Agent run successfully ")
Goto Last
Else
excel.quit
Exit Sub
End If
Else
excel.quit
Exit Sub
End If

skips:
Addlog("Unexpected Error during agent run: Project ID " + Trim(xlCells(row, 1).Value) + " Error No "+Cstr(Err)+" On Line "+Cstr(Erl))
Addlog("Error Description: "+Error$())
Msgbox " An Error has been generated by the agent and it has been updated in log file , Please check log file to rectify the error "
'''excel.quit
totalcount = totalcount-1
Goto Last
Last:
excel.quit
If totalcount=0 Then
Call Addlog("Total No: of documents Updated= 0")
Else
Call Addlog("Total No: of documents Updated= " + Cstr(totalcount) )
End If
End Sub


Check It Swine Flu