Depending on how many documents are in the view, how often you want to run the search and how many results you expect to get you can use different methods. Easiest would probably be to cache all values in a Array/List or try to create some kind of smart categorization formula.
Below is a kind of untested pseudocode for using List to find result in cached values.
Dim tmplist List as Integer
while not doc is nothing 'cache all values from a view
Thanks for the answer. but my scenerio here is bit different.
Here is my problem.
I am creating monthly reports in excel.
I have two forms A, B
user 1 created 10 documents with company X using form A (If same user+company name+form then I want just one company name but I need 10 document totals)
user 1 created 3 documents with company Y using Form B (three seperate entites in excel)
User 2 created 4 documents with company abc using Form A
user2 created 2 documents with company ghi using form B
I want to show the result in excel in this format
User Company check check1 check 2 Total
User1 X 10 10 10 30
user1 Y 1 1 1 3
user1 y 1 0 0 1
user1 y 0 1 1 2
-- -- -- --
user1 ToTal 12 12 12 36
user2 abc 4 4 4 12
user2 ghi 1 0 1 2
user2 ghi 1 1 1 3
-- -- -- --
user2 Total 6 5 6 17
Based on the search criteria I am getting the docuemnt collection but after that i don’t know how to export it to excel in above given format.
Please provide some code to solve this. I am stuck with this from two days.
Dim ws As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim doc As NotesDocument
Dim doc1 As NotesDocument
Dim session As New NotesSession
Dim db As NotesDatabase
Dim filename As String
Dim j As Long
Dim xlApp As Variant
Dim xlsheet As Variant
Dim dc As NotesDocumentCollection
Dim CurrDoc As NotesDocument
Dim dt As New NotesDateTime("")
Set uidoc = ws.CurrentDocument
Set doc1 = uidoc.document
Set db = session.CurrentDatabase
Rtitle = uidoc.FieldGetText("RType")
Rev = uidoc.fieldgettext("Rev")
Cname = uidoc.fieldgettext("Cnam")
SearchString$ = {Form = "FollowUp"} & {& DCDate >=[} & doc1.FDate(0) & {]} & {& DCDate <=[} & doc1.TDate(0) &{]}
SearchString$ = SearchString$ & {|} & {(} & {BeginDate >= [} & doc1.FDate(0) & {]} & {& BeginDate <=[} & doc1.TDate(0) & {]} & {)}
Set dc = db.Search(SearchString$,dt, 0)
You can export to CSV text format which you can then open in Excel. With new Excel versions you can use XML. You can also export directly to Excel using OLE.
Take a look at my examples of exporting to excel (simple, advanced, web-based): Botstation - Code Vault
You can use web-based example to make it export to CSV or TAB-separated text format.