Export to Excel wizard

I was asked to take a look at an existing application, and come up with suggestions to improve performance.

In a first look I found that most of the views, especially the default to be opened view, were set to refresh the index automatically… you can imagine how much the users love to start working with this application which has readers fields on all documents (NOT!).

When I asked were all the views were needed for, I got as reply: to have it in a format we want to make graphics in Excel.

Okej, why not delete these ‘views to be exported for Excel’ and replace them with an export functionality?

export start button

So what where the needs?

  1. a set of fields for different types of forms that should be manageble by the application manager
  2. an export-mechanism to Excel which should be easy to understand for end-users and give them the opportunity to create customizable exports.

1) Export profiles

In order to create so-called Export profiles for different Notes forms I needed an additional form (ExcelFormExport) where the administrator can:

  • select the desired form for export (FieldA)
  • select the form-fields that should be available for export (FieldB)

This last field is being calculated when FieldA is being exited:

Sub Exiting(Source As Field)
 Dim workspace As New NotesUIWorkspace
 Dim uidoc As NotesUIDocument
 Set uidoc = workspace.CurrentDocument 
 fieldvalue = Lcase(uidoc.FieldGetText(“Tx_FormSelected”))
 Dim doc As NotesDocument
 Set doc = uidoc.document
 If fieldvalue <> “” Then
  Dim session As New NotesSession
  Dim db As NotesDatabase
  Set db = session.CurrentDatabase  
  Forall form In db.Forms
   If Lcase(form.Name) = fieldvalue Then
    If Isempty(form.Fields) Then
     Messagebox form.Name & ” has no fields…”
    Else
     ‘collecting the form fields
     Dim arrFieldNames() As String
     Dim iCount As Integer
     iCount = 0     
     Redim arrFieldNames(0)
     Forall field In form.Fields      
      Redim Preserve arrFieldNames(iCount)
      arrFieldNames(iCount) = field
      iCount = iCount + 1
     End Forall     
     Call doc.ReplaceItemValue(“Tx_FormFields”, BubbleSort(arrFieldNames))     
     Call uidoc.Refresh()     
    End If
    Exit Sub
   End If
  End Forall
  Messagebox “The form “”” & formNameIn & “”” does not exist”
 End If
End Sub

Bubblesort is a function which sorts the array with fieldnames:

Function BubbleSort(vtList As Variant) As Variant
 Dim tmpValue As Variant
 Dim x As Integer, y As Integer 
 If Ubound(vtList) = 1 Then
  BubbleSort = vtList
  Exit Function
 End If 
 For x = 0 To Ubound(vtList)
  For y = (x + 1) To Ubound(vtList)
   On Error Resume Next
   If vtList(x) > vtList(y) Then
    tmpValue = vtList(x)
    vtList(x) = vtList(y)
    vtList(y) = tmpValue
   End If
  Next
 Next 
 BubbleSort = vtList 
End Function

Luckily I could make use of a solution from my colleague Tomas Ekström (his so-called ‘tablewalker’) which made the selection of fields and giving them for the end-user understandable ‘labels’ in the wizard:

export profile form

The next step was creating a dialog between the available ‘Export profiles’ and the Export to Excel solution written by Ken Pespisa.

 dialog for selection source

In order to display the ‘Export profiles’ in the dialoglist I had to re-write Ken’s solution a bit. I advice you to use Ken’s standard solution in stead.

Well, the rest of this export wizard is just following Ken’s work.

 select fields for export dialog

What I tried to achieve with this solution is:

  • having an easy way for an administrator to define ‘Export profiles’ based upon information on Notes forms.
  • presenting an understandable interface for the end-user that enables to create customizable exports to Excel
  • get rid of a large set of nasty Notes views!

A demo you can download here.

Advertisements

9 thoughts on “Export to Excel wizard

  1. Kevin Pettitt 2007-September-27 / 6:43 am

    I used this tool a few years ago and modified it in much the same way. I found the code complicated enough that it was hard to keep everything working for all the different versions of Excel and all the various scenarios for column types, etc.

    Fast forward to today and a new tool is available from OpenNTF which I quite like called ASND Export Facility. One of the really nice aspects of this tool is that you can setup exports from any database without putting any code in that database. I even “SuperNTFified” an earlier version of the tool and will repeat that treatment after my next SuperNTF release. You can find the tool here: http://www.openntf.org/Projects/pmt.nsf/ProjectLookup/ASND%20Export%20Facility

  2. quintessens 2007-September-27 / 8:22 am

    Hej Kevin,
    thanks for the comment. I am curious about your suggestion and I will take a look at it.

    I had to deal with 2 aspects:
    1 – Easy to define which information should be available for download (that’s why I created those ‘Export profiles’)
    2 – An understandable interface for the end-user. I have learnt to over-estimate their basic computer knowledge never.

  3. Sean Burgess 2007-October-4 / 7:54 pm

    Would be interested to see what you think about the Export Facility since it is my code. 🙂

  4. quintessens 2007-October-5 / 2:10 pm

    I think I got it from IBM’s Sandbox, is that correct?

    Well your name is still on the code itself (I think).

    But the code is really great, so thank you for that!

  5. Beat Leuenberger 2008-February-2 / 10:08 am

    Hello Patrick

    The file has a local access protection. Can you please provide a download
    without protection?

    Kind regards
    Beat

  6. raz786 2008-May-18 / 5:28 am

    Hi Patrick

    Again, great site for Notes people!
    I too am haivng a local security issue (attempted encryption process is not supported by this version – i am running r7x)…i would luv to check out this code…would you be able to send it to me?

    ps…as an fyi…i am running a notes db with alot of financial data (12 mnth cashflow system with pipeline info)…given we deal with total values across categories, i found the GetNext command from a Notes Navigator really helpful for dumping info into excel for catorgized total values rather then attempting to add values in a backend document (i hope this makes sense)…just thought i would share that with you and your readers in case they have come across a similar problem.

    regards
    raz

  7. ZM 2008-June-3 / 5:07 pm

    Same here.. I am getting “attempted encryption operation is not supported by this version of notes”. Could you please fix this??

    Thanks
    ZM

  8. Ajay B Mali 2009-January-5 / 8:13 am

    Hi Patrick,

    Nice & helpful site for lotus notes developer. I am trying same functionality and downloaded your sample databases. I am also faacing same issue(attempted encryption operation is not supported by this version of notes).
    Could you please provide me download
    without protection?

    Thanks !!

    Regards
    Ajay B Mali

  9. Pablo 2009-February-22 / 4:41 pm

    Hi Patrick,

    I’m facing a similar problem with field names and what users are expecting. Can you please send me a Db without protection.

    Regards,
    Pablo

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s