Solved

generate report to excel using notesdocumentcollection

Posted on 2004-03-22
31
332 Views
Last Modified: 2013-12-18
hello experts..

here is my problem..

i want to genereat reports to excel  notes document to excel by using notesdocumentcollection
but i dont know how..

here are the things that i want to happened..
i have a selection like this one

Set ncoll = db.Search( |form="fDocument" ......, nothing, 0)

in the selection i also want to include the date but i dont know the syntax of it...
the fields are "fromdate" and "todate"   fields

fields that iwant to exports to excel are:

Reference Code
Subject
Sender
Action Requested
COS Remarks
Secretary’s Remarks






Im using only one database.




thanks,
amd




 





0
Comment
Question by:amd1979
  • 16
  • 13
  • +1
31 Comments
 
LVL 19

Expert Comment

by:madheeswar
ID: 10648074
This can be done. wait for a while for the code..
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10648096
Sub Click(Source1 As Button)
      Dim ws As New notesuiworkspace
      Dim Source As notesuidocument
      Dim curdoc As notesdocument
      
      Set Source=ws.currentdocument
      Set curdoc=Source.document
      
      Dim ss As New notessession
      Dim db As notesdatabase
      Dim view As notesview
      Dim dc As notesdocumentcollection
      Dim doc As notesdocument
      
      Set db=ss.currentdatabase
      
'tmpkey=curdoc.urDateFieldIntheCurrentdocument(0)
      
      Set view=db.getview("(OnlybyDesc)")
      Dim fromdate As NotesDatetime
      Dim todate As notesdatetime
      
      Set fromdate=New notesdatetime(curdoc.urFromdate(0))
      Set todate=New notesdatetime(curdoc.urTodate(0))
      
      
      '================================
      Dim xlapp As Variant
      Dim xlsheet As Variant
      
      Set xlapp=createobject("Excel.Application")
      xlapp.statusbar="Creating Worksheet. Please be patient ..."
      xlapp.visible=True
      'xlapp.visible=False
      xlapp.workbooks.add
      xlapp.referencestyle=2
      Set xlsheet=xlapp.workbooks(1).worksheets(1)
      
      
      xlsheet.name="Result"
      
      Dim rows As Integer
      Dim cols As Integer
      Dim maxcols As Integer
      
      'Header Formation;
      xlapp.Rows("1:1").select
      xlapp.selection.font.name="Arial"
      xlapp.selection.font.size=11
      xlapp.selection.font.bold=True
      xlapp.selection.font.italic=True
      
      'Header Information(Titles):
      xlapp.statusbar="Creating Cells and Creating Cell Headings. Please be patient ..."
      xlsheet.application.activesheet.cells(1,1).value="Date Requested"
      xlsheet.application.activesheet.cells(1,2).value="Person Name"
      xlsheet.application.activesheet.cells(1,3).value="Dept name"
      xlsheet.application.activesheet.cells(1,4).value="Approving Person"
      'xlsheet.application.activesheet.cells(1,5).value="Designation"
      xlsheet.application.activesheet.cells(1,5).value="Contact No"
      xlsheet.application.activesheet.cells(1,6).value="Acc Code"
      xlsheet.application.activesheet.cells(1,7).value="Unit Price"
      xlsheet.application.activesheet.cells(1,8).value="Quantity"
      xlsheet.application.activesheet.cells(1,9).value="Amount"
      
      i=2
      'response1 = Messagebox ( "Click ""OK"" to export all the documents, if it is not a CLA Client or Click ""Cancel"" if it is not." ,(1+48 + 0 +0) ,"Mail Notice for Assign Person")
      'Click "OK" to export all the documents, if it is a CLA Client or Click "Cancel""
'If the User Clicks OK, Send MAil Else Continue.
      'OK means 1
'      If response1<>1 Then
'            Continue=True
'      Else
            'xlapp.visible=True
      Do While fromdate.dateonly<=todate.dateonly
            tmpkey=Cstr(fromdate.dateonly)
            
            
            Set dc=view.getalldocumentsbykey(tmpkey,True)
            Set doc=dc.getfirstdocument
            
            While Not doc Is Nothing
                  
                  xlsheet.range("A"&Trim(Str(i))).value=doc.createdon_d(0)
                  xlsheet.range("B"&Trim(Str(i))).value=doc.staffname_x(0)
                  xlsheet.range("C"&Trim(Str(i))).value=doc.dept_x(0)
                  xlsheet.range("D"&Trim(Str(i))).value=doc.approver_x(0)
            '      xlsheet.range("E"&Trim(Str(i))).value="Designation" 'doc.jobtitle_x(0)
                  xlsheet.range("E"&Trim(Str(i))).value=doc.ext_x(0)
                  
                        '      xlsheet.range("G"&(i))=Str(045678) 'doc.postalcode_n(0)
                  
                  'xlsheet.range("K"&Trim(Str(i))).value=doc.fax_n(0)
                  '      xlsheet.range("L"&Trim(Str(i))).value=doc.mobileno_n(0)
                  '      xlsheet.range("M"&Trim(Str(i))).value=doc.country_x(0)+"   "+doc.postalcode_n(0)
                  '      xlsheet.range("N"&Trim(Str(i))).value=doc.claclient_k(0)
                  
                  i=i+1
                  
                  Call xlsheet.Range("A1:E500").Columns.AutoFit
                  Call xlsheet.Range("A1:E500").Rows.AutoFit
                  
                  Set doc=dc.getnextdocument(doc)
                  xlapp.statusbar="Processing ..."
            Wend
            Call fromdate.Adjustday(1)
      Loop
      xlapp.statusbar="Completed ..."
      
End Sub
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10648112
Create a new form.
And on that form create 2 date fields.
one is for from date and another is for todate.
name first field as Fromdate
second field as Todate

And also one button to copy and paste the code i provided.

Compose this form and enter from and to date and clikc on button.
From date should be less than to date.

And in the view ur using in this code, u have to make it as text in the first column.
here is the revised code:
Sub Click(Source1 As Button)
      Dim ws As New notesuiworkspace
      Dim Source As notesuidocument
      Dim curdoc As notesdocument
      
      Set Source=ws.currentdocument
      Set curdoc=Source.document
      
      Dim ss As New notessession
      Dim db As notesdatabase
      Dim view As notesview
      Dim dc As notesdocumentcollection
      Dim doc As notesdocument
      
      Set db=ss.currentdatabase
      
'tmpkey=curdoc.urDateFieldIntheCurrentdocument(0)
      
      Set view=db.getview("(URView)")  'First column should be text and sorted.
      Dim fromdate As NotesDatetime
      Dim todate As notesdatetime
      
      Set fromdate=New notesdatetime(curdoc.Fromdate(0))
      Set todate=New notesdatetime(curdoc.Todate(0))
      
      
      '================================
      Dim xlapp As Variant
      Dim xlsheet As Variant
      
      Set xlapp=createobject("Excel.Application")
      xlapp.statusbar="Creating Worksheet. Please be patient ..."
      xlapp.visible=True
      'xlapp.visible=False
      xlapp.workbooks.add
      xlapp.referencestyle=2
      Set xlsheet=xlapp.workbooks(1).worksheets(1)
      
      
      xlsheet.name="Result"
      
      Dim rows As Integer
      Dim cols As Integer
      Dim maxcols As Integer
      
      'Header Formation;
      xlapp.Rows("1:1").select
      xlapp.selection.font.name="Arial"
      xlapp.selection.font.size=11
      xlapp.selection.font.bold=True
      xlapp.selection.font.italic=True
      
      'Header Information(Titles):
      xlapp.statusbar="Creating Cells and Creating Cell Headings. Please be patient ..."
      xlsheet.application.activesheet.cells(1,1).value="Reference Code"
      xlsheet.application.activesheet.cells(1,2).value="Subject"
      xlsheet.application.activesheet.cells(1,3).value="Sender"
      xlsheet.application.activesheet.cells(1,4).value="Action Requested"
      xlsheet.application.activesheet.cells(1,5).value="COS Remarks"
      xlsheet.application.activesheet.cells(1,6).value="Secretary’s Remarks"
      
      
      
      xlsheet.application.activesheet.cells(1,7).value="Unit Price"
      xlsheet.application.activesheet.cells(1,8).value="Quantity"
      xlsheet.application.activesheet.cells(1,9).value="Amount"
      
      i=2
      
            'xlapp.visible=True
      Do While fromdate.dateonly<=todate.dateonly
            tmpkey=Cstr(fromdate.dateonly)
            
            
            Set dc=view.getalldocumentsbykey(tmpkey,True)
            Set doc=dc.getfirstdocument
            
            While Not doc Is Nothing
                  
                  xlsheet.range("A"&Trim(Str(i))).value=doc.createdon_d(0)
                  xlsheet.range("B"&Trim(Str(i))).value=doc.staffname_x(0)
                  xlsheet.range("C"&Trim(Str(i))).value=doc.dept_x(0)
                  xlsheet.range("D"&Trim(Str(i))).value=doc.approver_x(0)
                  xlsheet.range("E"&Trim(Str(i))).value=doc.jobtitle_x(0)
                  xlsheet.range("F"&Trim(Str(i))).value=doc.ext_x(0)
                  
                  
                  i=i+1
                  
                  Call xlsheet.Range("A1:F500").Columns.AutoFit
                  Call xlsheet.Range("A1:F500").Rows.AutoFit
                  
                  Set doc=dc.getnextdocument(doc)
                  xlapp.statusbar="Processing ..."
            Wend
            Call fromdate.Adjustday(1)
      Loop
      xlapp.statusbar="Completed ..."
      
End Sub
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10648116
And in my code, i did not used error checking and other stuff. it is very easy to trim this code.

best of luck

before Tom & Sjef comes and looks this question, u do testing and confirm it is working or not.

-Thanks
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10648183
And if you are looking for db.search etc., I won't prefer as we need to Index that database inorder to use Search.
It will take resources on the server when ever Index is happening. And newly created document may not appear in ur result.

The best result is to approach getAlldocumentsBy Key .

Did u got time to test my code?
0
 
LVL 15

Expert Comment

by:Bozzie4
ID: 10648323
You don't need to index the database to use db.search, BUT it's better to use db.FTsearch, and you need to index the database to do that.

If you've got a view, that's even better (as m. already mentions), and you can use getalldocumentsbykey, or 'walk the view'  using getfirstdocument/getnextdocument

Tom

0
 
LVL 31

Expert Comment

by:qwaletee
ID: 10649133
Madheeswar,

You are MUCH too nervous!
0
 

Author Comment

by:amd1979
ID: 10655334
i paste the code below but no output.. pls check

thanks in advance...

Sub Click(Source1 As Button)
      
      Dim ws As New notesuiworkspace
      Dim Source As notesuidocument
      Dim curdoc As notesdocument
      
      Set Source=ws.currentdocument
      Set curdoc=Source.document
      
      Dim ss As New notessession
      Dim db As notesdatabase
      Dim view As notesview
      Dim dc As notesdocumentcollection
      Dim doc As notesdocument
      
      Set db=ss.currentdatabase
      
      'tmpkey=curdoc.urDateFieldIntheCurrentdocument(0)
      
      Set view=db.getview("lkup")  'First column should be text and sorted.
      Dim fromdate As NotesDatetime
      Dim todate As notesdatetime
      
      Set fromdate=New notesdatetime(curdoc.Fromdate(0))
      Set todate=New notesdatetime(curdoc.Todate(0))
      
      
     '================================
      Dim xlapp As Variant
      Dim xlsheet As Variant
      
      Set xlapp=createobject("Excel.Application")
      xlapp.statusbar="Creating Worksheet. Please be patient ..."
      xlapp.visible=True
     'xlapp.visible=False
      xlapp.workbooks.add
      xlapp.referencestyle=2
      Set xlsheet=xlapp.workbooks(1).worksheets(1)
      
      
      xlsheet.name="Result"
      
      Dim rows As Integer
      Dim cols As Integer
      Dim maxcols As Integer
      
     'Header Formation;
      xlapp.Rows("1:1").select
      xlapp.selection.font.name="Arial"
      xlapp.selection.font.size=11
      xlapp.selection.font.bold=True
      xlapp.selection.font.italic=True
      
     'Header Information(Titles):
      xlapp.statusbar="Creating Cells and Creating Cell Headings. Please be patient ..."
      xlsheet.application.activesheet.cells(1,1).value="Reference Code"
      xlsheet.application.activesheet.cells(1,2).value="Subject"
      xlsheet.application.activesheet.cells(1,3).value="Sender"
      xlsheet.application.activesheet.cells(1,4).value="Action Requested"
      xlsheet.application.activesheet.cells(1,5).value="COS Remarks"
      xlsheet.application.activesheet.cells(1,6).value="Secretary’s Remarks"
      
      i=2
      
          'xlapp.visible=True
      Do While fromdate.dateonly<=todate.dateonly
            tmpkey=Cstr(fromdate.dateonly)
            
            
            Set dc=view.getalldocumentsbykey(tmpkey,True)
            Set doc=dc.getfirstdocument
            
            While Not doc Is Nothing
                  
                  xlsheet.range("A"&Trim(Str(i))).value=doc.Dtreceive(0)
                  xlsheet.range("B"&Trim(Str(i))).value=doc.Sender(0)
                  xlsheet.range("C"&Trim(Str(i))).value=doc.Subject(0)
                  xlsheet.range("D"&Trim(Str(i))).value=doc.Arequested(0)
                  xlsheet.range("E"&Trim(Str(i))).value=doc.CosRem(0)
                  xlsheet.range("F"&Trim(Str(i))).value=doc.SecRem(0)
                  
                  
                  i=i+1
                  
                  Call xlsheet.Range("A1:F500").Columns.AutoFit
                  Call xlsheet.Range("A1:F500").Rows.AutoFit
                  
                  Set doc=dc.getnextdocument(doc)
                  xlapp.statusbar="Processing ..."
            Wend
            Call fromdate.Adjustday(1)
      Loop
      xlapp.statusbar="Completed ..."
      
      
End Sub
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10655353
No output in sense?
This code will
open blank Excel sheet. And then u can see the updates.

For this to work,
debug the code . File->Preferences->Debug Lotus Scipt.

Look what is happening. Any error messages? How many document collection ur getting?

In your PC, Excel is installed or not?
If the code is processing then u will see a blank excwel sheet with titles.

And in the view, use @Text(urdatefield) for the first column and sort Ascending and categortized.

make sure there are some documents within the date range ur specifying.
0
 

Author Comment

by:amd1979
ID: 10655492
yes no output,
the only output are the neme of the columns.. such as Subject|SEnder|Actionrequested| COs .....


yes i allready sorted the view
here is the formula of the view

@text(@date(dtreceive))

yes on the lkup2 view there was 3 documents.

yes i have excel program on my pc..

no error found on LS debugger


what do you mean by :

'tmpkey=curdoc.urDateFieldIntheCurrentdocument(0)
i dont declare any see the code i pasted..

i dont

tmp


0
 

Author Comment

by:amd1979
ID: 10655500
i pasted above..

thanks
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10655513
so, what I think is, the date range which us giving is not coming into the code.

hard code the dates instead of fromdate and todate in the code provided.

replace this:
Set fromdate=New notesdatetime(curdoc.Fromdate(0))
     Set todate=New notesdatetime(curdoc.Todate(0))
     
     
     
with:
Set fromdate=New notesdatetime("dd/mm/yyyy") 'will have ur dates in quotes
     Set todate=New notesdatetime("dd/mm/yyyy") 'will have ur dates in quotes.


'tmpkey=curdoc.urDateFieldIntheCurrentdocument(0)
this is not in use and u canb remove. if it is not declared, then it is considered as Variant.

I am sure there is a problem with date formats or no documents within the specified range.
     
     
     
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10655519
After doing the above, if it still not working then send me ur template to madheeswar at vmoksha dot com

and pls zip it. and nspecify what are the forms and views i need to look

thanks
0
 

Author Comment

by:amd1979
ID: 10655610
i already sent you the ntf thanks a lot
0
 

Author Comment

by:amd1979
ID: 10655916
did you see the form, view and code?

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Expert Comment

by:madheeswar
ID: 10655920
i sent an email to you. Did u not received it?

Invalid version. I have R5.
0
 

Author Comment

by:amd1979
ID: 10655931
aw, my version is R6.. i didnt rerceive yuor email..


0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10655981
make a new copy and name it as dts.ns4

or run at server console: load compact dts.nsf -R

this will become R5 version template.

Else u can do it from Administratoin.

Please send it immediately.,

Thanks

0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10655983
sorry it is dts.ns5 for a newcopy
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10656123
ur using Wrong view name.

it should be:
      Set view=db.getview("lkup2")
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10656127
Once I changed view name, it is working fine.

So, in the code u need to change the view name. Anyway I am sending the template for your use.
0
 

Author Comment

by:amd1979
ID: 10656132
the name of the form is test1
0
 

Author Comment

by:amd1979
ID: 10656153
ok, ill wait..

that was the only problem?
yes iam using that view.. lkup2..

thanks..

ill be wating for the ntf
0
 

Author Comment

by:amd1979
ID: 10656344
have you sent already the ntf?

thanks..

you can mail me on this add: amd1979@hotmail.com

i guessed the my 1st email  has problem..



0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10656361
I have sent it to the id which u have given.

As I said before, don't wait for my template and change the view name and the result will come.
0
 

Author Comment

by:amd1979
ID: 10656451
ah, but i already change the name the code

Set view=db.getview("lkup2")


my systems hung after clicking the buttong what do you are the problem?


ariel
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10656474
did u received the template or not?
It worked for me .

And I am pasting the entire code here again. This thread is becoming very large.

before that create 2 feilds fromdate and todate which are date fields. And a search button.
and paste the below code in search button.
The template which I sent is in the form testform

Sub Click(Source1 As Button)
      
      Dim ws As New notesuiworkspace
      Dim Source As notesuidocument
      Dim curdoc As notesdocument
      
      Set Source=ws.currentdocument
      Set curdoc=Source.document
      
      Dim ss As New notessession
      Dim db As notesdatabase
      Dim view As notesview
      Dim dc As notesdocumentcollection
      Dim doc As notesdocument
      
      Set db=ss.currentdatabase
      
     'tmpkey=curdoc.urDateFieldIntheCurrentdocument(0)
      
      Set view=db.getview("lkup2")  'First column should be text and sorted.
      Dim fromdate As NotesDatetime
      Dim todate As notesdatetime
      
      Set fromdate=New notesdatetime(curdoc.Fromdate(0))
      Set todate=New notesdatetime(curdoc.Todate(0))
      
      
     '================================
      Dim xlapp As Variant
      Dim xlsheet As Variant
      
      Set xlapp=createobject("Excel.Application")
      xlapp.statusbar="Creating Worksheet. Please be patient ..."
      xlapp.visible=True
     'xlapp.visible=False
      xlapp.workbooks.add
      xlapp.referencestyle=2
      Set xlsheet=xlapp.workbooks(1).worksheets(1)
      
      
      xlsheet.name="Result"
      
      Dim rows As Integer
      Dim cols As Integer
      Dim maxcols As Integer
      
     'Header Formation;
      xlapp.Rows("1:1").select
      xlapp.selection.font.name="Arial"
      xlapp.selection.font.size=11
      xlapp.selection.font.bold=True
      xlapp.selection.font.italic=True
      
     'Header Information(Titles):
      xlapp.statusbar="Creating Cells and Creating Cell Headings. Please be patient ..."
      xlsheet.application.activesheet.cells(1,1).value="Reference Code"
      xlsheet.application.activesheet.cells(1,2).value="Subject"
      xlsheet.application.activesheet.cells(1,3).value="Sender"
      xlsheet.application.activesheet.cells(1,4).value="Action Requested"
      xlsheet.application.activesheet.cells(1,5).value="COS Remarks"
      xlsheet.application.activesheet.cells(1,6).value="Secretary&#8217;s Remarks"
      
      i=2
      
          'xlapp.visible=True
      Do While fromdate.dateonly<=todate.dateonly
            tmpkey=Cstr(fromdate.dateonly)
            
            
            Set dc=view.getalldocumentsbykey(Cstr(tmpkey),True)
            Set doc=dc.getfirstdocument
            
            While Not doc Is Nothing
                  
                  xlsheet.range("A"&Trim(Str(i))).value=doc.Dtreceive(0)
                  xlsheet.range("B"&Trim(Str(i))).value=doc.Sender(0)
                  xlsheet.range("C"&Trim(Str(i))).value=doc.Subject(0)
                  xlsheet.range("D"&Trim(Str(i))).value=doc.Arequested(0)
                  xlsheet.range("E"&Trim(Str(i))).value=doc.CosRem(0)
                  xlsheet.range("F"&Trim(Str(i))).value=doc.SecRem(0)
                  
                  
                  i=i+1
                  
                  Call xlsheet.Range("A1:F500").Columns.AutoFit
                  Call xlsheet.Range("A1:F500").Rows.AutoFit
                  
                  Set doc=dc.getnextdocument(doc)
                  xlapp.statusbar="Processing ..."
            Wend
            Call fromdate.Adjustday(1)
      Loop
      xlapp.statusbar="Completed ..."
      
      
End Sub
0
 

Author Comment

by:amd1979
ID: 10656545
i didnt received any mail coming from you..

here is my email amd1979@hotmai.com
0
 

Author Comment

by:amd1979
ID: 10656587
it is working bro, ADDTIONAL what if i want to add a search key like i want to search..


Reportname: End of the day
Category     : Invitation
and
also

Fromdate and to date

   
now my key is not only one but 3?

0
 
LVL 19

Accepted Solution

by:
madheeswar earned 500 total points
ID: 10656612
This will be difficult.
But u need to give moew info on this.
As I did not understood the exact requirement.

If it is simple, I will provide answer here, else open a new question.

Close this question with Grade "A"

Thanks.
0
 

Author Comment

by:amd1979
ID: 10656647
ok..i will going to accept your answer
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now