Programatically export only categorized rows to spreadsheet

This can be achieved using NotesViewNavigator and NotesViewEntry (IsCategory property).

Walk through the view using navigator , get entry and check if it is a category, then export that row into a view.

~Hemanth

Well, easy enough to convert a standard excel export to do view navigator and columnentries.  Here's a quick adaptation of my standard excel export using view navigators.

Now this one loops through all the columns, and doesn't skip any.. which means if you have columns that are not rolled up into the category totals, the column will be there but not a value.  Also, this has some automatic formatting based on the value of the columns, so if the column value type is number, it will format a number.  If it's a date it will format a date.

Agent Information
Name:      Export View Categories to Excel
Type:      LotusScript
State:      Enabled
Trigger:      Manually From Actions Menu
Acts On:      All documents in view
LotusScript Code:
Option Public
Option Declare
Dim session As Variant
Dim ws As Variant
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim view As NotesView
Dim vname As String
Dim vcols As Variant
Dim uvcols As Integer
Dim rows, cols, maxcols, x As Integer
Dim result As Variant
Dim trackCols() As String
Dim theTitles() As String
Dim viewName As String
Dim errorFLAG As Integer
Dim xlApp As Variant
Dim xlSheet As Variant
Dim excelTitle As String
Dim excelCompany As String
Dim continue As Variant
Const xlWorkbookNormal = -4143
Const xlEdgeBottom = 9
Const xlContinuous = 1
Const xlThin = 2
Const xlEdgeTop = 8
Const xlThick = 4
Const fontName="Arial"
Dim cRGBCream As Long, cRGBBlue As Long, cRGBBlack As Long,cRGBRED As Long, cRGBYellow As Long
Dim cRGBGrey As Long,cRGBLTBlue As Long,cRGBMedBlue As Long
Const fmtGeneral="General"
Const fmtCurrency="#,##0;[Red](#,##0)"
Const fmtNumber="#,##0;[Red](#,##0)"
Const fmtPercent="#,##0.0%;[Red](#,##0.0%)"
Const fmtGM="#,##0.00;[Red](#,##0.00)"
Const fmtCustID="00000000"
Const fmtMarkup="#,#0.00;[Red]-#,##0.00"
Sub Initialize
      'Export Entire View Documents Agent
      On Error Goto ErrorHandler      
      Continue = True
      Set session = New NotesSession      
      Set db = session.currentdatabase
      Set ws =New NotesUIworkspace      
      Dim view As NotesView      
      Dim uiview As NotesUIView
      Set uiview = ws.currentview
      Dim numdocs As Integer
      Dim platform As String
      Dim thisTitle As String
      viewname = ws.currentview.viewname
      Set view = db.getView(viewName)
      If view Is Nothing Then
            Msgbox "Sorry, there was an error loading this view. " + Chr(10) + "Please contact the database administrator",,"Error loading view"
            Exit Sub
      End If
      'Creating colors for format
      cRGBCream= makeRGB(255,224,191)
      cRGBBlue=makeRGB(0,0,80)
      cRGBBlack=makeRGB(0,0,0)
      cRGBRed = makeRGB(208,0,0)
      cRGBGrey=makeRGB(240,240,240)
      cRGBYellow=makeRGB(255,255,192)
      cRGBLtBlue=makeRGB(224,255,255)
      cRGBMedBlue = makeRGB(0,0,255)      
      'Check to see if the user is on a MacIntosh
      'the "Create Object" function does not run on a Mac
      platform = session.Platform
      If Not Instr (platform, "MacIntosh") = 0 Then
            Messagebox ("This function cannot be run on a MacIntosh. Please use a PC to pull this data into a spreadsheet.")
            Exit Sub
      End If
      Dim nav As NotesViewNavigator
      Dim entry As NotesViewEntry
      Dim colEntries As Variant
     'Get all selected docs from the view or the entire view
      numdocs = view.allentries.count      
      Dim i As Long
      Dim j As Long
     'Get the number of columns in the view, and store the column headings in an array
      vcols = view.Columns
      uvcols = Ubound(vcols)
      Redim theTitles(uvCols+1) As String      
     'Create the Excel spreadsheet, hide it from the UI, and add a new workbook
      Set xlApp = CreateObject("Excel.Application")
      xlApp.Visible = False 'hide Excel while processing
      xlApp.Workbooks.Add
     'Set the Excel row and column heading to R1C1 reference style so that we can loop with numbers instead of letters
      xlApp.ReferenceStyle = 2
      Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
      xlSheet.Name = "Lotus Export"
      rows = 1
      cols = 1
     'Enter the view column headings into the spreadsheet, skipping hidden columns and columns without titles.
     'Rows stay constant at 1 while columns is incremented by 1 for each view column
      With xlSheet
            For x = 0 To uvcols                  
                  If vcols(x).IsHidden = False Then
                        If vcols(x).Title = "" Then
                              .Cells(rows, cols).Value = "COL " + Cstr(cols)
                              theTitles(cols) = "COL " + Cstr(cols)
                              cols = cols + 1
                        Else
                              .Cells(rows, cols).Value = Trim(vcols(x).Title)
                              theTitles(cols) = Ucase(Trim(vcols(x).Title))
                              cols = cols + 1
                        End If
                  End If                        
            Next    
            maxcols = cols - 1
            Redim trackCols(maxCols)
            'Export Categories to excel worksheet
            Set nav = view.CreateViewNav
            Set entry = nav.GetFirst            
            rows = 2
            cols = 1
            Print "Exporting category documents to Excel..."
            While Not entry Is Nothing
                  If entry.isCategory Then      
                        colEntries = entry.ColumnValues
                        Forall ce In colEntries
                              If ce <> "" Then
                                    .Cells(rows, cols).Value = ce                              
                              Else
                                    .Cells(rows, cols).Value = ""
                              End If                                          
                              'Always increment to be consistent with loop for col titles                                    
                              cols = cols + 1      
                        End Forall
                        rows = rows + 1
                        cols = 1
                  End If
                  Set entry = nav.GetNextCategory(entry)
            Wend    
      End With      
      Set doc = Nothing
      Set view = Nothing
      On Error Goto ExcelFormatError
      Print "Formatting Excel Spreadsheet..."
      Call formatSpreadsheet
ExitHere:      
     'Release Excel app and workbook if there is an error, otherwise memory will get clogged with versions of Excel running
      If Not Continue Then
            xlApp.Visible = False
            xlApp.Workbooks(1).close(False)            
            xlApp.quit                  
      Else
            If Not xlapp Is Nothing Then
                  xlApp.Visible = True 'Show Excel in the UI when done processing
            End If
      End If
      Set xlApp = Nothing
      Set xlSheet = Nothing      
      Exit Sub
ErrorHandler:
      Msgbox  "Error #" & Format$(Err) & " at line " & Format$(Erl) & ": " & Error$, 0, "Error Processing Excel Export"    
      Continue = False
      If Not xlapp Is Nothing Then              
           'Release Excel app and workbook if there is an error, otherwise memory will get clogged with versions of Excel running
            xlApp.Workbooks(1).close(False)      
            xlapp.quit
            Set xlApp = Nothing
            Set xlSheet = Nothing
      End If      
      Exit Sub    
ExcelFormatError:
      Msgbox  "There was a general excel export error: " + Error$ + "-" + Str(Err), 0, "Error Processing Excel Export"  
      Continue = False
      If Not xlapp Is Nothing Then              
          'Release Excel app and workbook if there is an error, otherwise memory will get clogged with versions of Excel running
            xlApp.Workbooks(1).close(False)      
            xlapp.quit
            Set xlApp = Nothing
            Set xlSheet = Nothing
      End If      
      Exit Sub    
End Sub
Sub Terminate
End Sub
Sub FormatSpreadsheet
      'Format the appearance of the Excel spreadsheet:
     'Select all cells and format
      'General Cell Format
      xlApp.Range(xlSheet.Cells(1,1), xlsheet.Cells(rows+2,maxcols)).Select
      xlApp.Selection.Font.Name = "Arial"
      xlApp.Selection.Font.Size = 8
      xlApp.Selection.Font.Color = cRGBBlack
      xlApp.Selection.Columns.AutoFit
      xlApp.Selection.RowHeight = 13
      'Top Row
      xlApp.Range(xlSheet.Cells(1,1), xlSheet.Cells(1, maxcols)).Select      
      xlApp.Selection.Font.Bold = True
      xlApp.Selection.Font.Name = "Arial"
      xlApp.Selection.Font.Size = 9
      xlApp.Selection.Font.Color = cRGBBlack
      xlApp.Selection.RowHeight = 15
      xlApp.Selection.Interior.color = cRGBGrey
      With xlApp.Selection.Borders(xlEdgeBottom)
            .color =  cRGBBlack            
            .LineStyle = xlContinuous
            .Weight = xlThin
      End With
      With xlApp.Selection.Borders(xlEdgeBottom)            
            .LineStyle = xlContinuous
            .Weight = xlThin
            .color =  cRGBBlack            
      End With
      With xlApp.Selection.Borders(xlEdgeTop)            
            .LineStyle = xlContinuous
            .Weight = xlThick
            .color =  cRGBBlack            
      End With
      'format the NUMERIC Columns using the TrackCols array
      Dim thisFormat As String
      For x = Lbound(theTitles) To Ubound(theTitles)
            thisFormat = ""
            Select Case Ucase(TrackCols(x))
            Case "NUMBER"
                  thisFormat = fmtNumber
            Case "ID"
                  thisFormat = fmtCustID
            Case Else
                  'do nothing                  
            End Select
            If thisFormat <>"" Then
                  xlApp.Range(xlSheet.Cells(2,x), xlsheet.Cells(rows,x)).NumberFormat = thisFormat
            End If
      Next      
      Dim sumString As String
      'Format the column numbers      
      For x = Lbound(theTitles) To Ubound(theTitles)
            thisFormat = ""
            sumString = {R} + Cstr(2) + {C} + Cstr(x) + ":" + {R} + Cstr(rows-1) + {C} + Cstr(x)
            Select Case Ucase(trackCols(x))
            Case "NUMBER"
                  xlApp.Cells(rows,x).formula={=SUM(} + sumString + {)}
                  xlApp.Range(xlSheet.Cells(2,x), xlsheet.Cells(rows,x)).NumberFormat = fmtNumber
            Case Else
                  'do nothing
            End Select            
      Next
      With xlApp.Worksheets(1).PageSetup
            .Orientation = 2            
            If excelTitle= "" Then
                  .CenterHeader =viewname + " Report - Confidential"
            Else
                  .CenterHeader = excelTitle + " - Confidential"
            End If
            .RightFooter =  "&R Page &P of &N"
            .LeftFooter = Format(Now(),"mm/dd/yyyy hh:mm AM/PM")
            If excelCompany = "" Then
                  .CenterFooter = "Export from Lotus Notes by" + session.commonusername
            Else
                  .CenterFooter = "&B" + excelCompany + "&B"
            End If
            '.PrintArea =myRange
            .PrintTitleRows = xlApp.Rows(1).Address
            .LeftMargin = xlApp.InchesToPoints(0.35)
            .RightMargin = xlApp.InchesToPoints(0.35)
            .TopMargin = xlApp.InchesToPoints(1)
            .BottomMargin = xlApp.InchesToPoints(1)
            .HeaderMargin = xlApp.InchesToPoints(0.5)
            .FooterMargin = xlApp.InchesToPoints(0.5)            
      End With
      Stop
        'Set the Excel row and col heading back to the AI Reference Style where the cols are lettered and the rows are numbered
      xlApp.ReferenceStyle = 1      
      xlApp.Range("B:B").Select  
      With xlapp.selection
            .NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
            .Font.Name = "Arial"
            .Font.Size = 8
            .Font.FontStyle = "Bold"
            .Font.Color = cRGBMedBlue
      End With
End Sub
Function MakeRGB(Red As Integer ,Green As Integer, Blue As Integer) As Long
      MakeRGB = (((&H100& * Green) Or Red) Or (&H10000 * Blue))
End Function
      

Hey Hemantha, welcome back!

Sorry, I was busy testing my solution when you posted, Hemantha.   So I didn't see your comment.  But that's basically what I did.

Thanks, marilyng. Your solution worked exquisitely.

I assume no one basically bothered to follow the links I posted? Plenty NotesViewNavigators and Entries in there...

sjef, I did check those out, and, yes, there was a view nav example, and an export example.  But nothing to excel, which is why I posted a sub from my excel export script library.

Excel? Now who said Excel??  ;-)