how to create reports in excel using views basing on start date and end date?
can any one give me an idea?
with hope
Subject: how to create reports in excel using views
Depends what you want in the report. Notes has an “Export” option in the File menu. If you want the contents of the view or selected documents, in the same order as they are shown in the view, just use the File > Export option and choose a CSV file. You would get the documents by date range via either a search or just manually selecting documents.
If you need something a bit more complex and formatted, you’ll need to use an Excel object. Do a search on here for “Excel.application” and you’ll find lots of code on how to create Excel files from Notes documents.
As for getting the documents based on a date range you’ll either need to have a view sorted by the relevant date, and then either work iteratively through each document seeing if it’s in the date range, or do a full text search (FTSearch) to get your documents. Put them in a notesdocumentcollection then work through that for each line of the Excel sheet.
It’s actually very straight forward once you get used to how Excel outputs the data
Subject: RE: how to create reports in excel using views
I have tried to here but didnt get output
Dim uiw As New NotesUIWorkspace
Dim uiview As NotesUIView
Dim ss As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView, vc As NotesViewColumn, nitem As NotesItem, entry As NotesViewEntry, vwnav As NotesViewNavigator
Dim doc As NotesDocument, doc1 As NotesDocument
Dim filepath As String
Dim filenum As Integer
Dim xlapp As Variant, xlsheet As Variant,rows As Integer,cols As Integer,maxcols As Integer
Dim formula As String,startdate As Long, enddate As Long
Dim getmonth As Integer
On Error Goto Errorhandle
'Set uidoc=ws.CurrentDocument
'Set db=ss.CurrentDatabase
startdate = uiw.Prompt(Prompt_OKCANCELEDIT,"Enter the starting date","Start Date (use format MM/DD/YY)","")
enddate= uiw.Prompt(Prompt_OKCANCELEDIT,"Enter the ending date","End Date (use format MM/DD/YY)","")
Set uiview = uiw.CurrentView
Set view = uiview.View
Set vc=view.Columns(0)
'Set doc=view.GetFirstDocument
getmonth = Month(startdate) //here i am getting type mismatch but i need to generate dates like this jun 08,jul 08,aug 08
Msgbox getmonth
%REM
formula = |SELECT Date > [| & startdate & |] & Date < [| & enddate & |]|
'formula="Select (view.Columns(0),view.Columns(1),view.Columns(2) from view where date >=[|& startdate&|] & date <=[|& enddate&|])"
'varmonth=Evaluate({@Select(@Month(startdate); "January";"February";"March";"April";"May";"June";"July";"August";"September";"October"; "November"; "December")})
view.SelectionFormula = formula
Call uiw.ViewRebuild
ct = view.EntryCount
Messagebox "The number of docs in this view = " & ct,,"Count of docs in view"
%END REM
filepath$="C:\Documents and Settings\RTI\My Documents\INQUIRIESS2006-2007.xls"
filenum%=Freefile()
’ Open Excel Application
Open filepath$ For Output As filenum%
Set xlapp=createobject("Excel.Application")
xlapp.visible=True
xlapp.workbooks.add
xlapp.caption="Exporting From Notes CustomerByCategory View to Excel"
’ Export Data from Notes Document
rows=28
cols=1
Set xlsheet=xlapp.workbooks(1).worksheets(1)
xlapp.worksheets(1).Name="Inquiry Info"
'xlapp.worksheets(1).Range("A28").value="Category"
xlsheet.cells(rows,cols).value="Category"
If startdate<>enddate Then
xlsheet.cells(rows+1,cols+1).value=Evaluate({@text(varmonth)})
End If
Set vwnav=view.CreateViewNav()
Set entry=vwnav.GetFirst
'rows=29 'data starts in 29 row
While Not(entry Is Nothing)
If entry.IsCategory Then
xlapp.cells(rows+1, cols).value = entry.ColumnValues(1)
rows = rows + 1
'Else 'detail row
' Msgbox "Not categorizwd"
' For i = 1 To 6
' xlapp.cells(irow, icol+i) = entry.ColumnValues(i+1)
' Next
End If
Set entry = vwnav.GetNext(entry)
Wend
Close filenum%
If Dir(filepath$)<>"" Then
If Len(Dir(filepath$))>0 Then Kill filepath$
xlapp.ActiveWorkbook.SaveAs(filepath$)
xlapp.Quit
Set xlapp=Nothing
Msgbox "Data Exported Successfully",,"Alert"
End If
Terminate :
Exit Sub
Errorhandle:
Messagebox "Error:" & Error & " at line " & Cstr(Erl)
any help will be appreciated and advance thanks