Kickin it with Excel - Part 1

So you love and Cherish Revit... and are fond of Excel... you want to share model data between Excel and your Revit models... here's step one... exporting data to Excel from a Revit model using the API

Please keep in mind that I can't give away ALL of my secrets, so this one is provided as a means to get you started...

This post assumes that you have 64 Bit Revit and 32 bit Microsoft Excel 2010 installed and have mad skills with a Bow Staff.

First set a reference to the following:

Now all you need is a handy class for opening an Excel document that you can write stuff to. This really doesn't need much explanation and should get you all greased up and ready for Part 2 (TBD):

Imports System.Windows.Forms
Imports System.IO
Imports Microsoft.Office.Interop

Public Class clsUtilityExcel

    Private m_ExcelSession As Excel.Application = New Excel.ApplicationClass

    ''' <summary>
    ''' Bind data to Excel
    ''' </summary>
    ''' <param name="path"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function BindToExcel(ByVal path As String) As Excel.Application
        Dim m_Excel As Excel.Application
        Dim m_WB As Excel.Workbook
        If path.Trim() = "" Then
            Try
                m_WB = m_ExcelSession.Workbooks.Add(Type.Missing)
                While m_ExcelSession.Worksheets.Count > 1
                    Dim m_WS As Excel.Worksheet
                    m_WS = TryCast(m_ExcelSession.Worksheets(1), Excel.Worksheet)
                    m_WS.Delete()
                End While
                m_Excel = m_WB.Application
                m_Excel.Visible = True
                Return m_Excel
            Catch exception As Exception
                MessageBox.Show("Unable to start Excel session with file. " & _
                                vbCr & vbCr & _
                                "System Error Message: " & vbCr & _
                                exception.Message, "Error")
                Return Nothing
            End Try
        Else
            If Not File.Exists(path) Then
                MessageBox.Show("Unable to locate file: " & path & ".", "Error")
                Return Nothing
            End If
            Try
                m_WB = DirectCast(System.Runtime.InteropServices.Marshal.BindToMoniker(path), Excel.Workbook)
                If m_WB.Application.ActiveWorkbook Is Nothing Then
                    Dim m_FileName As String
                    m_FileName = path.Substring(path.LastIndexOf("\") + 1)
                    m_WB.Application.Windows(m_FileName).Visible = True
                End If
                m_Excel = m_WB.Application
                m_Excel.Visible = True
                Return m_Excel
            Catch exception As Exception
                MessageBox.Show("Unable to find or start Excel with file: " & path & ". " & _
                                vbLf & vbLf & "System Error Message: " & vbLf & _
                                exception.Message, "Error")
                Return Nothing
            End Try
        End If
    End Function

    ''' <summary>
    ''' Launch an Excel Session
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function LaunchExcel() As Excel.Application
        Dim m_Worksheet As Excel.Worksheet
        Dim m_Workbook As Excel.Workbook = m_ExcelSession.Workbooks.Add(Type.Missing)
        Try
            If m_ExcelSession Is Nothing Then
                Return Nothing
            End If
            While m_ExcelSession.Worksheets.Count > 1
                m_Worksheet = TryCast(m_ExcelSession.Worksheets(1), Excel.Worksheet)
                m_Worksheet.Delete()
            End While
            Return m_ExcelSession
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        End Try
    End Function

End Class

I guess you'll have to stay tuned on how the magic is done to get the data from the elements and into the Excel worksheet...

Care to Request a Topic?

Do you have a burning desire to make an API post request?

Send your request as a mention on Twitter to AYBABTM

Come Book an Appointment to Master Revit

Well... it's already that time of year again... The Autodesk annual software releasals event that you've all been waiting for (patiently I hope). We have a new book out this year and while the name isn't a total representation of how creative we are at our day jobs, is a very creative and enlightening perspective on how to master Autodesk Revit Architecture 2012!

Be sure to check out the best chapter in the whole book, chapter 25!!
"Getting Acquainted with the API"

Buy your copy today (...using the link to the right please ;)

You may want to buy one for every room in your house... your friends will be absolutely envious!! They also make great gifts to relatives and even better gifts for anniversaries and graduations!! Collect 'em all!!!

And if you bring your copy to Revit Technology Conference USA 2011 in Huntington Beach, CA... we (the main authors... and then me) might even autograph them for you!!... Registration is now open!!

Winning Visual Studio Stretchy Form Design 101

I get this question a bunch: "How Don, how do you build those awesome winning forms all the time that stretch all sweet like?"... The answer is simple and I'll share how it's done right here in this very post!

First off, if you're using the form resize events to calculate the widths and heights of your controls based on the size of the form, you're on the wrong path... there's a far easier way to do it.

If you want to follow along, create a new form and layout some controls like the one shown here in this tricky example (datagrid on the left, grouped listbox in upper right, two buttons on the lower right):

Here is how it looks as it stretches (in design view too!!!).... notice how the controls morph in a logical way? It's magic... and logic (I know, I get the two confused sometimes too...)

Here's how I got it to do this... the trick is in each control's anchor settings!... Select a control and click on the Anchor properties. You can set any combination of four directions for anchoring for a single control.

Setting the anchoring of opposite directions will result in the control stretching as the form is stretched in that same direction. Setting all four directions will result in the control stretching in all directions as the form is stretched in both directions from the corner.

Setting the anchor to upper and right will simply justify the control in the form in that direction as the form is stretched in any direction...

When working with groups, the controls inside the groups are anchored within the group boundary only. So if you want a group to stretch, you must set the anchor settings for the group... you should then set the anchoring for the control within the group boundary separately.

See you guys at RTC 2011!!

Thou Shall Read Thy Dialog Box!!

This may appear to be a ridiculous post to most... but you would be amazed as to how many people call me to fix something with their model when they get a popup dialog that they have not seen before. They could solve their own problems 99% of the time if they would just simply READ the dialog box.

It really gets me though when people never read any warnings or dialogs at all and just close them no matter what. These are the people that tend to destroy stuff and don't even know it!

So please... if you're new to Revit or have been doing this stuff for 20 years... please... PLEASE read the dialogs and warning popups all the time!

...or the code ninjas will get you!!! I'm planning to start placing random jokes in my dialogs to urge people to want to start reading dialog boxes... I wish Autodesk could do the same