Hi All,

I've some excel programs. I want to attach those programs to my vb.net application. Is it possible? If yes then How? If its possible then can the excel file be editable?

Recommended Answers

All 5 Replies

The first thing that you'll need to do is add a reference to the Microsoft Excel Object Library. In your code you will need to create an Excel application object and excel workbooks. Then you should be able to access the file to edit the cells. It may look something like this;

'declare an instance of an excel application
Dim oExcel As New Microsoft.Office.Interop.Excel.Application

'make a book 
Dim oBook As Microsoft.Office.Interop.Excel.Workbook
'set it to the excel doc
oBook = oExcel.Workbooks.Open("path to your file")

'get the sheet 
Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet = oBook.Worksheets("Sheetname")

'here you would put any other code to edit the sheet

There is information about manipulating the data at this link, http://support.microsoft.com/default.aspx?scid=kb;EN-US;302094

I wonder how to process the data in the excel cells with the vb.net codes. thnx

If you are wanting to just read what is on the sheet like it is a database column you would use an OleDb connection object like this;

Dim strSql As String = "select * from [" & SheetName & "$]"
Dim conConn As New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & c:\PathToFile & "; " & _
"Extended Properties=Excel 8.0;")
Dim cmd As New OleDb.OleDbCommand(strSql, conConn)
Dim dr As OleDb.OleDbDataReader

Then use as any other OleDb connection. To access individual cells you need to create Excel objects like the post above and then loop through each row and cell, check the link in that post for examples.

Member Avatar for iamthwee

A word of warning I found out later if using oledb - the spreadsheet must be perfect

In other words:

Every column must either number numeric or non-numeric, if there is a mismatch it doesn't work.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.