export excel data to datagrid using openfiledialog

[sigridish]

hi all! i have here a code for browse button

[LIST=1]
[*]Private Sub btnbrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbrowse.Click

[*] 

[*]        'prompt user to select Excel name and folder path

[*] 

[*]        Dim openFileDialog1 As System.Windows.Forms.OpenFileDialog

[*]        openFileDialog1 = New System.Windows.Forms.OpenFileDialog

[*] 

[*]        With openFileDialog1

[*]            .Title = "Excel Spreadsheet"

[*]            .FileName = ""

[*]            .DefaultExt = ".xls"

[*]            .AddExtension = True

[*]            .Filter = "Excel Worksheets|*.xls; *.xlsx"

[*] 

[*]            If .ShowDialog = Windows.Forms.DialogResult.OK Then

[*]                excelPathName = (CType(.FileName, String))

[*] 

[*]                If (excelPathName.Length) <> 0 Then

[*]                    Me.TextBox1.Text = excelPathName

[*]                Else

[*] 

[*]                End If


[*]            End If

[*]        End With


[*] 

[*]   End Sub

[/LIST]

and for view button

[LIST=1]
[*] 'test open up excel spreadsheet

[*]        Dim objExcel As New Excel.Application

[*]        Dim objBook As Excel.Workbook = objExcel.Workbooks.Open(excelPathName)

[*]        Dim objSheet As Excel.Worksheet = objBook.Worksheets(1)

[*]        objExcel.Visible = True

[*]        Dim bolFlag As Boolean = True

[*]        Dim excelRow As Integer = 7

[*]        Dim excelCol As Integer = 1

[*]        Dim DGVRow As Integer = 1

[*]        Dim strCell1 As String

[*]        Dim strCell2 As String

[*]        Dim strCell3 As String

[*]        Dim strCell4 As String

[*]        Dim strCell5 As String

[*]        Dim strCell6 As String

[*]        Try

[*]            Do While bolFlag = True

[*] 

[*]                If Convert.ToString(objSheet.Cells(excelRow, 1).value) = "" Then

[*]                    bolFlag = False

[*]                    Exit Do

[*]                End If

[*] 

[*]                With DataGridView1

[*] 

[*]                    strCell1 = CType(objSheet.Cells(excelRow, 1).value, String)

[*]                    strCell2 = CType(objSheet.Cells(excelRow, 2).value, String)

[*]                    strCell3 = CType(objSheet.Cells(excelRow, 3).value, String)

[*]                    strCell4 = CType(objSheet.Cells(excelRow, 4).value, String)

[*]                    strCell5 = CType(objSheet.Cells(excelRow, 5).value, String)

[*]                    strCell6 = CType(objSheet.Cells(excelRow, 6).value, String)

[*] 

[*]                    DGVRow += 1

[*]                    excelRow += 1

[*] 

[*]                    strCell1 = ""

[*]                    strCell2 = ""

[*]                    strCell3 = ""

[*]                    strCell4 = ""

[*]                    strCell5 = ""

[*]                    strCell6 = ""

[*]                End With

[*]            Loop

[*]        Catch ex As Exception

[*]            MessageBox.Show(ex.Message)

[*]        Finally

[*]            objBook.Close()

[*]            objExcel.Quit()

[*]        End Try

[*]    End Sub

[/LIST]

everytime i hit the view button a message box comes out saying exception HResult 0x800A01A8 also the excel file opens. i dont want the excel file to be opened please help

 

[niceastham]

At which point in the code is the exception being thrown?

 

[sigridish]

hi! i already have a new code. here it is

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim fName As String = ""
        OpenFileDialog1.InitialDirectory = "C:\Documents and Settings\t-rpalberto\Desktop"
        OpenFileDialog1.Filter = "Excel Worksheets|*.xls; *.xlsx"
        OpenFileDialog1.FilterIndex = 2
        OpenFileDialog1.RestoreDirectory = True
        If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
            fName = OpenFileDialog1.FileName
        End If
        Me.TextBox1.Text = fName
        Dim TextLine As String = ""
        DataGridView1.Columns.Add("Customer Name", "Customer Name")
        DataGridView1.Columns.Add("Sales Group", "Sales Group")
        DataGridView1.Columns.Add("Customer Type", "Customer Type")
        DataGridView1.Columns.Add("Type Of Industry", "Type Of Industry")
        DataGridView1.Columns.Add("RM", "RM")
        DataGridView1.Columns.Add("Senior RM", "Senior RM")
        If System.IO.File.Exists(fName) = True Then
            Dim objReader As New System.IO.StreamReader(fName)
            Do While objReader.Peek() <> -1
                TextLine = objReader.ReadLine()
                Me.DataGridView1.Rows.Add()
            Loop
        End If
    End Sub

its working but my excel file has 5 sheets. and when i open my file i want the name of the sheets to be displayed in my combo box. can you help me with that? as of now, whats happening is that i created a dummy excel file with only one sheet. and also there is data displayed in my datagrid. can you help me with that?