Tip of the Week #4 Treeview To Excel

Yesterday I was asked if I knew of a way to export a treeview to PDF or excel. I did not at the time, but I always welcome a challenge, as its a chance for me to learn something new, and broaden my own code and method toolbox.

Problem definition and requirements gathering

Now as with any client, the first thing you as a programmer need to do is to make sure you have understood the requirements, and at least have a good idea whether its possible to deliver those requirements with the tools available to you. I needed to make sure we had the same idea about what the end result should look like.  Treeview to excel actually has a bit of room for interpretation. Does the client want the treeview CONTROL to be placed in the excel sheet? Or just the visible area of the treeview? Or just the selected node and child nodes? Expanded nodes only? What about images? I knew I could place the text into excel without issues, but what about images? I don’t know of any easy way to get images from the treeview into excel, especially if you don’t have the original image. Of course it can be done, but such a thing would be outside the scope of what I can accomplish on a pro-bono basis. So I asked the poster for some more specific requirements and asked him to make a sketch. I know of so many posters and clients who just say “I want something that I can use to keep track of hours spent”. Sure, but you can do that in a 1000 ways, and some ways are more suitable than others depending on how you are working, and what your processes are. The fact is we as developers need the client to express what HIS idea of a interface or requirement is, and then we can make a prototype and see how close our mutual expectations match.


So I asked the original poster for a screenshot of the treeview and what he wanted it to look like in excel, and he came up with these 2 screenshots:

The Treeview

The Treeview


The Excel Sheet

The Excel Sheet


Now these 2 images convey alot of information to me. The poster wants the child items to be indented, and he wants any text colour to be copied over into excel as well. Without the images I would probably not have thought to include colouring as part of my export routine. The real issue is that like building a house, its more difficult to change the foundation once the house is standing. Its the same with code. The more you know about your end target before starting, the more you can plan to take the most efficient route to get there. Many projects end up as a perceived failure due to misunderstandings on what the requirements are.


The solution

Okay, enough talking, lets get down to business. Its a simple matter of getting the first root node, which can be done through Treeview.Nodes(1).Root.FirstSibling. This will fail if treeview is empty so therefore I start by checking that the nodecount is greater than 1. After that I simple iterate through nodes and export each of them to excel. The full code is below.

Public Sub TreeviewToExcel(tv As MSComctlLib.TreeView, Optional exWorksheet As Excel.Worksheet)
' Procedure : Treeview_ToExcel
' Author    : AEC - Anders Ebro Christensen / TheSmileyCoder
' Date      : 2014-03-07
' Version   : 1.0
' Purpose   : Export a treeviews content into excel.
'           : If no worksheet is passed a workbook is created.
' Commments :
'           :
' Bugs?     : Email: TheSmileyCoder
   Dim nodRoot As Node
   Dim nodX As Node
   Dim irow As Integer
   On Error GoTo Err_Handler
   If tv.Nodes.Count = 0 Then
      MsgBox "Treeview contains no nodes to export"
   End If

   If exWorksheet Is Nothing Then
      Set exWorksheet = CreateObject("Excel.Application").Workbooks.Add.Sheets(1)

   End If

   'Get A node
      Set nodX = tv.Nodes(1) 'There is always at least 1 node present

   'Get the first root node
      Set nodRoot = nodX.Root.FirstSibling

   'Drill down and export to excel.
         Call pOutputNodeToExcel(0, irow, nodRoot, exWorksheet)
         Set nodRoot = nodRoot.Next
      Loop While Not (nodRoot Is Nothing)

   'Ensure worksheet is visible
      exWorksheet.Application.visible = True

      Set exWorksheet = Nothing

      On Error GoTo 0
      Exit Sub

   TSCs_ReportUnexpectedError "Treeview_ToExcel", "modTreeviewToExcel"
   Resume Exit_Sub
End Sub

Private Sub pOutputNodeToExcel(ByVal iLevel As Integer, ByRef irow As Integer, ByRef nodZ As Node, ByRef exWorksheet As Excel.Worksheet)
   'Export to excel
      exWorksheet.Cells(irow + 1, iLevel + 1).Value = nodZ.Text
      If nodZ.ForeColor <> vbBlack Then
         exWorksheet.Cells(irow + 1, iLevel + 1).Font.Color = nodZ.ForeColor
      End If
      If nodZ.Bold Then
         exWorksheet.Cells(irow + 1, iLevel + 1).Font.Bold = True
      End If
      irow = irow + 1
   'Recursively work on children if any
      iLevel = iLevel + 1
      If nodZ.Children > 0 Then
         Dim nodChild As Node
         Set nodChild = nodZ.Child
            pOutputNodeToExcel iLevel, irow, nodChild, exWorksheet
            Set nodChild = nodChild.Next
         Loop While Not (nodChild Is Nothing)
      End If
End Sub

This uses early binding, but could be converted to late binding. If you are not using my automatic Error reporter to mail bug reports to yourself, then you should modify the errorhandler part accordingly.


I hope someone finds this usefull, and thanks to AJ for inspiration for this post.


Tagged with: , , , , , ,
12 comments on “Tip of the Week #4 Treeview To Excel
  1. Jesus Miranda. says:

    hello, do you have any Idea, how to do the treeview in excel 2013 via VBA?

    • TheSmileyCoder says:

      I presume you could put it in an excel userform in much the same way as it is done in access, but I have not tried.

  2. vikas says:

    Hello !
    Thanks a lot ! for such a wonderfull article.I have one request.Can you please convert this article into c# so that i can use it in my case.

    Thanks a lot!

  3. Jim dePrado says:

    I am having trouble calling your procedures. I have a treeview (“TreeView31”) on sheet5. I would like to transfer the information to “Reference” sheet. Can you help?



    • TheSmileyCoder says:

      Hi Jim
      When you say you have the treeview on sheet5, is that to indicate you have a treeview control inside excel?

  4. Rodrigo says:

    i have a problem with this line:

    Set nodX = tv.Nodes(1)

    Error 13 mismatch type

  5. tomazvdsnmaz says:

    what value should I put in the tv of the Public Sub TreeviewToExcel(tv As MSComctlLib.TreeView, Optional exWorksheet As Excel.Worksheet) ?

    • TheSmileyCoder says:

      It will take a treeview object. So if you are calling the function from e.g. a form button it might look like:
      Private sub form_button_Click()
      TreeviewToExcel Me.TreeviewControl.Object
      End Sub

      The .Object is required, because technically Me.TreeviewControl is “just” an ActiveX container, and we want object (the treeview) inside of that container. Hope that helps.

  6. Penny says:

    Thank you so much for your great solution. This is so useful. Not sure if you’re still monitoring this thread, but could you advise of a way to include the full path of each item, ie including all parent node names in the preceding columns (one parent per column)? And of including another field, eg an ID field, in eg the first column of the spreadsheet? Thank you very much

  7. Penny says:

    Re previous request – i’ve added node.key and node.fullpath to the export, and I can do a manual transform to get the full path separated into columns (convert the excel to delimited file, search and replace the path separator with the delimiter then open in Excel again), but would really appreciate any advice on how to code it so all the parents are in sep columns without having to do the manual transform – beyond my skill level 🙂

    • TheSmileyCoder says:

      For what its worth, that is not always such a straightforward task 🙂
      I would love to say I could code a demo for you, but the reality is that I don’t think I will find the time for it, and I don’t want to promise more than I can keep. Sorry.
      You would need to look into the Parent property of each node recursively.

Leave a Reply to Rodrigo 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.