Solved

Export to Excel

Posted on 2011-02-14
18
515 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
ID: 34894517
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
ID: 34895100
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
ID: 34902770
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

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

Expert Comment

by:mbonaci
ID: 34904483
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
ID: 34905047
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
ID: 34905153
Actually that is the problem, the requirement is to select the category instead of documents.
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 34905409
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
ID: 34905723
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
ID: 34912038
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
ID: 34914097
I'm curious, which complicated tricks?
0
 

Author Comment

by:Glactus
ID: 35026241
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
ID: 35026268
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
ID: 35026815
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
ID: 35034213
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Domino Smart Upgrade from Release 8.5.3 to 9.0.1 1 1,013
VBA Lotus notes 2 199
Event in Notes Form after open and visible 10 222
Lotus Notes: Email Delivery Failure 5 183
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 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