Simple Steps to create an excel worksheet using VB.NET

In this tutorial I’m going to show you how to parse a text from Text Box to Microsoft Office Excel with Visual Basic .NET.I hope that the reader is a beginner and know the basics of .NET. Our aim is to automate an Excel Work Book. This is very useful for the developers of business based applications. If we are using MS Excel for reporting, that will be very handy because Excel has a lot of features for formatting both text and numbers.

   Now we are going to use Visual Basic 2010. We will also be using VBA that is Visual basic for applications which is bundled with the office package. We will use VBA for generating code (Confused?).Let me explain how it will help us in generating the code. You might have familiar with the macro feature in the office .Whenever we record a macro a corresponding VB code is generated by the office and when we run it the reordered code runs and we can view the effects made by the macro. So, where could I find the code in Excel? This might be the question you are going to ask me. My answer is, Just Click on the view code in the excel sheet’s context menu.

Check out the following screen shot for reference.

 OK.I got the code what do I do with it? Now this is your question. Keep this question in mind and I’ll be explaining it later in this article. I’ll show you everything from the scratch so that you can understand it pretty easily.

   First thing is obviously we need to create a project in Visual Studio. That should be a windows application and the language should be Visual Basic. And add reference to the Office object library. This is a Name Space containing a lot of classes for interacting with the office applications such as MS Word, MS Power Point and Obviously MS Excel. Once you added the reference to the office object library, you need to import it into your code b using the “Imports” keyword so that you can directly declare the variables without specifying its name space. While you code you might have noticed that, some imported libraries won’t show their methods or subclasses if you pressed the dot. That is because the libraries are not written in .NET. In olden times (Visual Basic 6) you might have to call APIs for accessing all these services .Still we are using the API calls but that is in a simple way. Here comes the importance of VBA. Since VBA is integrated with Office, we can view all the methods supported by office by declaring variables s and pressing the dot, So that you can copy the code from VBA to Visual Basic. Now we are going to the step by step explanation.

Step 1:

Create a new windows application

Step 2:

Go to Project ->Add Reference

Double Click on the Microsoft office 12.0 Object Library and add the following lines at the start of the Form Class.

Step 3:

Add One Text Box and One button to the form and add the following code in the button’s click event.

Step 4:

Run the code! That’s it. But where is the VBA? Let me Show it for you

Now do some formatting stuffs like bold, Color Change etc after pressing the Record Macro menu. You will get the following window for specifying the macro short cut.

I formatted my name like this

Now open the VBA as I mentioned earlier.

You Can see that a module is generated by the macro open it to view the code. Mine look like this

You can apply all these properties to the following sheet1 object of type Excel.WorkSheet.

That’s it, pretty easy huh?