import values from excel to vb application

[lexaeterna]

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]

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]

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]

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]

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]

Thanks Jim, What an awesome explanation :) Now I undestand..