VB.Net To Excel Usual Stuff

It’s quite interesting for me as I worked with Excel using vb.net for some time.
Here are some topics which I know want to share with others whom this might help.
First of all if you are working with Excel then you need to Download the Following

http://www.microsoft.com/downloads/details.aspx?familyid=c41bd61e-3060-4f71-a6b4-01feba508e52&displaylang=en

And then extract it and then add Microsoft.Office.Interop.Excel as reference then start writing the things you want to accomplish using .net and Excel.

This Blog or Article helps you to write values to excel,Merge Cells,Protect Excel sheet with Passwords and Many more stuffs.
Import the Statement in to your form or Class
Imports Microsoft.Office.Interop.Excel

Private Sub WorkingWithExcel()
                      
                      'Declaring variable for Excel sheet
                      Dim xlApp As New Microsoft.Office.Interop.Excel.ApplicationClass
                      Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
                      Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
                      'Set the File path
                      xlWorkBook = xlApp.Workbooks.Open("File Path")
                      xlWorkSheet = xlWorkBook.Worksheets("Sheet1$")
                      With xlWorkSheet
                      'ASSIGN VALUE TO A CELL. Here below the value is assigned to 8th Row 12th 'column
                      .Cells(8, 12).Value ="This is test sentence."
                      
                      'Add Rows to the Excel sheet.Here it inserts the row at 10th position
                      .Range("A10").EntireRow.Insert()
                      
                      'Delete Rows to the Excel sheet.Here it Deletes the 10th Row
                      .Range("A10").EntireRow.Delete()
                      
                      
                      'Below code Merges the Entire row
                      .Range("A" & RowNo).EntireRow.MergeCells = True
                      
                      'If you want to Merge only Cells not rows then Below code does it.
                      'Below merges Cells from C to F of 10th ROW
                      .Range("C10:F10").MergeCells = True
                      
                      
                      End With
                      
                      'Before Saving if you want to Protect the Excel sheet with Password then
                      'below code tells how to assign the password for Excel workbook
                      xlWorkBook.Password = "TEST"
                      
                      'Save the WORKBOOK..
                      xlWorkBook.Save()
                      
                      'Close the WorkBook
                      xlWorkBook.Close()
                      xlApp.Quit()
                      'Below Releases the Excel sheet from the system process.
                      releaseObject(xlApp)
                      releaseObject(xlWorkBook)
                      releaseObject(xlWorkSheet)
                      End  Sub
                      ''' <summary>
                      ''' Releases the excel from the system process
                      ''' </summary>
                      ''' <param name="obj">
                      </param>
                      
                      ''' <remarks></remarks>
                      Private Sub releaseObject(ByVal obj As Object)
                      Try
                      System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                      obj = Nothing
                      Catch ex As Exception
                      obj = Nothing
                      Finally
                      GC.Collect()
                      End Try
                      End Sub

Open in new window

If you have any query or suggestion to the code please send me a message or leave comment here..

Hope you liked this code .

Comments (1)