CODEWITHSUNDEEP
visual-studio-2008excelvba
user2763619
user2763619

Reputation: 1

How to get data from Excel sheet to VB 2008 program?

I am creating a program which requires some Data from an Existing Excel file. I want to show few Cell's values to VB program message box created in a form. How can I get it in VB-2008? Please help.

Upvotes: 0

Views: 294

Answers (1)

Brian Ogden
Brian Ogden

Reputation: 19232

I believe "TABLE_NAME" in the following example will be the name of the first Sheet in the Excel file.

    Imports System.Data.OleDb
    Imports System.Data

    Private Function getExcelDataTable() As Data.DataTable
                Dim dt As New Data.DataTable

                Dim fileName As String = Server.MapPath("~/Temp/MyExcel.xls")


                Dim xConnStr As String = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
                        "Data Source=" & fileName & ";Extended Properties=""Excel 12.0;IMEX=1"""
                Dim objXConn As New OleDbConnection(xConnStr)
                Try
                    objXConn.Open()
                    Dim dtSchema As Data.DataTable
                    dtSchema = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
                    Dim sheet1 As String = dtSchema.Rows(0).Item("TABLE_NAME")
                    Dim objCommand As New OleDbCommand("SELECT * FROM [" & sheet1 & "]", objXConn)
                    Dim objDataAdapter As New OleDbDataAdapter()
                    ' retrieve the Select command for the Spreadsheet
                    objDataAdapter.SelectCommand = objCommand
                    objDataAdapter.Fill(dt)
                    objXConn.Close()
                Catch ex As Exception
                    objXConn.Close()
                    Throw ex
                End Try

                Return dt
            End Function

Upvotes: 1

Related Questions