Excel Automation - Example with Named Ranges

And now to something completely different... the Excel automation. Every now and then you need to open up an Excel file and do stuff with it. In this case we needed to create and read from named ranges in Excel, and it's not overy easy to get information about how to do that, so I thought I might as well post some sample code here.

To get started with Excel automation, there is a good KB-article on MSDN: http://support.microsoft.com/kb/q302094

Now, here's a console app in .NET 3.5 using VB.NET (I prefer to work with VB.NET with Office Interop and VSTO), which opens Excel, adds a new workbook, creates a (3,5) string array with values, fills the a range with these values, creates a named range and gets the values again to see that it worked properly.

Imports Microsoft.Office.Interop

Module Module1

    Sub Main()
        Dim application = New Excel.Application
        application.Visible = True
        Dim workbook = application.Workbooks.Add()
        Dim worksheet As Excel._Worksheet = workbook.ActiveSheet

        Dim saRet(3, 5) As String

        For iRow = 0 To 3
            For iCol = 0 To 5
                saRet(iRow, iCol) = iRow.ToString() + "|" + iCol.ToString()
            Next iCol
        Next iRow

        'get a range, alternative 1
        'Dim range As Excel.Range = worksheet.Range("A1:E3", Reflection.Missing.Value)

        'get a range, alternative 2
        Dim range As Excel.Range = worksheet.Range("A1", Reflection.Missing.Value)
        range = range.Resize(3, 5)

        'set value of range to that of the string array
        range.Value = saRet
        'name the range, explicitly (using dollar $ sign)
        workbook.Names.Add("NamedRange", "=$A$1:$E$3")

        'clear range
        range = Nothing
        'get the values of the named range
        range = worksheet.Range("NamedRange")
        Dim values(,) As Object = range.Value
        Console.WriteLine("rows:" & values.GetUpperBound(0))
        Console.WriteLine("cols:" & values.GetUpperBound(1))
        Console.WriteLine("value of row 2, column 4 (D4) = " & values(2, 4))

        Console.WriteLine("Press key to exit...")
        Console.Read()
        workbook.Close(False)
        application.Quit()
    End Sub

End Module

Hope this helps someone.