I’ve come across lotus script coding to export to Excel, however, I have a view, which I want to export data which is collapsed at certain level into excel. i.e I don’t want to export at the individual level.
Please help, I tried to search earlier in the forum, but so far is more of export at document level.
Subject: Any sample coding to export categorised view into excel?
You’ll just need to alter any code to look for categories as well as documents. I think the best way of doing this is to use a notesviewnavigator and loop through notesviewentries - take a look at the designer help.
Dan
Subject: RE: Any sample coding to export categorised view into excel?
Sub Initialize Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim wks As New NotesUIWorkspace
Dim uiView As NotesUIView
Dim column As NotesViewColumn
Dim doc As NotesDocument
Dim row As Long
Dim counter As Long
Dim colcounter As Integer
Dim filename As String
Dim xlApp As Variant
Dim xlsheet As Variant
Dim xlswb As Variant
Dim xlrange As Variant
Set db=session.currentdatabase
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True 'Excel program is visible (to avoid errors and see what is happening)
Set xlwb=xlApp.Workbooks.Add
Set xlsheet =xlwb.Worksheets(1)
Set uiView = wks.currentview
'Set view = db.GetView( uiView.ViewName ) ' get the view currently open in UI
Set view = db.getview("Incident") 'Get View from where you want to copy data
row=1
colcounter=0
Forall c In view.Columns
If c.isIcon<>True Then ' do not include icon columns
If c.Formula<>"""1""" And c.Formula<>"1" Then 'do not include columns which are counting docs (Total)
xlsheet.Cells(row,colcounter+1).Value = c.Title 'set Excel column titles
colcounter=colcounter+1
End If
End If
End Forall
row=2
counter = 0
x=0
Set doc = view.GetFirstDocument
While Not ( doc Is Nothing )
Forall c In view.Columns
If c.isIcon<>True Then ' do not include icon columns
If c.Formula<>"""1""" And c.Formula<>"1" Then 'do not include columns which are counting docs (Total)
currentvalue= doc.ColumnValues(counter)
xlsheet.Cells(row, x+1).Value = currentvalue 'set values to Excel table
x=x+1
End If
End If
counter = counter +1
End Forall
counter = 0
x=0
row=row+1
Set doc = view.GetNextDocument (doc)
Wend
Msgbox"Documents Copied to Excel ! "
'Msgbox "Done"
End Sub
Subject: RE: Any sample coding to export categorised view into excel?
tq Dan, I am sorry but I am quite lost on how to go about doing it… maybe I try to search some books, if there is any 
on ways to export to excel.
Subject: RE: Any sample coding to export categorised view into excel?
As you said in your first post, there’s plenty of postings about how to export to excel in this forum.
The only difference you need from what I can tell is that instead of looping through documents and exporting field values, you need to loop through notesviewentries and export columnvalues, including those that are categories for which you need a notesviewnavigator.
Is that right?
As an example of exporting to excel, this is a link to the sandbox with code:
Dan
Subject: RE: Any sample coding to export categorised view into excel?
Hi Dan,
I am Ravi here. In your code you are exporting data to an excel and finally you are using xlApp.visible = true to display the excel. Instead of displaying the excel, do you have any code available to store this excel object into a file path / as attachment to a document??
Looking for your response.
If any one has such a code your help is highly appreciated.
Thanks,
Ravi.
Subject: RE: Any sample coding to export categorised view into excel?
Here is an example:This agent gets a template from a lookupdoc then gets a categorized view and interates through the view getting values from the view (including totals from the categories).
Sub Initialize
On Error Goto endroutine
Dim s As New Notessession
Dim db As Notesdatabase
Set db = s.currentdatabase
Dim view As NotesView
Dim entry As NotesViewEntry
Dim nav As NotesViewNavigator
Dim doc As NotesDocument
Dim i As Integer
Dim irow As Integer
Dim icol As Integer
Dim startrow As Integer
Dim dtotal As Double
Dim gtotal As Double
Set View = db.GetView( "Keywords" )
Dim viewdoc As NotesDocument
Set viewdoc = view.getdocumentbyKey("Invoice Aging Template")
Dim object As NotesEmbeddedObject
filenames = Evaluate("@AttachmentNames", viewdoc)
numberoffiles = Evaluate("@Attachments", viewdoc)
Set object = viewdoc.GetAttachment( filenames(0) )
localdrive = Environ$("Temp") & "\"
Call object.ExtractFile(localdrive + filenames(0) )
Dim xlApp As Variant
Dim oworkbook As Variant
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set oworkbook = xlApp.Workbooks
oworkbook.Open(localdrive + filenames(0) )
xlApp.Sheets("Invoice Aging").Select
Set xlworksheet = xlApp.ActiveSheet
irow = 2
icol = 2
Set view = db.GetView("(Report:Invoice Aging)")
Set nav = view.CreateViewNav
Set entry = nav.GetFirst
startrow = irow
While Not(entry Is Nothing)
If entry.IsCategory Then 'new partner
If Not irow = startrow Then
With xlApp.cells(irow,icol+5)
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.Font.ColorIndex = 9
.HorizontalAlignment = -4152 'right
.Select
With xlApp.Selection.borders(8) '9=xlEdgeBottom, 7=xlEdgeLeft, 10=xlEdgeRight, 8=xlEdgeTop,
.LineStyle = -4119 '1 'xlDouble
.Weight = 4 'xlThick
.ColorIndex = -4105 'xlAutomatic
End With
End With
xlApp.cells(irow, icol+5) = "Total Outstanding"
With xlApp.cells(irow,icol+6)
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.Font.ColorIndex = 9
.HorizontalAlignment = -4152 'right
.Select
With xlApp.Selection.borders(8) '9=xlEdgeBottom, 7=xlEdgeLeft, 10=xlEdgeRight, 8=xlEdgeTop,
.LineStyle = -4119 '1 'xlDouble
.Weight = 4 'xlThick
.ColorIndex = -4105 'xlAutomatic
End With
End With
xlApp.cells(irow, icol+6) = dtotal
dtotal = 0
irow = irow + 2
xlApp.Range("A2:H3").Copy
xlApp.Range("A" & Cstr(irow) & ":" & "H" & Cstr(irow)).Select
xlApp.ActiveSheet.Paste
xlApp.Application.CutCopyMode = False
End If
gtotal = gtotal + entry.ColumnValues(0)
dtotal = dtotal + entry.ColumnValues(0)
xlApp.cells(irow, icol) = entry.ColumnValues(1)
Else 'detail row
For i = 0 To 6
xlApp.cells(irow, icol+i) = entry.ColumnValues(i+2)
Next
End If
irow = irow + 1
Set entry = nav.GetNext(entry)
Wend
irow = irow - 1
With xlApp.cells(irow,icol+5)
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.Font.ColorIndex = 9
.HorizontalAlignment = -4152 'right
.Select
With xlApp.Selection.borders(8) '9=xlEdgeBottom, 7=xlEdgeLeft, 10=xlEdgeRight, 8=xlEdgeTop,
.LineStyle = -4119 '1 'xlDouble
.Weight = 4 'xlThick
.ColorIndex = -4105 'xlAutomatic
End With
End With
xlApp.cells(irow, icol+5) = "Total Outstanding"
With xlApp.cells(irow,icol+6)
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.Font.ColorIndex = 9
.HorizontalAlignment = -4152 'right
.Select
With xlApp.Selection.borders(8) '9=xlEdgeBottom, 7=xlEdgeLeft, 10=xlEdgeRight, 8=xlEdgeTop,
.LineStyle = -4119 '1 'xlDouble
.Weight = 4 'xlThick
.ColorIndex = -4105 'xlAutomatic
End With
End With
xlApp.cells(irow, icol+6) = dtotal
irow = irow + 2
xlApp.Range("F" & Cstr(irow) & ":" & "G" & Cstr(irow)).Select
With xlApp.Selection
.HorizontalAlignment = -4152
.ShrinkToFit = False
.MergeCells = True
End With
With xlApp.cells(irow,icol+4)
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.Font.ColorIndex = 1
.HorizontalAlignment = -4152 'right
.Select
With xlApp.Selection.borders(8) '9=xlEdgeBottom, 7=xlEdgeLeft, 10=xlEdgeRight, 8=xlEdgeTop,
.LineStyle = -4119 '1 'xlContinuous
.Weight = 4 'xlThick
.ColorIndex = -4105 'xlAutomatic
End With
End With
With xlApp.cells(irow,icol+6)
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.Font.ColorIndex = 1
.HorizontalAlignment = -4152 'right
.Select
With xlApp.Selection.borders(8) '9=xlEdgeBottom, 7=xlEdgeLeft, 10=xlEdgeRight, 8=xlEdgeTop,
.LineStyle = -4119 '1 'xlContinuous
.Weight = 4 'xlThick
.ColorIndex = -4105 'xlAutomatic
End With
End With
xlApp.cells(irow, icol+4) = "Total Outstanding for All Alliance Partners"
xlApp.cells(irow, icol+6) = gtotal
alldone:
If Not (xlApp Is Nothing) Then
xlApp.ActiveWorkbook.saved = True
xlApp.Range("A1").Select
xlApp.Visible = True
End If
Exit Sub
endroutine:
If Not (xlApp Is Nothing) Then
xlApp.Quit
Set xlApp = Nothing
End If
Resume alldone
End Sub
Subject: Tq Stan/ Dan
Tq Dan/ Stan, really appreciates your link. I hoped one day I’ll be able to really understand how does lotus script coding works. Right now, I just learn from example. I hoped someone will write a book on how to export to excel/ excel chart/ powerpoint, surely it will be a big hit! :). There are just too many declarations involved, I always got lose halfway.
Tq
Subject: RE: Any sample coding to export categorised view into excel?
i did tried to email to you, but your name no longer exist in the domino directory.
hv nice holiday
Subject: RE: Any sample coding to export categorised view into excel?
tq Dan, i am sorry, i tried it just now, but I get error message, object not found.
I put the coding into an agent, under initialise. than run it from the menu, after opened up my view. ( i even selected the document).
really greatful for your help.
tq.
Subject: RE: Any sample coding to export categorised view into excel?
Will,Click on my name and send me an email with your contact information.
Exporting to excel is really a simple process, however by your most recent reply I’m not sure you know a couple building blocks these things rest upon.
I can touch base with you and get these items cleared up in just a few minutes.
In case I don’t hear from you:
This creates and opens an excel workbook:
Dim xlApp As Variant
Dim oWorkbook As Variant
Set xlApp = CreateObject(“Excel.Application”)
xlApp.Visible = True 'without this you can’t see it’s running
Set oworkbook = xlApp.Workbooks 'creates instance
oworkbook.Add
This one opens an existing workbook, which I think is better because you can have a blank one ready to use, which already has the font colors and/or headers… dates or whatever… so you only need to populate it from your lookup(s).
Dim xlApp As Variant
Dim oWorkbook As Variant
Set xlApp = CreateObject(“Excel.Application”)
xlApp.Visible = True 'without this you can’t see it’s running
Set oworkbook = xlApp.Workbooks 'creates instance
oworkbook.Open(“AM_Template.xls”) '(your template)
Of course if you want to ‘browse’ to open it, well that’s easy enough to include, but if you’re going to use a template to make things easier, you don’t want to make the user go find a template… so if you have the template attached to a document in your database, then you only need to find that document, remove the attachment to a known spot THEN run the code above that uses it… and that is what I included in my example the other day… a bit too much at once I suspect.
Couple other tricks:
This is how you put something into a cell:
xlApp.cells(1,2).Value = “123.45” 'row 1, col 2
So if you use LotusScript to open a view:
Dim s As New NotesSession
Dim db As NotesDatabase
Set db = s.currentdatabase
Dim viewdoc As NotesDocument
Dim view As NotesView
Set view = db.GetView( “SalesView” )
Then go through each row in the view and get a salesAmount
Set viewdoc = view.GetFirstDocument
While Not ( viewdoc Is Nothing )
Salesperson= viewdoc.GetItemValue( “First_Name” )(0)
Set viewdoc = view.getnextdocument(viewdoc)
Wend
You could just as easily populate the cells in the workbook if you increment the row number (irow = irow + 1):
xlApp.cells(irow,2).Value = Salesperson
So let’s put it all together (if you want to see this work, you only need to change the view’s name to one in your database (uncategorized view) and change the fieldname to one that exists in your document.
Dim irow as Integer
Dim Salesperson as String
Dim xlApp As Variant
Dim oWorkbook As Variant
Set xlApp = CreateObject(“Excel.Application”)
xlApp.Visible = True 'without this you can’t see it’s running
Set oworkbook = xlApp.Workbooks
oworkbook.Add
Dim s As New NotesSession
Dim db As NotesDatabase
Set db = s.currentdatabase
Dim viewdoc As NotesDocument
Dim view As NotesView
Set view = db.GetView( “Sales” )
view.AutoUpdate = False
Set viewdoc = view.GetFirstDocument
irow = 2
While Not ( viewdoc Is Nothing )
Salesperson = viewdoc.GetItemValue( “First_Name” )(0)
xlApp.cells(irow,2).Value = Salesperson
irow = irow + 1
Set viewdoc = view.getnextdocument(viewdoc)
Wend
view.AutoUpdate = True
Create an Agent in your database with these properties:
Trigger: On Event
Action menu selection
Target: None
When you run the agent it will go thorough every document in your view and get the First_Name field and put in into the excel worksheet. I didn’t include any error handling, so if there is an error you’ll need to do something… oh, whatever field you decide to use, make sure it exists in every document … maybe use that as part of your view’s selection criteria: First_Name != “”
Enjoy