<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

VB.Net To Excel Usual Stuff

Published on
10,103 Points
4,003 Views
1 Endorsement
Last Modified:
Avinash Desai
I am normal programmer with skill list consisting of .Net programming for desktop apps and Excel addin devs..
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 .
1
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 3

Author Comment

by:Avinash Desai
Thanks for publishing ...

Dear Readers

If any question or any errors please let me know so that i can correct it

Regards
Avinash Desai
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Join & Write a Comment

This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month