I have a view export that only exports the categories and totals.
Mu user wants the totals BELOW the data as opposed to above. Any thoughts?
I tried using the subtotal function of Excel to do the word but it’s not working as I’d prefer… I want the same export but the category below the data.
Line JAN FEB MAR Q1
line1 266.30 289.85 478.04 1,034.19
line2 363.27 378.64 370.93 1,112.84
test 4,080.21 4,264.25 4,371.96 12,716.42
++++++++++++++++++++++++++++++
here’s the code:
On Error Goto errtrap
Dim session As New NotesSession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim view As NotesView
Dim vc As NotesViewEntryCollection
Dim entry As NotesViewEntry
Dim doc As NotesDocument
Dim x As Integer ' used to count the number of the selected document
Dim y As Integer ' used to count which column is being checked to see if it is hidden
Dim cp As Integer ' used to count the offset position of the columns, i.e. which column should be filled in in Excel
Dim cols As Variant
Dim xlApp As Variant, xlWb As Variant, xlWs As Variant
Dim eval As Variant
x=1
cp=0
Set db = session.CurrentDatabase
’ Get a handle on the required Excel Objects
Set xlApp = CreateObject("excel.application")
If xlApp Is Nothing Then
Msgbox "Error!!! Agent was unable to open Microsoft Excel.",16,"Error"
Exit Sub
End If
'Set xlApp = CreateObject("Excel.Application")
xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
xlApp.Visible = True
xlApp.Workbooks.Add
xlApp.ReferenceStyle = 2
’ xlApp.Workbooks(1).WorkSheets.Add
’ xlApp.Cells.Select
’ xlApp.Selection.NumberFormat = “@”
’ Set xlsheet = xlApp.WorkSheets.Add()
Set view = db.GetView("AllInvoicesExport")
Set vc = view.AllEntries ' gets a handle on all the documents in the folder
eval=Evaluate({@unique(@DbColumn( "" : "nocache" ; "" : "" ; "} & view.Name & {" ; 1 ))})
Dim viewNav As NotesViewNavigator
Dim viewEntry As NotesViewEntry
’ Set viewNav = view.CreateViewNav
’ Set viewEntry = viewNav.GetFirst
cols=view.Columns
n=0
Forall ExpCode In eval
n=n+1
cp = 0
x=1
Set viewnav = view.CreateViewNavFromCategory(ExpCode)
Set viewEntry = viewNav.GetFirst
’ Set viewentry = viewnav.GetFirstDocument
If n > 3 Then
Set after = xlapp.workbooks(1).worksheets(n-1)
Set xlsheet = xlapp.activeworkbook.worksheets.Add(Null,after)
Else
Set xlsheet = xlApp.Workbooks(1).Worksheets(n)
End If
If xlsheet Is Nothing Then
xlApp.Workbooks(1).WorkSheets.Add
End If
xlsheet.Name = ExpCode
’ Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
xlApp.Cells.Select
xlApp.Selection.NumberFormat = "@"
cp=0 ' reset the column position
y = 0 ' reset the count for check for hidden columns
’ Add view column titles
Forall columns In view.Columns
If columns.position>1 Then
If columns.ishidden = False Then
If columns.isicon = False Then
xlsheet.Range("A1").OffSet(0,cp).value= Fulltrim(columns.Title)
cp=cp+1
End If
End If
End If
y = y +1
End Forall
x=1
While Not ( viewEntry Is Nothing )
If viewEntry.IsCategory = True Then
cp=0 ' reset the column position
y = 0 ' reset the count for check for hidden columns
Forall columns In viewentry.ColumnValues
If y>0 Then
If cols(y).ishidden = False Then
If cols(y).isicon = False Then
' If viewentry.indentlevel=1 Then
' If cp=0 Then
' xlsheet.Range("A1").OffSet(x,cp).value=columns
' Exit Forall
' End If
' Else
xlsheet.Range("A1").OffSet(x,cp).value=columns
' End If
cp= cp + 1
End If
End If
End If
y = y +1
End Forall
x=x+1
End If
Set viewEntry = viewNav.GetNext ( viewEntry )
Wend
End Forall
xlApp.StatusBar = "Formatting sheets. Please be patient..."
Print "Formatting sheets. Please be patient..."
’ make pretty
Stop
Forall sheet In xlApp.Worksheets
Call sheet.Activate
xlApp.Rows("1:1").Select
With xlapp.selection
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
End With
xlApp.Cells.Select
xlApp.Selection.Font.Name = "Arial"
xlApp.Selection.Font.Size = 8
xlApp.Range("C:Z").Select
xlApp.Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
' xlApp.Cells.Select
' xlApp.Selection.Columns.AutoFit
’ Sort
xlApp.Range("A1").Select
' Set vRange=xlApp.Range("A2")
'Call vRange.Sort(xlsheet.Columns("A"), , , , , , , 1)
’ Subtotal
’ Set vRange=xlApp.Range(“A2”)
'expression.Subtotal(GroupBy, Function, TotalList, Replace, PageBreaks, SummaryBelowData)
’ Call vRange.subtotal (1,-4157,19,1,0,1)
’ Collapse
’ xlApp.ActiveSheet.Outline.ShowLevels 2
xlApp.ReferenceStyle = 1
xlApp.Cells.Select
xlApp.Selection.Columns.AutoFit
End Forall
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
Call xlsheet.Activate
xlApp.Range("A1").Select
Print "Importing Data from Lotus Notes Application was Completed."
xlApp.StatusBar = "Importing Data from Lotus Notes Application was Completed."
xlApp.Visible = True
Exit Sub
errtrap:
ERRtxt= "The following error occurred while running : Error" & Str(Err) & ": " & Error$
Print ERRtxt
Msgbox ERRtx
xlApp.Visible = True
Exit Sub
End Sub