TexasSwede
texasswede@gmail.com
  • About this blog
  • My Website
  • My Resume
  • XML Export Tool
  • Photos

Export Notes view to Excel – with multi-value fields

Posted on April 5, 2013 by Karl-Henry Martinsson 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
« Should everyone be a programmer?
New LEGO Lord of The Rings sets this summer »

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

  1. Anand says:
    May 15, 2013 at 22:47

    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
    • Karl-Henry Martinsson says:
      May 16, 2013 at 17:24

      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
  2. kiddy says:
    May 21, 2013 at 02:16

    Really awesome coding bro, You made such a complex thing so simple.

    Reply
  3. niel says:
    August 20, 2013 at 05:41

    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
    • Karl-Henry Martinsson says:
      August 20, 2013 at 09:50

      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. syerie says:
    September 25, 2013 at 09:12

    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
    • Karl-Henry Martinsson says:
      September 25, 2013 at 09:17

      On what line of code are you getting that error? Turn on the debugger.
      Try to include your code. Or post on StackOverflow, including code, information about what line causes the crash, etc.

      Reply
      • syerie says:
        September 26, 2013 at 01:21

        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
        • Karl-Henry Martinsson says:
          September 26, 2013 at 08:12

          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. Sara says:
    March 7, 2014 at 14:44

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

    Reply
    • Karl-Henry Martinsson says:
      March 7, 2014 at 15:09

      That is the name of the view to use.

      Reply
  6. Raj says:
    June 4, 2014 at 20:57

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

    Reply
    • Karl-Henry Martinsson says:
      June 4, 2014 at 22:47

      Add another call to Replace() to replace any double quotes with two double quotes, as Carl Tyler explains here: http://www-10.lotus.com/ldd/nd6forum.nsf/0/a73c50747f8776fe85257ced004f602a

      Reply
      • Raj says:
        June 4, 2014 at 23:28

        Thanks a bunch. That worked like charm.

        Reply
  7. Judy says:
    June 23, 2014 at 14:48

    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. Deepak Sharma says:
    April 7, 2016 at 01:59

    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
    • Karl-Henry Martinsson says:
      April 21, 2016 at 16:11

      Each document should come out as a separate line in the CSV file.
      The Print statement automatically adds a linebreak automatically.

      Reply
  9. david gellert says:
    October 27, 2016 at 23:52

    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
    • Karl-Henry Martinsson says:
      October 30, 2016 at 14:04

      Thank you, sir! Always feels good to hear I have been able to help someone out. :-)

      Reply
  10. John Thoele says:
    May 19, 2017 at 10:12

    Karl

    Thank you for the example, any chance this could be configured to run on selected documents?

    Reply
    • Karl-Henry Martinsson says:
      May 19, 2017 at 20:52

      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
      • John Thoele says:
        May 22, 2017 at 11:03

        Karl

        Thank you for the response.

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

        Is that because of the document collection?

        Reply
        • Karl-Henry Martinsson says:
          May 22, 2017 at 11:33

          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
          • John Thoele says:
            May 22, 2017 at 13:15

            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

          • Karl-Henry Martinsson says:
            May 22, 2017 at 13:33

            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.

          • John Thoele says:
            May 22, 2017 at 15:18

            Karl

            Getting the Type mismatch on the “Set csv”

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

          • Karl-Henry Martinsson says:
            May 22, 2017 at 17:22

            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.

          • John Thoele says:
            May 23, 2017 at 09:56

            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

          • Karl-Henry Martinsson says:
            May 23, 2017 at 10:46

            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.

          • John Thoele says:
            May 23, 2017 at 16:00

            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

          • Karl-Henry Martinsson says:
            May 23, 2017 at 16:18

            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”

          • John Thoele says:
            May 24, 2017 at 10:10

            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

          • Karl-Henry Martinsson says:
            May 24, 2017 at 10:55

            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. Todor says:
    May 25, 2018 at 05:07

    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. Chris says:
    April 30, 2019 at 01:01

    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
    • Chris Hudson says:
      July 5, 2019 at 01:33

      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 Cancel reply

Your email address will not be published. Required fields are marked *

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

Stack Exchange

profile for Karl-Henry Martinsson on Stack Exchange, a network of free, community-driven Q&A sites

Recent Posts

  • Domino 14 is now available
  • Domino 14 Early Access Program
  • Announced: Engage 2024
  • Integrate Node-RED with Notes and Domino
  • Notes and Domino v12 is here!

Recent Comments

  • Theo Heselmans on Announced: Engage 2024
  • Lotus Script Multi-thread Message Box [SOLVED] – Wanted Solution on ProgressBar class for Lotusscript
  • Viet Nguyen on Keep up with COVID-19 though Domino!
  • Viet Nguyen on Keep up with COVID-19 though Domino!
  • Mark Sullivan on Looking for a HP calculator? Look no further!

My Pages

  • How to write better code in Notes

Archives

  • December 2023 (1)
  • October 2023 (2)
  • September 2023 (1)
  • June 2021 (1)
  • April 2021 (2)
  • March 2021 (1)
  • August 2020 (3)
  • July 2020 (2)
  • April 2020 (2)
  • March 2020 (1)
  • December 2019 (2)
  • September 2019 (1)
  • August 2019 (2)
  • July 2019 (2)
  • June 2019 (3)
  • April 2019 (2)
  • December 2018 (1)
  • November 2018 (1)
  • October 2018 (5)
  • August 2018 (2)
  • July 2018 (3)
  • June 2018 (2)
  • May 2018 (1)
  • April 2018 (2)
  • March 2018 (1)
  • February 2018 (2)
  • January 2018 (4)
  • December 2017 (3)
  • November 2017 (2)
  • October 2017 (2)
  • September 2017 (1)
  • August 2017 (2)
  • July 2017 (6)
  • May 2017 (4)
  • February 2017 (1)
  • January 2017 (2)
  • December 2016 (2)
  • October 2016 (3)
  • September 2016 (4)
  • August 2016 (1)
  • July 2016 (2)
  • June 2016 (2)
  • May 2016 (3)
  • April 2016 (1)
  • March 2016 (4)
  • February 2016 (2)
  • January 2016 (4)
  • December 2015 (3)
  • November 2015 (2)
  • October 2015 (1)
  • September 2015 (2)
  • August 2015 (1)
  • July 2015 (5)
  • June 2015 (2)
  • April 2015 (2)
  • March 2015 (3)
  • February 2015 (2)
  • January 2015 (10)
  • December 2014 (1)
  • November 2014 (3)
  • October 2014 (3)
  • September 2014 (13)
  • August 2014 (6)
  • July 2014 (5)
  • May 2014 (3)
  • March 2014 (2)
  • January 2014 (10)
  • December 2013 (5)
  • November 2013 (2)
  • October 2013 (5)
  • September 2013 (4)
  • August 2013 (7)
  • July 2013 (3)
  • June 2013 (1)
  • May 2013 (4)
  • April 2013 (7)
  • March 2013 (8)
  • February 2013 (9)
  • January 2013 (5)
  • December 2012 (7)
  • November 2012 (13)
  • October 2012 (10)
  • September 2012 (2)
  • August 2012 (1)
  • July 2012 (1)
  • June 2012 (3)
  • May 2012 (11)
  • April 2012 (3)
  • March 2012 (2)
  • February 2012 (5)
  • January 2012 (14)
  • December 2011 (4)
  • November 2011 (7)
  • October 2011 (8)
  • August 2011 (4)
  • July 2011 (1)
  • June 2011 (2)
  • May 2011 (4)
  • April 2011 (4)
  • March 2011 (7)
  • February 2011 (5)
  • January 2011 (17)
  • December 2010 (9)
  • November 2010 (21)
  • October 2010 (4)
  • September 2010 (2)
  • July 2010 (3)
  • June 2010 (2)
  • May 2010 (3)
  • April 2010 (8)
  • March 2010 (3)
  • January 2010 (5)
  • November 2009 (4)
  • October 2009 (7)
  • September 2009 (1)
  • August 2009 (7)
  • July 2009 (1)
  • June 2009 (4)
  • May 2009 (1)
  • April 2009 (1)
  • February 2009 (1)
  • January 2009 (3)
  • December 2008 (1)
  • November 2008 (1)
  • October 2008 (7)
  • September 2008 (7)
  • August 2008 (6)
  • July 2008 (5)
  • June 2008 (2)
  • May 2008 (5)
  • April 2008 (4)
  • March 2008 (11)
  • February 2008 (10)
  • January 2008 (8)

Categories

  • AppDev (10)
  • Blogging (11)
    • WordPress (5)
  • Design (5)
    • Graphics (1)
    • UI/UX (2)
  • Featured (5)
  • Financial (2)
  • Food (5)
    • Baking (3)
    • Cooking (3)
  • Generic (11)
  • History (5)
  • Hobbies (10)
    • LEGO (4)
    • Photography (4)
  • Humor (1)
  • IBM/Lotus (178)
    • #Domino2025 (14)
    • #DominoForever (8)
    • #IBMChampion (46)
    • Administration (7)
    • Cloud (7)
    • CollabSphere (9)
    • Community (49)
    • Connect (33)
    • ConnectED (12)
    • Connections (3)
    • HCL (15)
    • HCL Master (1)
    • IBM Think (1)
    • Lotusphere (46)
    • MWLUG (25)
    • Notes/Domino (99)
      • Domino 11 (7)
    • Sametime (8)
    • Verse (14)
    • Volt (3)
    • Watson (6)
  • Life (8)
  • Microsoft (7)
    • .NET (2)
    • C# (1)
    • Visual Studio (1)
  • Movies (3)
  • Old Blog Post (259)
  • Personal (23)
  • Programming (84)
    • App Modernization (11)
    • Formula (4)
    • Lotusscript (47)
    • NetSuite (4)
      • SuiteScript (3)
    • node.js (4)
    • XPages (4)
  • Reviews (9)
  • Sci-Fi (4)
  • Software (24)
    • Flight Simulator (2)
    • Games (4)
    • Open Source (2)
    • Utilities (6)
  • Technology (37)
    • Aviation (3)
    • Calculators (2)
    • Computers (6)
    • Gadgets (7)
    • Mobile Phones (7)
    • Science (3)
    • Tablets (2)
  • Travel (7)
    • Europe (1)
    • Texas (2)
    • United States (1)
  • Uncategorized (16)
  • Web Development (50)
    • Frameworks (23)
      • Bootstrap (14)
    • HTML/CSS (12)
    • Javascript (32)
      • jQuery (23)

Administration

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Tracking

Creeper
MediaCreeper
  • Family Pictures
© TexasSwede 2008-2014