Excel Forum

Code: Select all

Option Explicit On
Option Strict On

Imports Microsoft.Office.Interop.Excel
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO


Public Class Form1


    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim _dlgOpenFile As OpenFileDialog = New OpenFileDialog()
        Dim _oleConnect As OleDb.OleDbConnection
        Dim _oleCommand As OleDb.OleDbCommand
        Dim _oleAdapter As OleDb.OleDbDataAdapter
        Dim _dataSet As DataSet
        Dim _strConn As String

        With _dlgOpenFile
            .Title = "FIle Selection"
            .Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
            .FilterIndex = 1
            .RestoreDirectory = True
        End With

        If _dlgOpenFile.ShowDialog() = DialogResult.OK Then
            'TextBox1.Text = dlgOpenFile.FileName

            _strConn =
                " Provider = Microsoft.ACE.OLEDB.12.0;" &
                " Data Source = " & _dlgOpenFile.FileName & ";" &
                " Extended Properties = Excel 12.0 XML;" &
                " HDR = YES"

            Try
                _oleConnect = New OleDb.OleDbConnection
                _oleCommand = New OleDb.OleDbCommand
                _oleConnect.ConnectionString = _strConn
                _oleCommand.CommandText = "Select * FROM [Sheet1]"
                _oleCommand.Connection = _oleConnect

                _oleAdapter = New OleDb.OleDbDataAdapter(_oleCommand)
                _dataSet = New DataSet
                _oleConnect.Open()
                _oleAdapter.Fill(_dataSet, "Sheet1")
                DataGridView1.DataSource = _dataSet.Tables("Sheet1")

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                _oleConnect = Nothing
                _oleCommand = Nothing
                _oleAdapter = Nothing
                _dataSet = Nothing
            End Try

        End If


    End Sub

End Class