The message here is that Excel is a very powerful tool and VBA just makes it even better. If you’re a BASIC Programmer you may be wondering what VBA is?. Well with Microsoft Office it’s an object orientated way of programming using objects.

These work like this

Object.Method.Property=”whatever”

Or rather you are supplied with a number of objects or classes, each of which has their own methods and properties. Really coding with VBA for me was very logical. You had an Application object with let you control the errr..Application!! Then you would have worksheet objects which had cells which could be populated!

All this was fine and cool but what if you wanted to do something like get some data from an outside source? It could be done but typically you would end up adding in declare statements for WIN32 APIs. In short as soon as you tried to do anything none Excel related you were back to using APIs which was a pain.

I guess Microsoft could have produced VBA.NET and given you all sorts of funky language add-ons but instead they have taken the reverse route and now given you the ability to code excel applications in C#.NET and VB.NET!

Why??

We are all excited about .NET and are busy coding award in C# or VB.NET when suddenly you get a command from the company president to build some business logic in Excel so he can manipulate some company financial data. Oh no, you think. It’s been months since you did any VBA. Well worry no more, you can now launch Visual Studio.NET and write your Excel code in C#!!!

Over time companies are going to end up having more and more .NET skilled developers and it makes sense to be able to use these skills wherever possible. Let me just say that VBA is an excellent tool but now I have transitioned to C# I do not want to have to use VBA if at all possible.

So here you go, a first Excel C# program! In this example I will simply populate the data in the Active Worksheet to give you a taste of what’s possible.

Before you can do any of this you need to get the Office 2003 beta kit from

http://www.microsoft.com/office/preview

Then you need the office add-on for Visual Studio.Net 2003

http://www.microsoft.com/downloads/details.aspx?FamilyID=9e0b1b7c-4ab5-40d2-b4d9-5817ab0bc1e5&DisplayLang=en 

Once it’s all loaded up as per the instructions its time to code some Excel!!

Launch Visual Studio.Net 2003 and go new project and choose an Excel Workbook project like this

Click here to see a screen shot

Next you will be prompted to create a new Excel Workbook for this project or use an existing one. I chose to create a new Excel workbook project like this.

Click here to see a screen shot

Ok now that we have created the project you can just go ahead and build the project as normal. However when you run it you will just get Excel opening and nothing else happening. We need to add some code to make something useful happen.

For this project I have added code to the ThisWorkbook_Open method of my OfficeCodeBehind class. This code of course is run when I open the workbook. Here are the guts of my code.

protected void ThisWorkbook_Open() { Excel.Worksheet ws=new Excel.WorksheetClass(); ws = (Excel.Worksheet)ThisApplication.ActiveWorkbook.ActiveSheet; for(int i=1;i<11;i++) { for(int j=1;j<11;j++) { ws.Cells[i,j]=i; } } }

You will notice that coding for Excel is not much different from coding other applications in C#. You have classes and methods and properties as always. You just have to learn a new set of classes specific to Microsoft Excel.

In the above code I have used the WorksheetClass and then retrieved the ActiveSheet or the one Excel is showing. Once that is done I can use Cells to specify the cell value by row and column.

In fact the only thing complicated in the code above is this line,

ws = (Excel.Worksheet)ThisApplication.ActiveWorkbook.ActiveSheet;

Here I am using (Excel.Worksheet) to cast the object to a worksheet. Yes, for some reason .ActiveSheet Is not returning a worksheet object but is instead returning just an object so I have to cast it to another type or in this case a Worksheet object.

Okay so this code is built and ready to run so let’s see the output!

Click here to see a screen shot

Wow all that effort for so little reward! So every time you open this Workbook the code I have written will be run. In this case the output is pretty simplistic but you can imagine the same code could instead be connecting to some server on the other side of the world and pulling in last nights sales figures and populating the spreadsheet with them. The power of this tool is going to be the ability to create libraries of C# or VB.NET code and allow you to reuse them in Microsoft Office documents as well as more normal Window Applications.

Get the code here: Excel2003fromCSCode.zip


This article originally appeared at http://www.c-sharpcorner.com