Options
Option Public
Option Declare


Declarations
Dim whereami As String					' global debug variable


Sub Initialize
	' this agent exports a lookup view's document to excel.
	' as client requires custom formatting, we will use a view with column calculations.
	Dim s As New NotesSession
	Dim db As NotesDatabase					' this db
	Dim lupV As NotesView						' lookup view of docs
	Dim vNav As NotesViewNavigator		' view navigator - needed to get vE
	Dim vE As NotesViewEntry					' entry (documents) in lookup view
	
	' xls dims
	Dim xl As Variant								' the Excel application object
	Dim xlDoc As Variant							' the Excel workbook/sheet
	Dim row As Long								' chose long just incase there are more than 32k of entries
	
	' view dims
	Dim vcArray As Variant						' array of view columns in v (vname) - needed to get # of columns and their titles
	Dim vccount As Integer						' count of view columns (vcArray)
	Dim excludecount As Integer				' number columns (starting from left) to exclude from report
	
	On Error Goto ErrorHandler
	
	whereami="(Initialize) Beginning ..."
	excludecount=0
	Set db = s.CurrentDatabase
	Set lupV = db.GetView ("lupExcelExport")
	Set vNav = lupV.CreateViewNav()
	Set ve= vNav.GetFirst()
	If (ve Is Nothing) Then
		' quit, nothing to export
		Print "no entries to export to Excel. Exiting..."
		Exit Sub
	End If
	
	
	' we have at least one document, create the Excel objects
	Set xl = CreateObject ("Excel.Application")
	xl.Visible = True
	xl.displayAlerts=False
	Set xlDoc = xl.Application.Workbooks.Add
	
	' set the 1st excel row
	row = 1				' starting row number ( column label descriptions)
	
	' write the column labels to the 1st excel row
	vcArray = lupV.Columns
	vccount = Ubound(vcArray)
	Call WriteXLSLabels(xL, row, vcArray, excludecount)
	row = row + 1
	
	While Not (ve Is Nothing)
		' loop through entry documents and write out Excel rows
		' treat each column as a column in Excel
		
		Call WriteXLSData(xL, row, vE, vccount, excludecount)
		
		' increment
		row = row + 1
		Set vE = vNav.GetNext(vE)
	Wend
	' no more rows to export, close the Excel object
	Call xl.ActiveWorkbook.SaveAs ("c:\export.xls")
	Call xlDoc.close (False) 
	Set xlDoc = Nothing
	Call xl.Application.Quit
	Set xl = Nothing 
	
	' Print done
	whereami="(Initialize) Export completed"
	Print whereami
	Exit Sub
	
ErrorHandler:
	Print "(Initialize) Error exporting. Info: " & Str(Err) & ": " & Error$ & " on line: " & Cstr(Erl)
	Exit Sub
	
End Sub


Function WriteXLSLabels(xL As Variant, row As Long, vcArray As Variant, excludecount As Integer) As Integer
	' this function adds the table begin tag
	' xL - Excel application object
	' row - current row in xL worksheet
	' vcArray - array of all the columns in view/table
	' excludecount -  total # of columns to exclude (starting from left) should be at least 1 to not do the category column
	Dim tmpcount As Integer					' temporary counting variable
	
	On Error Goto FunctionErrorHandler
	
	' set counter
	tmpcount = 1		' start at 1 as 1 is base for xls column numbers
	
	whereami="(WriteXLSLabels) Adding labels ..."
	Forall c In vcArray
		If tmpcount > excludecount Then
			whereami="(WriteXLSLabels) Adding label: " & c.Position
			xl.cells(row, tmpcount) = c.Title
		End If
		tmpcount=tmpcount + 1
	End Forall
	
	WriteXLSLabels=1			' success
	Exit Function
	
FunctionErrorHandler:
	Print "(WriteXLSLabels) Error exporting. Info: " & Str(Err) & ": " & Error$ & " on line: " & Cstr(Erl)
	WriteXLSLabels=0			' failure
	Exit Function
	
End Function


Function WriteXLSData(xL As Variant, row As Long, vE As NotesViewEntry, columncount As Integer, excludecount As Integer) As Integer
	' this function adds the table begin tag
	' xL - Excel application object
	' row - current row in xL worksheet
	' vE - current entry's data to export
	' columncount - total # of columns
	' excludecount -  total # of columns to exclude (starting from left) should be at least 1 to not do the category column
	Dim multiarray As Variant				' tmporary working variable for looping through multivalue column values.
	Dim tmpstring As String					' temporary working string for holding column values
	Dim tmpcount As Integer					' temporary counting variable
	Dim columnval As Integer				' internal for loop count variable
	
	On Error Goto FunctionErrorHandler
	
	whereami="(WriteXLSData) Adding row ..." & vE.GetPosition(".")
	
	' set counter
	tmpcount = 1		' start at 1 as 1 is base for xls column numbers
	
	For columnval = excludecount To Ubound(vE.ColumnValues)			' excludecount is base 0
		tmpstring=""
		If Isarray(vE.ColumnValues(columnval)) Then
			' we need to cycle and print all values
			multiarray = vE.ColumnValues(columnval)
			Forall colvalue In multiarray
				If (tmpstring="") Then
					' 1st value
					tmpstring = Cstr(colvalue)
				Else
					' > than 1st
					tmpstring = tmpstring & ", " & Cstr(colvalue)
				End If
			End Forall
		Else
			' just do a simple line to get
			tmpstring = Cstr(vE.ColumnValues(columnval) )
		End If		
		' add tmpstring to current row/column
		xl.cells(row, tmpcount) = tmpstring
		
		' increment counter
		tmpcount=tmpcount + 1
	Next columnval
	
	WriteXLSData=1			' success
	Exit Function
	
FunctionErrorHandler:
	Print "(WriteXLSData) Error exporting. Info: " & Str(Err) & ": " & Error$ & " on line: " & Cstr(Erl)
	WriteXLSData=0			' failure
	Exit Function
	
End Function