is there a way to transfer the values of an excel file to a control in vb application (eg datagridview)? if there is, can you also selectively import values by specifying a cell number? it will be a great help. thanks :))
Reverend Jim 3,233
The following code displays how to read an Excel file as if it was a database, and how to write that data to another Excel file by using the Excel Application object. This example uses ADO.NET, however, the same thing can be done using OleDb objects. For OleDb you will need a differenc connection string. For details on that see OleDb Connection Strings. In the SELECT clause, [Name] refers to the column with the header Name. The code assumes that the data in the file is in tabular format with the first row containing the column names. By altering the connection string you can access data with no column headers.
Imports ADODB
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
'Objects for retrieving data from source worksheet
Dim con As New ADODB.Connection
Dim rec As New ADODB.Recordset
'Objects for writing to destination Excel file
Dim xls As New Excel.Application
Dim sheet As Excel.Worksheet
Dim book As Excel.Workbook
'Create an Excel workbook and get references to active workbook and worksheet.
'Note that if you access the sheet directly without creating a "book" reference
'then you will be left with one orphaned Excel.exe task for every time you run
'this application.
xls.Workbooks.Add()
book = xls.ActiveWorkbook
sheet = book.ActiveSheet
'Select the data in the source Excel file
con.Open("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=D:\temp\asset.xls;")
rec.Open("select * from [Sheet1$] where [Name] = 'Rahul'", con, CursorTypeEnum.adOpenStatic)
'Populate the destination worksheet cell by cell
Dim row As Integer = 0
Do Until rec.EOF
row += 1
sheet.Cells(row, 1) = rec("Name").Value
sheet.Cells(row, 2) = rec("Emailid").Value
sheet.Cells(row, 3) = rec("Asset").Value
rec.MoveNext()
Loop
'Close the objects from the source file
rec.Close()
con.Close()
'Save the new file and close objects
book.SaveAs("D:\temp\test.xls")
book.Close()
xls.Quit()
'Release the objects used to access the destination file
releaseObject(book)
releaseObject(sheet)
releaseObject(xls)
End Sub
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
End Class
GeekPlease 16
Good day Jim,
I already seen how to connect to excel using ADO.NET and OLEDB but can you explain what is the good choice between the two? What are the pros and cons of each connection?
Reverend Jim 3,233
ADO is a lower level connection which you can use for basic operations. Your interface to the data is via the RecordSet object. OleDb is a layer of abstraction on top of ADO and provides more flexibility such as using DataTables, DataAdapters, etc. However, greater flexibility comes at the expense of greater complexity.
lexaeterna
thanks reverend jim for the sample code. from what i understand from the code, it fetches data from an excel file and writes it on another excel file. correct me if im wrong, im just a beginner in vb.net. need more experience and explanations. thanks :))
Reverend Jim 3,233
That's pretty much the gist of it. Most of the apps that I wrote were automated and were written in vbscript because they did not require a GUI (and for other reasons which I won't get into). That's why I used the basic ADO interface objects. The code is easily adaptable to OleDb.
GeekPlease 16
Thanks Jim, What an awesome explanation :) Now I undestand..