Using Visual Basic… if you need help translating to C#, let me know.

1 – First thing you will need to do is to add the Microsoft Excel Com object.

• Right click your project and click Add Reference.
• Click the COM tab – and scroll down until you find the Microsoft Excel Object Library (depending on your installed version – the exact number may be different but mine was Microsoft Excel 11.0 Object Library  — I have Office 2003 installed
• Click OK and step one is finished!

2 – Create an excel document, setup any formatting, record your macro and save the document.

***
Now for me, I had an ASP app that I added an ASPX page to and did the code behind there.  I simply drug one button to my canvas because that was all I needed at the time.  THEN in that buttons click event (to automatically get it’s click event… double click on the button and you will be taken to the code behind and you will be in the click method) I created the code.

4 – Make a reference to your Office COM object.

Reference your Office com object in the top of your code behind like this:

Imports Microsoft.Office.Interop

5 – The Code.

In the button click event we make some variables — for me I made some that you may not need.
ALTHOUGH YOU WILL NEED a datagrid full of data to use the following code.

I created a Dataset from some data that I had lying around.
Dim Districts As DataSet = m_pDistrict.GetAllDistricts

Then created a table from that set and then a datagrid to bind the table to
Dim dt As DataTable = Districts.Tables(0)
Dim dg As New DataGrid

Following that I created my three Excel objects (fairly explanatory)
Dim oExcel As Excel.ApplicationClass
Dim wkbk As Excel.Workbook
Dim wksheet As Excel.Worksheet

Then I bound my data to my datagrid.
dg.DataSource = dt
dg.DataBind()

Now start excel and open the workbook.
oExcel = CreateObject(“Excel.Application”)
oExcel.Visible = True
wkbk = oExcel.Workbooks.Open(Server.MapPath(“/book1.xls”))
wksheet = wkbk.Worksheets(“Sheet1″)  <–NOTE that Sheet1 is the name of your worksheet in your excel doc… if you named it something else, then you need to reflect that here!!!

*** ALSO – the .OPEN above reflects the fact that I am using an ASP app.  If you are using a desktop app you can simply use something like C:\book1.xls  or Application.StartupPath…

NOW I loop through all my data and create a two dimensional OBJECT that will then be filled with my data.  I will then create an Excel Range object that I will resize to the size of my OBJECT that holds my data.  Once that is done, I simply assign the OBJECT to the Range.  (that may sound confusing but the code below will help…)

Dim iRowCount As Integer = dt.Rows.Count
Dim iColCount As Integer = dt.Columns.Count
Dim oData(iRowCount, iColCount) As Object

Dim iRow As Integer, iCol As Integer

For iRow = 0 To iRowCount – 1

For iCol = 0 To iColCount – 1

If Not IsDBNull(dt.Rows(iRow).Item(iCol)) Then
oData(iRow, iCol) = dt.Rows(iRow).Item(iCol)
Else
oData(iRow, iCol) = “”
End If

Next

Next

Dim XRange As Excel.Range = wksheet.Range(“A1″)
XRange = XRange.Resize(iRowCount, iColCount)
XRange.Value = oData

Finally, I call the .Run command with the name of the Macro and BOOM!  We are done.
oExcel.Run(“CreateHFZChart”)

=======

I am sure if you are reading this then you probably see the great utility of this piece of code.  This could be used to produce reports for users who then wish to manipulate the data for their businesses purposes.  And that is without the added functionality of being able to run the Macro.

The limitations on this code would be that the user would have to make sure NOT to save over your master document.  Although, this could probably easily be worked around by having a spare backup document that takes the place of the final document name each time the application is fired.

Anyway, this is a nice useable piece of functionality brought to you by .Net…. hope this helps someone!

t