Last week I was working on a project where the customer needed to get categorized data from a Notes view onto different sheets of Excel. Each of the Excel sheets had to have data specific to a particular branch. The desired output was to be on the lines shown below.

Picture 1

Picture 2

To get this working, I wrote an agent which I have shared here.

Sub Initialize

On Error Goto errHandler

Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim coll As NotesViewEntryCollection
Dim entry As NotesViewEntry
Dim doc As NotesDocument

Dim msExcel As Variant
Dim sheet1 As Variant
Dim sheet2 As Variant
Dim cnt As Integer
Dim cell As Variant
Dim rowNum As Integer
Dim brArray() As Variant
Dim branch As Variant

‘Select the database as current database

Set db = session.CurrentDatabase

‘Get the view from current database and get the view collection
Set view = db.GetView(“viewname”)

‘check whether the specified view is existing in the database

If view Is Nothing Then
Messagebox “View does not exist.”,64,”Message”
Exit Sub
End If

view.AutoUpdate = False
Set coll = view.AllEntries
cnt=coll.Count

‘Check if there are any documents existing in the view

If Isempty(cnt) Or cnt=0 Then
Msgbox “There is no document “,64,”Information”
Exit Sub
End If

Redim brArray(cnt-1) As Variant
Redim branch(cnt-1) As Variant
cnt=0
Set doc=view.GetFirstDocument
While Not doc Is Nothing
brArray(cnt)=doc.GetItemValue(“BrName”)(0)

‘Get the unique values from the first column of the view to name the worksheets

branch=Arrayunique(brArray,0)
Set doc=view.GetNextDocument(doc)
cnt=cnt+1
Wend

‘ Create the excel application and then add the worksheets to it
Set msExcel= CreateObject(“Excel.Application”)
msExcel.visible=True
msExcel.displayAlerts=False
If msExcel Is Nothing Then
Msgbox “An excel-object could not be created.” , 16 , “Error”
Exit Sub
End If
msExcel.Workbooks.Add
msExcel.ReferenceStyle = 2

‘ Create the worksheet based on unique value of the first column of the view
Dim upArray As Integer
upArray=Ubound(branch)
Dim i As Integer
For i=1 To upArray+1
Set sheet1= msExcel.Workbooks(1).Worksheets(i)

‘Set header values to the first Row of the worksheets

Set cell = sheet1.Range(“A1”)
cell.FormulaR1C1 = “SI.NO.”
Set cell = sheet1.Range(“B1”)
cell.FormulaR1C1 = “UHID.NO.”
Set cell = sheet1.Range(“C1”)
cell.FormulaR1C1 = “BR..NAME”
Set cell = sheet1.Range(“D1”)
cell.FormulaR1C1 = “PATIENT NAME”
Set cell = sheet1.Range(“E1”)
cell.FormulaR1C1 = “BILL NO”
Set cell = sheet1.Range(“F1”)
cell.FormulaR1C1 = “AMOUNT”
sheet1.Name =branch(i-1)
rowNum=2 ‘selecting the second row

‘Get the document’s handle from the view and export those documents to the worksheet

Set entry=coll.GetFirstEntry
While Not entry Is Nothing
If branch(i-1)=Cstr(entry.ColumnValues(0)) Then
Set cell = sheet1.Range(“A” & Cstr(rowNum))
cell.FormulaR1C1 = entry.ColumnValues(1)
Set cell = sheet1.Range(“B” & Cstr(rowNum))
cell.FormulaR1C1 = entry.ColumnValues(2)
Set cell = sheet1.Range(“C” & Cstr(rowNum))
cell.FormulaR1C1 = entry.ColumnValues(0)
Set cell = sheet1.Range(“D” & Cstr(rowNum))
cell.FormulaR1C1 = entry.ColumnValues(3)
Set cell = sheet1.Range(“E” & Cstr(rowNum))
cell.FormulaR1C1 = entry.ColumnValues(4)
Else
Goto NextDoc
End If

rowNum = rowNum + 1
NextDoc:
Set entry=coll.GetNextEntry(entry)
Wend
‘Format the styles of the excel worksheets.

msExcel.Visible = False
msExcel.Rows(“1:1”).Select
msExcel.Selection.Font.Bold = True
msExcel.Selection.Font.Underline = False
msExcel.Selection.Columns.AutoFit
msExcel.Range(“A2”).Select
msExcel.ActiveWindow.FreezePanes = True
msExcel.Cells.Select
msExcel.Cells.EntireColumn.AutoFit
sheet1.Rows(“1:1”).RowHeight = 25.5
msExcel.Range(“A2”).Select
Next
Exit Sub

errHandler:
If Err=208 Then
Msgbox “Excel is not installed in this machine, please install Excel to get the report”,,”Information”
Exit Sub
End If
Msgbox “Error “+Error+” occured on the line “+Cstr(Erl)+” with Err No “+Cstr(Err)
Exit Sub
End Sub

In this manner, you can export Notes documents to Excel applications to reflect data which is categorized and shown on multiple Excel sheets.