Export Notes view to Excel – with multi-value fields

Posted on by Posted in Lotusscript, Notes/Domino, Programming 36 Comments

A few days ago, a question was asked on StackOverflow about how to export the content of a Notes view to Excel. The caveat was that some columns contained multiple values, but not on all documents.

To solve this, I wrote a Lotusscript class that will export view data as either CSV or as an HTML table, both can then be saved to a file and opened in Excel. I am posting the code below. Enjoy!

 

%REM
    Agent View Export
    Created Mar 27, 2013 by Karl-Henry Martinsson
    Description: Code to export a specified view as CSV.
    Copyright (c) 2013 by Karl-Henry Martinsson
    This code is distributed under the terms of 
    the Apache Licence Version 2. 
    See http://www.apache.org/licenses/LICENSE-2.0.txt
%END REM

Option Public
Option Declare

Class RowData
    Public column List As String

    Public Sub New()
    End Sub

    Public Sub SetColumnHeader(view As NotesView)
        Dim viewcolumn As NotesViewColumn
        Dim cnt As Integer
        ForAll vc In view.Columns
            Set viewcolumn = vc
            column(CStr(cnt)) = viewcolumn.Title 
            cnt = cnt + 1
        End Forall  
    End Sub

    Public Sub SetColumnValues(values As Variant)
        Dim cnt As Integer
        Dim tmp As String 
        ForAll v In values
            If IsArray(v) Then
                ForAll c In v
                    tmp = tmp + c + Chr$(13)
                End ForAll
                column(CStr(cnt)) = Left$(tmp,Len(tmp)-1)
            Else
                column(CStr(cnt)) = v 
            End If
            cnt = cnt + 1
        End ForAll          
    End Sub
End Class

Class CSVData
    Private row List As RowData
    Private rowcnt As Long

    %REM
        Function New
        Description: Open the view and read view data 
        into a list of RowData objects.
    %END REM    
    Public Sub New(server As String, database As String, viewname As String)
        Dim db As NotesDatabase
        Dim view As NotesView
        Dim col As NotesViewEntryCollection
        Dim entry As NotesViewEntry
        Dim colcnt As Integer

        Set db = New NotesDatabase(server, database)
        If db Is Nothing Then
            MsgBox "Could not open " + database + " on " + server,16,"Error" 
            Exit Sub
        End If
        Set view = db.GetView(viewname)
        If view Is Nothing Then
            MsgBox "Could not access view " + viewname + ".",16,"Error" 
            Exit Sub
        End If
        Set col = view.AllEntries()
        rowcnt = 0
        Set entry = col.GetFirstEntry()
        Set row("Header") = New RowData()
        Call row("Header").SetColumnHeader(view)
        Do Until entry Is Nothing
            rowcnt = rowcnt + 1
            Set row(CStr(rowcnt)) = New RowData()
            Call row(CStr(rowcnt)).SetColumnValues(entry.ColumnValues)
            Set entry = col.GetNextEntry(entry) 
        Loop
    End Sub

    %REM
        Function CSVArray
        Description: Returns a string array of CSV data by row
    %END REM
    Public Function CSVArray() As Variant
        Dim rowarray() As String 
        Dim textrow As String
        Dim cnt As Long
        ReDim rowarray(rowcnt) As String

        ForAll r In row
            textrow = ""
            ForAll h In r.column 
                textrow = textrow + |"| + Replace(h,Chr$(13),"\n") + |",|
            End ForAll
            rowarray(cnt) = Left$(textrow,Len(textrow)-1)
            cnt = cnt + 1
        End ForAll  
        CSVArray = rowarray
    End Function

    %REM
        Function HTMLArray
        Description: Returns a string array of HTML data by row
    %END REM
Public Function HTMLArray() As Variant
        Dim rowarray() As String 
        Dim textrow As String
        Dim cnt As Long
        ReDim rowarray(rowcnt) As String

        ForAll r In row
            textrow = ""
            ForAll h In r.column 
                textrow = textrow + |<td>| + Replace(h,Chr$(13),"<br>") + |</td>|
            End ForAll
            rowarray(cnt) = "<tr>" + textrow + "</tr>"
            cnt = cnt + 1
        End ForAll  
        HTMLArray = rowarray
    End Function

End Class

Here is an example of how to use the class:

Sub Initialize
    Dim csv As CSVData
    Dim outfile As String

    Set csv = New CSVData("DominoServer/YourDomain", "names.nsf", "People\By Last Name")
    '*** Create CSV file from view
    outfile = "c:\ExcelExportTest.csv"
    Open outfile For Output As #1
    ForAll row In csv.CSVArray()
        Print #1, row
    End ForAll
    Close #1
    '*** Create HTML table and save as .xls to open in Excel
    outfile = "c:\ExcelExportTest.xls"
    Open outfile For Output As #2
    Print #2, "<table>"
    ForAll row In csv.HTMLArray()
        Print #2, row
    End ForAll
    Print #2, "</table>"
    Close #2
End Sub

36 thoughts on “Export Notes view to Excel – with multi-value fields”

  1. Hi Karl,

    Really good code that is.

    But this code SAVE excel/ csv in user local machine.Can u please tell us how to open this file while user click on that agent? (We call this agent on button given to user – Notes/Web)

    Reply

    • It would work totally different on the web and in the Notes client.

      On the web you can use the fact that a HTML table can be sent with a mime-type of “application/vnd.ms-excel”, that will make the browser open it in Excel (except for the latest version of Excel, where you get a security warning).
      Don’t save the file to disk, use the print statement to send the data to the browser.

      In the client you have to create a HTML table as well, but this time save it as a file with an extension of .xls. Then you have to shell to the operating system, and call “cmd /c start /wait filename.xls” (in Windows).

      Reply

  3. Set csv = New CSVData(“DominoServer/YourDomain”, “names.nsf”, “People\By Last Name”)

    Hi admin,

    Would like to ask how to setup above code to work on domino server?

    Reply

    • I am a developer, not an admin. ;-)

      I am not sure what you are asking… The code does work on a Domino server, in scheduled agents or anywhere you like, since it is not using any UI classes.

      Sre you asking how you create a server-based agent that uses my script library to export data?
      Or are you asking how you modify the quoted line of code to work against your server?
      Can you be more specific?

      Reply

  4. hi karl,
    it works for me, but after running several times i get an error “unable to open the file” . i have read some articles about the error regarding the path file and I have created the folder but still does not solve the issue. any advise on this?

    Reply

      • hi karl,
        tq for ur reply, now i know what had caused the error. this is because my formatting date on the file name.
        however i’m getting an error “overflow” when i try to export the data around 31 000 ++ rows.
        any advise on this

        Reply

        • Again, learn use the debugger.
          If you ask for help, you should show the relevant part(s) of your code, as well as indicate what line of code is causing the error.
          Otherwise I can just guess at what yoru code looks like. Based on your statement “around 31000 rows” (if you used a counter and either displayed the counter in the status bar or used the debugger, you would know the exact number´), my guess would be that you declared an variable/counter as integer but the value excedes 32,767…
          As you can see, in my code I am not using integers, exactly because of this reason.

          Reply

  5. in Set csv = New CSVData(“DominoServer/YourDomain”, “names.nsf”, “People\By Last Name”)
    what does “People\By Last Name” is for?

    Reply

  6. Great code. But it doesn’t work if any field value has quote. e.g “Name” , it puts Name”” in csv file.

    Reply

  7. Hi Karl, having recently spent a great deal of time (some might say “too much time”) writing a generic plug-and-play Export to Excel utility I noticed a couple of things that might trip you up. You’re using NotesView.Columns to derive the column headers and NotesViewEntry.ColumnValues to then write out the row data. There won’t always be a 1-1 correlation between the two. NotesView.Columns will include all columns in the view; NotesViewEntry.ColumnValues will only include columns for a constant if it’s used in a categorized column (THAT was a surprise) but will otherwise exclude constants (such as columns used for spacing or totalling) & columns with things such as @DocNumber. I like the simplicity of your solution & although I don’t think I’ll be trading in mine anytime soon, it’s always thought provoking to see how other people approach the same challenge. Thanks!

    Reply

  8. Hi Sir…First of all thanks a lot for this great code. I’ve only issue that all the data is coming in one single row in CSV. How to fetch all the person document data in separate rows in CSV?

    I need to export all the person document data in separate rows in csv just like exporting manually.

    I’m not a developer & I got this project to do. Please respond ASAP if you can.

    Thanks in Advance…

    Reply

  9. you are a legend. good code. used it for where the export from a view with multivalue rows don’t export the multivalues. got me out of trouble.

    Reply

    • Sure, that’s not hard. Change the constructor from
      Public Sub New(server As String, database As String, viewname As String)
      to
      Public Sub New(col As NotesViewEntryCollection)
      Next you remove all the lines until and including Set col = view.AllEntries().

      When you create the object, you just pass the NotesViewEntryCollection containing the selected documents into the contructor. That should work.

      Reply

      • Karl

        Thank you for the response.

        But I am now getting a “Type Mismatch” error.

        Is that because of the document collection?

        Reply

        • Yes, I forgot to mention that you need to remove all references to those removed variables. I have updated my comment above with that information, as well as fixed some typos.
          Also, you need to pass a NotesViewEntryCollection into the function (since you want to process the VioewEntries), not NotesDocumentCollection as I initially said. I have updated that as well.

          Reply

          • Karl

            I am still getting type mismatch with the following code for “Public Sub New”

            %END REM
            Public Sub New(col As NotesViewEntryCollection)

            Dim db As NotesDatabase
            Dim view As NotesView
            ” Dim col As NotesViewEntryCollection
            Dim entry As NotesViewEntry
            Dim colcnt As Integer

            rowcnt = 0
            Set entry = col.GetFirstEntry()
            Set row(“Header”) = New RowData()
            Call row(“Header”).SetColumnHeader(view)
            Do Until entry Is Nothing
            rowcnt = rowcnt + 1
            Set row(Cstr(rowcnt)) = New RowData()
            Call row(Cstr(rowcnt)).SetColumnValues(entry.ColumnValues)
            Set entry = col.GetNextEntry(entry)
            Loop

            End Sub

            %REM

          • What line are you getting the error on? You should be able to see in the debugger what line it is and what the value is where you are getting “type mismatch”.
            It might be as simple as you having to change colcnt to rowcnt in the declaration.

          • Karl

            Getting the Type mismatch on the “Set csv”

            Set csv = New CSVData(“DEVL1/MFF”, “AdPage.nsf”, “EventsTesting99”)

          • You did not change how you call the constructor.
            From my first comment:
            When you create the object, you just pass the NotesViewEntryCollection containing the selected documents into the contructor.

            So you have to change the way you instantiate the csv object:
            Set csv = New CSVData(viewentrycol)
            where viewentrycol is the NotesViewEntryCollection where you have your selected docuents.

          • Karl

            I am Still getting Type Mismatch after adding the set csv to the Public Sub New

            complete code below:

            %REM
            Agent View Export

            %END REM

            Class RowData
            Public column List As String

            Public Sub New()
            End Sub

            Public Sub SetColumnHeader(view As NotesView)
            Dim viewcolumn As NotesViewColumn
            Dim cnt As Integer
            Forall vc In view.Columns
            Set viewcolumn = vc
            column(Cstr(cnt)) = viewcolumn.Title
            cnt = cnt + 1
            End Forall
            End Sub

            Public Sub SetColumnValues(values As Variant)
            Dim cnt As Integer
            Dim tmp As String
            Forall v In values
            If Isarray(v) Then
            Forall c In v
            tmp = tmp + c + Chr$(13)
            End Forall
            column(Cstr(cnt)) = Left$(tmp,Len(tmp)-1)
            Else
            column(Cstr(cnt)) = v
            End If
            cnt = cnt + 1
            End Forall
            End Sub
            End Class

            Class CSVData
            Private row List As RowData
            Private rowcnt As Long

            %REM
            Function New
            Description: Open the view and read view data
            into a list of RowData objects.
            %END REM
            Public Sub New(col As NotesViewEntryCollection)

            Dim db As NotesDatabase
            Dim view As NotesView
            ” Dim col As NotesViewEntryCollection ”
            Dim entry As NotesViewEntry
            Dim colcnt As Integer

            Dim csv As String
            Set csv = New CSVData(col)

            rowcnt = 0
            Set entry = col.GetFirstEntry()
            Set row(“Header”) = New RowData()
            Call row(“Header”).SetColumnHeader(view)
            Do Until entry Is Nothing
            rowcnt = rowcnt + 1
            Set row(Cstr(rowcnt)) = New RowData()
            Call row(Cstr(rowcnt)).SetColumnValues(entry.ColumnValues)
            Set entry = col.GetNextEntry(entry)

            Loop

            End Sub

            %REM
            Function CSVArray
            Description: Returns a string array of CSV data by row
            %END REM
            Public Function CSVArray() As Variant
            Dim rowarray() As String
            Dim textrow As String
            Dim cnt As Long
            Redim rowarray(rowcnt) As String

            Forall r In row
            textrow = “”
            Forall h In r.column
            textrow = textrow + |”| + Replace(h,Chr$(13),”\n”) + |”,|
            End Forall
            rowarray(cnt) = Left$(textrow,Len(textrow)-1)
            cnt = cnt + 1
            End Forall
            CSVArray = rowarray
            End Function

            %REM
            Function HTMLArray
            Description: Returns a string array of HTML data by row
            %END REM
            Public Function HTMLArray() As Variant
            Dim rowarray() As String
            Dim textrow As String
            Dim cnt As Long
            Redim rowarray(rowcnt) As String

            Forall r In row
            textrow = “”
            Forall h In r.column
            textrow = textrow + || + Replace(h,Chr$(13),””) + ||
            End Forall
            rowarray(cnt) = “” + textrow + “”
            cnt = cnt + 1
            End Forall
            HTMLArray = rowarray
            End Function

            End Class

            Sub Click(Source As Button)
            Dim csv As CSVData
            Dim outfile As String

            ” Set csv = New CSVData(“DominoServer/YourDomain”, “names.nsf”, “People\By Last Name”)

            Set csv = New CSVData(“DEVL1/MFF”, “AdPage.nsf”, “EventsTesting99”)
            outfile = “c:\ExcelExportTest.csv”
            Open outfile For Output As #1
            Forall row In csv.CSVArray()
            Print #1, row
            End Forall
            Close #1

            outfile = “c:\Temp2\ExcelExportTest.csv”
            Open outfile For Output As #1
            Forall row In csv.CSVArray()
            Print #1, row
            End Forall
            Close #1

            ”’Date and time stamp for end of file name
            Dim fileName As String
            Dim timestamp As String
            timestamp = Cstr(Now)
            timestamp = Replace(timestamp, “/”, “”)
            timestamp = Replace(timestamp, “:”, “-“)

            outfile = “c:\Temp2\ExcelExportEvents” & timestamp & “.xls”
            Open outfile For Output As #2
            Print #2, “”
            Forall row In csv.HTMLArray()
            Print #2, row
            End Forall
            Print #2, “”
            Close #2

            ‘ Display the message box”

            Dim message As String
            message = “Output File Location = s:\AdPageExport\ExcelExportEvents.xls ”
            Messagebox message
            End Sub

            ”Option Public
            Option Declare

            Thank you for your help on this

          • You are still doing this in your code on the button click event:
            Set csv = New CSVData(“DEVL1/MFF”, “AdPage.nsf”, “EventsTesting99”)
            You need to change that to this:
            Dim col As NotesViewEntryCollection
            '
            ' Set col to contain the selected documents
            '
            Set csv = New CSVData(col)

            It is up to you to create the NotesViewEntryCollection from the selected documents.

          • Karl

            I made the updates listed above I am not getting “Object Variable Not Set”

            %REM
            Agent View Export

            %END REM

            Class RowData
            Public column List As String

            Public Sub New()
            End Sub

            Public Sub SetColumnHeader(view As NotesView)
            Dim viewcolumn As NotesViewColumn
            Dim cnt As Integer
            Forall vc In view.Columns
            Set viewcolumn = vc
            column(Cstr(cnt)) = viewcolumn.Title
            cnt = cnt + 1
            End Forall
            End Sub

            Public Sub SetColumnValues(values As Variant)
            Dim cnt As Integer
            Dim tmp As String
            Forall v In values
            If Isarray(v) Then
            Forall c In v
            tmp = tmp + c + Chr$(13)
            End Forall
            column(Cstr(cnt)) = Left$(tmp,Len(tmp)-1)
            Else
            column(Cstr(cnt)) = v
            End If
            cnt = cnt + 1
            End Forall
            End Sub
            End Class

            Class CSVData
            Private row List As RowData
            Private rowcnt As Long

            %REM
            Function New
            Description: Open the view and read view data
            into a list of RowData objects.
            %END REM
            Public Sub New(col As NotesViewEntryCollection)

            Dim db As NotesDatabase
            Dim view As NotesView

            Dim entry As NotesViewEntry
            Dim colcnt As Integer

            rowcnt = 0
            Set entry = col.GetFirstEntry()
            Set row(“Header”) = New RowData()
            Call row(“Header”).SetColumnHeader(view)
            Do Until entry Is Nothing
            rowcnt = rowcnt + 1
            Set row(Cstr(rowcnt)) = New RowData()
            Call row(Cstr(rowcnt)).SetColumnValues(entry.ColumnValues)
            Set entry = col.GetNextEntry(entry)
            Loop

            End Sub

            %REM
            Function CSVArray
            Description: Returns a string array of CSV data by row
            %END REM
            Public Function CSVArray() As Variant
            Dim rowarray() As String
            Dim textrow As String
            Dim cnt As Long
            Redim rowarray(rowcnt) As String

            Forall r In row
            textrow = “”
            Forall h In r.column
            textrow = textrow + |”| + Replace(h,Chr$(13),”\n”) + |”,|
            End Forall
            rowarray(cnt) = Left$(textrow,Len(textrow)-1)
            cnt = cnt + 1
            End Forall
            CSVArray = rowarray
            End Function

            %REM
            Function HTMLArray
            Description: Returns a string array of HTML data by row
            %END REM
            Public Function HTMLArray() As Variant
            Dim rowarray() As String
            Dim textrow As String
            Dim cnt As Long
            Redim rowarray(rowcnt) As String

            Forall r In row
            textrow = “”
            Forall h In r.column
            textrow = textrow + || + Replace(h,Chr$(13),””) + ||
            End Forall
            rowarray(cnt) = “” + textrow + “”
            cnt = cnt + 1
            End Forall
            HTMLArray = rowarray
            End Function

            End Class

            Sub Click(Source As Button)
            Dim csv As CSVData
            Dim outfile As String

            Dim col As NotesViewEntryCollection
            ‘
            ‘ Set col to contain the selected documents
            ‘
            Set csv = New CSVData(col)

            outfile = “c:\ExcelExportTest.csv”
            Open outfile For Output As #1
            Forall row In csv.CSVArray()
            Print #1, row
            End Forall
            Close #1

            outfile = “c:\Temp2\ExcelExportTest.csv”
            Open outfile For Output As #1
            Forall row In csv.CSVArray()
            Print #1, row
            End Forall
            Close #1

            ”’Date and time stamp for end of file name
            Dim fileName As String
            Dim timestamp As String
            timestamp = Cstr(Now)
            timestamp = Replace(timestamp, “/”, “”)
            timestamp = Replace(timestamp, “:”, “-“)

            outfile = “c:\Temp2\ExcelExportEvents” & timestamp & “.xls”
            Open outfile For Output As #2
            Print #2, “”
            Forall row In csv.HTMLArray()
            Print #2, row
            End Forall
            Print #2, “”
            Close #2

            ‘ Display the message box”

            Dim message As String
            message = “Output File Location = s:\AdPageExport\ExcelExportEvents.xls ”
            Messagebox message
            End Sub

          • OK, good. Just remember that if you change the signature of a function declaration you also need to update everywhere it is being called. Sometimes you also want to do a full recompile of the Lotusscript code.
            Also, I spotted something in your code that you could write in an easier way:
            Dim timestamp As String
            timestamp = Cstr(Now)
            timestamp = Replace(timestamp, “/”, “”)
            timestamp = Replace(timestamp, “:”, “-“)
            outfile = “c:\Temp2\ExcelExportEvents” & timestamp & “.xls”
            Could be written as:
            outfile = “c:\Temp2\ExcelExportEvents” & Format$(Now(),"yyyymmdd_hhnnss") & “.xls”

          • Karl

            I cleaned up the code and did compile still getting “Object variable not set”

            %REM
            Agent View Export
            Created Mar 27, 2013 by Karl-Henry Martinsson
            Description: Code to export a specified view as CSV.
            Copyright (c) 2013 by Karl-Henry Martinsson
            This code is distributed under the terms of
            the Apache Licence Version 2.
            See http://www.apache.org/licenses/LICENSE-2.0.txt
            %END REM

            Class RowData
            Public column List As String

            Public Sub New()
            End Sub

            Public Sub SetColumnHeader(view As NotesView)
            Dim viewcolumn As NotesViewColumn
            Dim cnt As Integer
            Forall vc In view.Columns
            Set viewcolumn = vc
            column(Cstr(cnt)) = viewcolumn.Title
            cnt = cnt + 1
            End Forall
            End Sub

            Public Sub SetColumnValues(values As Variant)
            Dim cnt As Integer
            Dim tmp As String
            Forall v In values
            If Isarray(v) Then
            Forall c In v
            tmp = tmp + c + Chr$(13)
            End Forall
            column(Cstr(cnt)) = Left$(tmp,Len(tmp)-1)
            Else
            column(Cstr(cnt)) = v
            End If
            cnt = cnt + 1
            End Forall
            End Sub
            End Class

            Class CSVData
            Private row List As RowData
            Private rowcnt As Long

            %REM
            Function New
            Description: Open the view and read view data
            into a list of RowData objects.
            %END REM
            Public Sub New(col As NotesViewEntryCollection)

            Dim db As NotesDatabase
            Dim view As NotesView

            Dim entry As NotesViewEntry
            Dim colcnt As Integer

            rowcnt = 0
            Set entry = col.GetFirstEntry()
            Set row(“Header”) = New RowData()
            Call row(“Header”).SetColumnHeader(view)
            Do Until entry Is Nothing
            rowcnt = rowcnt + 1
            Set row(Cstr(rowcnt)) = New RowData()
            Call row(Cstr(rowcnt)).SetColumnValues(entry.ColumnValues)
            Set entry = col.GetNextEntry(entry)
            Loop

            End Sub

            %REM
            Function CSVArray
            Description: Returns a string array of CSV data by row
            %END REM
            Public Function CSVArray() As Variant
            Dim rowarray() As String
            Dim textrow As String
            Dim cnt As Long
            Redim rowarray(rowcnt) As String

            Forall r In row
            textrow = “”
            Forall h In r.column
            textrow = textrow + |”| + Replace(h,Chr$(13),”\n”) + |”,|
            End Forall
            rowarray(cnt) = Left$(textrow,Len(textrow)-1)
            cnt = cnt + 1
            End Forall
            CSVArray = rowarray
            End Function

            %REM
            Function HTMLArray
            Description: Returns a string array of HTML data by row
            %END REM
            Public Function HTMLArray() As Variant
            Dim rowarray() As String
            Dim textrow As String
            Dim cnt As Long
            Redim rowarray(rowcnt) As String

            Forall r In row
            textrow = “”
            Forall h In r.column
            textrow = textrow + || + Replace(h,Chr$(13),””) + ||
            End Forall
            rowarray(cnt) = “” + textrow + “”
            cnt = cnt + 1
            End Forall
            HTMLArray = rowarray
            End Function

            End Class

            Sub Click(Source As Button)
            Dim csv As CSVData
            Dim outfile As String

            Dim col As NotesViewEntryCollection

            ‘

            ‘ Set col to contain the selected documents

            ‘

            Set csv = New CSVData(col)

            ””””””””””’ Set csv = New CSVData(“DominoServer/YourDomain”, “names.nsf”, “People\By Last Name”)
            ‘*** Create CSV file from view
            outfile = “c:\ExcelExportTest.csv”
            Open outfile For Output As #1
            Forall row In csv.CSVArray()
            Print #1, row
            End Forall
            Close #1
            ‘*** Create HTML table and save as .xls to open in Excel
            outfile = “c:\ExcelExportTest.xls”
            Open outfile For Output As #2
            Print #2, “”
            Forall row In csv.HTMLArray()
            Print #2, row
            End Forall
            Print #2, “”
            Close #2
            End Sub

          • You need to set col to something. That object variable is not set to anything (hence the error message). You asked if the code could get modified to process selected documents, so you need to put those documents into a NotesViewEntryCollection.

            Another alternative, if you can’t get them into a NotesViewEntryCollection is to get them into a NotesDocumentCollection, but then you have to rewrite the class itself, and you can’t get the columns names. Instead you probably have to use the field names, or even design a way to create a way to designate the fields you want to export.

            If you send me the requirements I can see how long it would take me to build a solution for you, and how much it would cost.

  11. Hi there,
    Do you think that will be possible to schedule export all e-mails in inbox to excel?
    What I mean – want every day to have my inbox on excel (export to csv, or different readable in excel way). If I have possibilities to automate export will stop to wonder if I missed to export, or lose time to export again. :-)
    Most important for me information can be structured in followed columns:
    1. Sender (column Who)
    2. Subject. If no subject, get all message from e-mail.
    3. Receiving date (column Date)
    4. Flag if e-mail was opened (read)

    After that I can automatize import in excel and will have copy of important information from inbox.

    Best regards
    Todor Todorov

    P.S. Just as detail (think not so important), but most e-mails was written on Cyrillic.

    Reply

  12. Thanks for the code, very much appreciated.

    I had to make a small change as it doesn’t work well if there is more than one column with multivalues. The tmp variable is retained between columns resulting in the second and subsequent multivalue columns inheriting values from previous ones.

    Public Sub SetColumnValues(values As Variant)
    Dim cnt As Integer
    Dim tmp As String
    ForAll v In values
    If IsArray(v) Then
    ForAll c In v
    tmp = tmp + c + Chr$(13)
    End ForAll
    column(CStr(cnt)) = Left$(tmp,Len(tmp)-1)
    tmp=”” ‘<=== Extra line added
    Else
    column(CStr(cnt)) = v
    End If
    cnt = cnt + 1
    End ForAll
    End Sub

    Notuice the

    Reply

    • I had to make another change to the code to use a NotesViewNavigator rather than a NotesViewCollection, because we had the “Show multiple entries as separate values” option ticked on some columns and we ran into an issue where columns that had that option ticked all showed the same value rather than the different values.

      See https://www-01.ibm.com/support/docview.wss?uid=swg21264910 for details of the issue.

      The lines changed are shown below.

      Public Sub New(server As String, database As String, viewname As String)
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim nav As NotesViewNavigator ‘<==== Changed
      Dim entry As NotesViewEntry
      Dim colcnt As Integer

      Set db = New NotesDatabase(server, database)
      If db Is Nothing Then
      MsgBox "Could not open " + database + " on " + server,16,"Error"
      Exit Sub
      End If
      Set view = db.GetView(viewname)
      If view Is Nothing Then
      MsgBox "Could not access view " + viewname + ".",16,"Error"
      Exit Sub
      End If

      Set nav = view.Createviewnav '<==== Changed
      rowcnt = 0
      Set entry = nav.GetFirst() '<==== Changed
      Set row("Header") = New RowData()
      Call row("Header").SetColumnHeader(view)
      Do Until entry Is Nothing
      rowcnt = rowcnt + 1
      Set row(CStr(rowcnt)) = New RowData()
      Call row(CStr(rowcnt)).SetColumnValues(entry.ColumnValues)
      Set entry = nav.GetNext(entry) '<==== Changed
      Loop
      End Sub

      Note… This worked because the view is an uncategorised view. A Categorised view will require changes to this code.

      Reply

Leave a comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.