Export notes view data to Excel Sheet

HCL Domino Domino Forum
1

HiI have a notes view .I want to export all data in a excel sheet.

So pls help me.Its urgent.

Thanx in advance

2

Subject: Export notes view data to Excel Sheet

Hi,

This willl be easier and generic too.

Run it on all selected docs.

Dim session As New NotesSession

Dim db As NotesDatabase

Dim view As NotesView

Dim doc As NotesDocument

Dim fmlaitem As notesitem

Dim myobject As Variant

Dim mysheet As Variant

Dim uiview As NotesUIView

Dim ws As New NotesUIWorkspace

Dim collection As NotesDocumentCollection

Dim column As NotesViewColumn



On Error Goto generalError



Set db=session.currentDatabase

Print "Please Be Patient as the Excel Spreadsheet is being created..."

Set uiview=ws.currentView

Set view=uiview.View



Set collection = db.UnprocessedDocuments

Set doc=collection.getFirstDocument

If doc Is Nothing Then

	Msgbox "No record found"

	End

End If



Set myobject=createObject("Excel.application")

On Error Goto theError

myobject.statusbar="Creating Excel Sheet"

 'add the object

myobject.workbooks.add

Set mysheet=myobject.workbooks(1).Worksheets(1)

mysheet.name="Review"

count=2

Dim fmlaval As Variant

For i = 1 To collection.count	

	Print i

	Forall c In view.Columns

		mysheet.cells(1, c.Position).value=c.Title

		mysheet.cells(1,c.Position).Font.Bold = True

		If c.formula <> "" Then

			fmla = c.formula

			fmlaval = Evaluate(fmla, doc)

		Else

			item = c.ItemName

			fmlaval = doc.GetItemValue( item )

		End If

		mysheet.cells(count, c.position).value = fmlaval

’ mysheet.cells(count, c.position).WrapText = True

	End Forall

	Set doc=collection.getNextDocument(doc)

	count=count+1

Next	

myobject.statusbar="Finished"

mysheet.Columns.shrinktofit = True

mysheet.Columns.autofit

myobject.visible=True

End

theError:

myobject.quit

Msgbox Error()

End

generalError:

Msgbox Error()

End

Thanks

Saswat Das

3

Subject: Export notes view data to Excel Sheet

hi Smruti, try the below code…I will help you out

Sub Initialize

Dim ws As New NotesUIWorkspace

Dim session As New notesSession

Dim db As NotesDatabase

Dim viewChq, viewCont, viewPoints As NotesView

Dim coll As NotesDocumentCollection

Dim docChq, docCont, docPoints As NotesDocument

Dim mysheet, myobject,worksheet As Variant

Dim ctr, show As Integer

Dim totalVol, totalPoint As Long

Dim vc As NotesViewEntryCollection



ctr = 2

Set myobject = createObject("Excel.application")

myobject.workbooks.add

Set worksheet = myobject.workbooks(1).Worksheets(1)

worksheet.name = "Fire Extn Audit Report"

worksheet.cells(1,1).value = "Depot"

worksheet.cells(1,1).Font.Bold = True

worksheet.cells(1,2).value = "Date"

worksheet.cells(1,2).Font.Bold = True

worksheet.cells(1,3).value = "Sand bucket has sand"

worksheet.cells(1,3).Font.Bold = True

worksheet.cells(1,4).value = "Sand is clean"

worksheet.cells(1,4).Font.Bold = True

worksheet.cells(1,5).value = "Water bucket is half full"

worksheet.cells(1,5).Font.Bold = True

worksheet.cells(1,6).value = "Buckets is clean"

worksheet.cells(1,6).Font.Bold = True

worksheet.cells(1,7).value = "Bucket not full of waste"

worksheet.cells(1,7).Font.Bold = True

worksheet.cells(1,8).value = "Clearly visible buckets"

worksheet.cells(1,8).Font.Bold = True

worksheet.cells(1,9).value = "Dispaly signage"

worksheet.cells(1,9).Font.Bold = True

worksheet.cells(1,10).value = "Clean Extuingsher"

worksheet.cells(1,10).Font.Bold = True

worksheet.cells(1,11).value = "Extuingsher within valid date"

worksheet.cells(1,11).Font.Bold = True

worksheet.cells(1,12).value = "Sufficient to meet exigency"

worksheet.cells(1,12).Font.Bold = True

worksheet.cells(1,13).value = "Requisite pressure"

worksheet.cells(1,13).Font.Bold = True

worksheet.cells(1,14).value = "General cond`n is good"

worksheet.cells(1,14).Font.Bold = True

worksheet.cells(1,15).value = "Aware Depot Staff"

Set db = session.CurrentDatabase

Set viewCont = db.GetView("vwFireExtn")

Set docCont = viewCont.GetFirstDocument

Set vc = viewCont.AllEntries

show = vc.Count



While Not docCont Is Nothing

	Print ctr & " of " & show

skip:

	worksheet.cells(ctr,1) = docCont.GetItemValue("cfa_depot")(0)

	worksheet.cells(ctr,2) = docCont.GetItemValue("fire_date")(0)

	worksheet.cells(ctr,3) = docCont.GetItemValue("fire_answer")(0)

	worksheet.cells(ctr,4) = docCont.GetItemValue("fire_answer_1")(0)

	worksheet.cells(ctr,5) = docCont.GetItemValue("fire_answer_2")(0)

	worksheet.cells(ctr,6) = docCont.GetItemValue("fire_answer_3")(0)

	worksheet.cells(ctr,7) = docCont.GetItemValue("fire_answer_4")(0)

	worksheet.cells(ctr,8) = docCont.GetItemValue("fire_answer_5")(0)

	worksheet.cells(ctr,9) = docCont.GetItemValue("fire_answer_6")(0)

	worksheet.cells(ctr,10) = docCont.GetItemValue("fire_answer_7")(0)

	worksheet.cells(ctr,11) = docCont.GetItemValue("fire_answer_8")(0)

	worksheet.cells(ctr,12) = docCont.GetItemValue("fire_answer_9")(0)

	worksheet.cells(ctr,13) = docCont.GetItemValue("fire_answer_10")(0)

	worksheet.cells(ctr,14) = docCont.GetItemValue("fire_answer_11")(0)

	worksheet.cells(ctr,15) = 		Set docCont = viewCont.getNextDocument(docCont)

	ctr = ctr + 1

Wend



worksheet.Columns.shrinktofit = True

worksheet.Columns.autofit

myobject.visible = True

End Sub

4

Subject: quick method if formatting not critical

  1. select docs from view2. select edit/copy selected as table

  2. paste into excel