Thursday, January 23, 2014

How to create excel worksheet with visual basic .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.
View code in Excel in VB


 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:
How to create a new project in VB
Create a new windows application
 


Step 2:
Go to Project ->Add Reference
Easy way to add reference to a VB preogram


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

how to import a namespace in vb.net


Step 3:
Add One Text Box and One button to the form and add the following code in the button’s click event.
simple steps to design form in Visual Studio



Relate excel worksheet with VB code


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

how to record macro in ms office 2012


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.

how to run a macro in excel


I formatted my name like this

Easily format text in MS Excel

Now open the VBA as I mentioned earlier.
Visual basic for applications IDE

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

Recorded macro is easy to view with VBA.


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

That’s it, pretty easy huh?






 

 

No comments:

Post a Comment