export excel data to datagrid using openfiledialog

[sigridish]

hi all! i have here a code for browse button

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

and for view button

'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

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

[hericles]

You only set your bool value to false If Convert.ToString(objSheet.Cells(excelRow, 1).value) == ""
and never at any other point. In other words your loop runs and runs and runs if that bool is not false (which it won't ever be if it isn't the first time). you end up running out of memory.

[sigridish]

so what should i do hericles?

[sigridish]

hi!
this is my new code

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 guys help me with that?