Solved

Export to Excel

Posted on 2011-02-14
18
512 Views
Last Modified: 2013-12-18
There is a view in which one coloumn is sorted on teh basis of Year and second on the basis of month, I want to extract all the documents of one year to excel ex. if I just select 2009 it should give extract of all the documents of that year nad if I select month also like Jan then it should give extract of all documents of that year and that month.
0
Comment
Question by:Glactus
  • 8
  • 4
  • 2
  • +1
18 Comments
 
LVL 5

Expert Comment

by:Didier Vally
Comment Utility
I think you can code a macro to do this kind of filtering based on year and month.
0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
To get the currently selected category in a view (no matter whether you're positioned on a document or a category):
Dim w As New NotesUIWorkspace
Dim v As NotesUIView
Dim cat As Variant

Set v = w.CurrentView
cat = v.CaretCategory

Open in new window

0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
Is it sorted or categorized by year/month?

Are you simply asking to select any document within the particular subset, and the system shoudl figure out which rows to export?
0
 

Author Comment

by:Glactus
Comment Utility
@qwaletee: Yes the coloumn is sorted and I want to select the category not only one document.
0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
I assumed you'll figure out from there.
Once you get Category name, to get all documents in that category use NotesView.GetAllDocumentsByKey and use Category name as the key.
0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
I just saw that you have two categorized columns, not one.
Since AFAIK, there's no way you can get the parent category name while you're positioned on the child category, your best bet is to tell your users to select first document in the category they want exported.
Then, when you have document, it's easy to read field values and get both categories.

Then use the same thing as above, but as the key you'll send array containing parent category at position 0 and child category at position 1, like this:


Function GetAllDocsInCurrentCategory() As NotesDocumentCollection
	Dim w As New NotesUIWorkspace
	Dim v As NotesView
	Dim uiv As NotesUIView
	Dim doc As NotesDocument
	Dim arrCat(1) As String

	Set doc = GetSelectedDoc()
	If doc Is Nothing Then Exit Function 'you can add user messages or error handling

	arrCat(0) = doc.FieldNameDisplayedInColumn1(0) 'parent category - change field names
	arrCat(1) = doc.FieldNameDisplayedInColumn2(0) 'child category

	Set uiv = w.CurrentView	'get current UI view
	Set v = uiv.View 	'get its back-end view
		
	Set etAllDocsInCurrentCategory = v.GetAllDocumentsByKey( arrCat, True )
	
End Function


Function GetSelectedDoc() As NotesDocument
	'Mbo, 04.04.2005.
	'The function returns currently selected document in a view
	Dim s As New NotesSession
	Dim db As NotesDatabase
	Dim c As NotesDocumentCollection
	Dim doc As NotesDocument
	
	Set db = s.CurrentDatabase	
	Set c = db.UnprocessedDocuments
	If c.Count = 0 Then Exit Sub 'c is never nothing in a view

	Set doc = c.getFirstdocument
	If doc Is Nothing Then Exit Function
	
	Set GetSelectedDoc = doc
	
End Function

Open in new window

0
 

Author Comment

by:Glactus
Comment Utility
Actually that is the problem, the requirement is to select the category instead of documents.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
AFAIK it's not possible when you have more than one category in your view.
That's because of the fact that categories are not documents so they cannot be obtained using LotusScript.
You can get only currently selected category name, but not its parent category.

You don't have to select all documents in a category, only the first document of a (second level) category that you want to export.
If an user selects category you'd prompt him with message similar to this "Please select first document of the category you want to export".
0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
So the final solution would be:

 - get currently selected document in a view (using GetSelectedDoc function)

 - if doc is nothing, get currently selected category (CaretCategory) using the code I posted in my first post
       - if the category is Year - use the code bellow to get all documents in that category
       - if the category is not Year - prompt the user to select only the first (or any other) document in that category and exit sub

 - else (doc is NOT nothing) use the code from my second post to get all documents in that second level category

 - do the export of DocumentCollection


That's best I can offer, maybe someone else will know how to get that parent category so you'll be able to improve the code...
Function getAllDocsInCategory( topLevelCategory As String ) As NotesDocumentCollection
	Dim s As New NotesSession
	Dim w As New NotesUIWorkspace
	Dim db As NotesDatabase
	Dim v As NotesView
	Dim c As NotesDocumentCollection
	Dim nav As NotesViewNavigator
	Dim entry As NotesViewEntry

	Set db = s.CurrentDatabase
	Set c = db.CreateDocumentCollection()	'undocumented
	Set v = w.CurrentView.View
	Set nav = v.CreateViewNavFromCategory( topLevelCategory )
	Set entry = nav.GetFirstDocument

	Do Until entry Is Nothing
		Call c.AddDocument( entry.document )

		Set entry = nav.GetNextDocument( entry )
	Loop

	Set getAllDocsInCategory = c

End Function

Open in new window

0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
There are some complicated tricks you can use to find the parent category, despte mbonaci's claim, but it may not be worth it. The simpl solution is t make your second category include both year and month, so you can get the correct value just by retrieving the current category (CaretCategory)
0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
I'm curious, which complicated tricks?
0
 

Author Comment

by:Glactus
Comment Utility
I have achieved the functionality, I have created an another view and given user the option to select Year and Month from the previous view(checking that it should not be a multi selection).

In the other view in the first coloumn I am getting the document of the Year and Months selected by user and the exporting them!!!!!!!!!!!!!!!!!!!!!!!

Little tricky but easy one :)
0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
So we did not help?

 > ...and given user the option to select Year and Month from the previous view...
How exactly did you implement this? Can you post the code?
0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
I think we helped a lot with this question and provided perfectly good solution with the complete source code and detailed explanation.
Can you answer the question from my previous post about your implementation and post your code?
0
 

Accepted Solution

by:
Glactus earned 0 total points
Comment Utility
Sub Click(Source As Button)
            
            Dim workspace As New NotesUIWorkspace
            Dim session As New NotesSession
            
            Dim db As NotesDatabase
            Dim response As Variant
            Dim response1 As Variant
            Dim row As Integer
            Dim colmn As Integer
            
            Dim values1 As Variant
            Dim values2 As Variant
            Dim Searchstring As String
            Dim Mon As Integer
            Dim vw As NotesView
            Dim vw1 As NotesView
            Dim doccol As NotesDocumentCollection
            Dim doc As NotesDocument
            Dim object As NotesEmbeddedObject 
            Dim xlApp As Variant
            Dim oWorkbook As Variant   
            
            Set xlApp = CreateObject("Excel.Application") 
            
            
            Set oworkbook = xlApp.Workbooks 'handle to Workbook
            oworkbook.Add 
            'this highlights the headings 
            xlApp.application.Rows("1:1").Select
            With xlApp.application.Selection.Font
                        .bold = True
                        .ColorIndex = 48
                        .Name = "Arial"
                        .Size = 12
            End With
            
            
            
            
            Set db = session.CurrentDatabase
            Set vw1 = db.GetView("Approveduser")
            Set vw = db.GetView("ExpLkp")
            
            
            
            values1 =  Evaluate (|@Text(@DbColumn("":"";@DbName;"Approveduser";1))|)
            values2 =  Evaluate (|@unique(@Text(@DbColumn("":"";@DbName;"Approveduser";2)))|)
            
            response = workspace.Prompt (PROMPT_OKCANCELLIST, _
            "Year", _
            "Select a value for Year", _
            values1(0), values1)
            
            If Isempty (response) Then
                        Messagebox "Year Selection Cancelled", , "Value not Selected"
                        Exit Sub
            End If
            
            
            Mon = workspace.Prompt (PROMPT_YESNO, _
            "Month Selection", "Do you want to select Month?")
            If Mon = 1 Then
                        
                        response1 = workspace.Prompt (PROMPT_OKCANCELLIST, _
                        "Month", _
                        "Select a value for Month", _
                        values2(0), values2)
                        
            End If
            
            
            
            If Isempty (response1) Then
                        Searchstring = response
            Else
                        Searchstring = Trim(response + "~" + response1)
                        
            End If
            
            
            
            
            Set doccol = vw.GetAllDocumentsByKey(Searchstring)
            
            If doccol.Count = 0 Then
                        Msgbox "No Documents to Export for this selection"
                        Exit Sub
            End If
            
            
            
            Set doc = doccol.GetFirstDocument()
            
             'Stick out the column headers
            hcolmn=1
            Forall c In vw1.Columns
                        xlApp.cells(1,hcolmn) = c.title
                        hcolmn=hcolmn+1
            End Forall  
            
            colmn = 0          
            row=2 
            
            
            xlApp.Visible = True 
            
            While Not doc Is Nothing
                        
                        Dim yr As String
                        Dim mo As String
                        
                        yr = Cstr(Year(doc.Admin_Date(0)))
                        mo = Cstr(Month(doc.Admin_Date(0)))
                        
                        xlApp.cells(row,colmn+1) = yr
                        
                        If           mo = "1" Then
                                    xlApp.cells(row,colmn+2) = "January"
                        Elseif mo = "2" Then
                                    xlApp.cells(row,colmn+2) = "February"
                        Elseif mo = "3" Then
                                    xlApp.cells(row,colmn+2) = "March"
                        Elseif mo = "4" Then
                                    xlApp.cells(row,colmn+2) = "April"
                        Elseif mo = "5" Then
                                    xlApp.cells(row,colmn+2) = "May"
                        Elseif mo = "6" Then
                                    xlApp.cells(row,colmn+2) = "June"
                        Elseif mo = "7" Then
                                    xlApp.cells(row,colmn+2) = "July"
                        Elseif mo = "8" Then
                                    xlApp.cells(row,colmn+2) = "August"
                        Elseif mo = "9" Then
                                    xlApp.cells(row,colmn+2) = "September"
                        Elseif mo = "10" Then
                                    xlApp.cells(row,colmn+2) = "October"
                        Elseif mo = "11" Then
                                    xlApp.cells(row,colmn+2) = "November"
                        Elseif mo = "12" Then
                                    xlApp.cells(row,colmn+2) = "December"
                                    
                        End If
                        
                        If doc.form(0) = "UserIDConsultant" Then
                                    xlApp.cells(row,colmn+3) ="Software Consultant"
                        Else
                                    xlApp.cells(row,colmn+3) ="Employee"
                        End If
                        
                        xlApp.cells(row,colmn+4) = doc.UniqueNo(0)
                        xlApp.cells(row,colmn+5) = doc.Fname(0)+" "+doc.Familyname(0)
                        xlApp.cells(row,colmn+6) = doc.country(0)
                        xlApp.cells(row,colmn+7) = doc.Factory(0)
                        xlApp.cells(row,colmn+8) = doc.FrmType(0)
                        xlApp.cells(row,colmn+9) = doc.request(0)
                        xlApp.cells(row,colmn+10) = doc.requestedby(0)
                        xlApp.cells(row,colmn+11) = doc.createdon(0)
                        xlApp.cells(row,colmn+12) = doc.ReferenceNo(0)             
                        xlApp.cells(row,colmn+13) = doc.Department(0)               
                        xlApp.cells(row,colmn+14) = doc.FSO_app(0)                  
                        
                        
                        If doc.Admin_Date(0) = ""  Then
                                    xlApp.cells(row,colmn+15) = ""
                        Else
                                    xlApp.cells(row,colmn+15) = Cdat(doc.Admin_Date(0))
                        End If
                        
                        
                        row=row+1   
                        
                        Set doc = doccol.GetNextDocument(doc)
            Wend
            
            
'this freezes the panes 
            xlApp.application.Rows("2:2").Select
            xlApp.application.ActiveWindow.FreezePanes = True
            
'this autofits the columns
            xlApp.cells.select
            xlApp.selection.Columns.AutoFit 
            xlApp.application.rows("1:1").Select
            
            
            
            ' ************************Export to Excel ******************************
            
            
End Sub

Open in new window

0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

10 Experts available now in Live!

Get 1:1 Help Now