Notes of my Lotus Notes Headline Animator

Search My Blog

Showing posts with label Importing. Show all posts
Showing posts with label Importing. Show all posts

Friday, December 21, 2012

Excel sheet to notes Document - Importing Data

Importing excel sheet information into individual documents in LN client application:


Sub Initialize

Dim xlFilename As String
'xlFilename = "C:\Documents and Settings\ggouda\My Documents\EMP_WRS_DETAIL.xls"

'// This is the name of the Excel file that will be imported

xlFilename=Inputbox("Please enter path of the spreadsheet - Example: C:\Excel.xls" & Chr(10) &_
" C:\spreadsheet.xls", "File Path Inquiry Box", " enter path here ....")


Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Set doc = New NotesDocument(db)
Dim One As String

Dim row As Integer
Dim written As Integer


'// Connect to Excel and open the file. Start pulling over the records.
Dim Excel As Variant
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Print "Connecting to Excel..."
Set Excel = CreateObject( "Excel.Application.9" )
Excel.Visible = False '// Don't display the Excel window
Print "Opening " & xlFilename & "..."
Excel.Workbooks.Open xlFilename '// Open the Excel file
Set xlWorkbook = Excel.ActiveWorkbook
Set xlSheet = xlWorkbook.ActiveSheet

'// Cycle through the rows of the Excel file, pulling the data over to Notes
Goto Records
Print "Disconnecting from Excel..."
xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
Excel.Quit '// Close Excel
Set Excel = Nothing '// Free the memory
Print " " '// Clear the status line


Records:
row = 1 '// Integers intialize to zero
written = 0

Print "Starting import from Excel file..."
Do While True
Finish:

With xlSheet
row = row + 1
Set view = db.GetView("Import")
Set doc = db.CreateDocument '// Create a new doc
doc.Form = "Person"

doc.Employee_ID = .Cells( row, 1 ).Value
doc.Prj_Cost_Centre= .Cells(row, 2 ).Value
doc.WRS_Number= .Cells(row, 3).Value
doc.Proj_Title = .Cells( row, 4 ).Value
doc.Project_Location = .Cells(row, 7).Value
doc.Dispatcher = .Cells( row, 8).Value
doc.Project_manager = .Cells(row, 9).Value
doc.Wk_Ending = .Cells( row, 10).Value
doc.Employee_DOJ = .Cells( row, 11).Value
doc.Employee_Name = .Cells( row, 12).Value
doc.Employee_Role = .Cells( row, 13).Value
doc.ST_Hrs = .Cells( row, 14).Value
doc.OT_Hrs = .Cells( row, 15).Value
doc.Total_Hrs = .Cells( row, 16).Value
doc.Misc_Exp = .Cells( row, 17).Value
doc.Travel = .Cells( row, 18).Value
doc.Total_Bill = .Cells( row, 19).Value

Call doc.Save( True, True ) '// Save the new doc
written = written + 1
Print Str(written)
If .Cells( row, 1 ).Value = "" Then
Goto Done
End If
End With

Loop
Return
Done:
Messagebox "Import Complete - Total number of WRS documents imported ---> " & written


End Sub