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.

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: , , , , , ,
9 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?

    Thanks,

    Jim

    • 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.

Leave a Reply

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

*