I got the following script to export category , however my efforts to modify it to my requirement is still futile currently. what I want is just for database to pick up categorised total values from a view, and paste it into a template, which I saved in my Page Element. i.e:
MyView: Jan Feb
Category 1 30 50
SubCateg1 10 40
SugCateg2 20 10
Category 2 30 60
*for category 2, I collapsed at the highest level.
I was made to understand that there is no way I can write a script to collapsed or expand at certain level, thus, my process will be follow:
-
collapse and expand relevant doument manually via clicking the twisties on the left hand side
-
click the button on the view,which will
a. go to MyView, get total values of Jan and Feb for Categ1, SubCateg1, subcateg2, and categ2,
b. go to page element, and select an excel attached file, and paste it to cell B2, C2, for Categ1, B3-B4 and C3-C4 to subcateg1-3. and B7-B8 for Categ2.
I hoped someone can guide me how do i modify the code below for above needs, currently, I keep getting object variable not defined ,altough I created "Keywords"view, and a document with first column displaying “Invoice Aging Template”. But i don’t understand what name of excel file should I attached, and whether any formating required.
thank you.
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
