Retrieve Data from Excel to fill DataGridView

[lenniekuah]

Hi Friends,
I need your help. Please help me.

I am trying to retrieve data from Excel Spreadsheet to fill the DataGridView for display prior to updating SQL SERVER with individual DataGridView row and encounter this error message: Public member 'WorkbookOpen' on type 'ApplicationClass' not found. which is caused by this coding trying to open the Excel WorkBook with the Excel name and Folder path.

Dim objBook As Excel.Workbook = CType(objExcel.WorkbookOpen(excelPathName), Excel.Workbook)

-------------------------------------------------------------------
This coding prompt user to select Excel name and folder path

 Private Sub btnFolderDialog_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFolderDialog.Click
        'prompt user to select Excel name and folder path
        excelPathName = ""
        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  (*.xls)| *.xls|All File(*.xls)|.xls"
 
            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                excelPathName = (CType(.FileName, String))             
                End If
            End If
        End With
 
    End Sub 

----------------------------------------------------------------------
This coding retrieve data from Excel to fill DataGridView and error generated here

  Private Sub btnOpenExcel_Click(ByVal sender As System.Object,
     ByVal e As System.EventArgs) Handles btnOpenExcel.Click
 
        Dim objExcel As Excel.Application = CType(CreateObject("Excel.Application"), Excel.Application)
        Dim objBook As Excel.Workbook = CType(objExcel.WorkbookOpen(excelPathName))  <---- cause Error messsage ******* 
        Dim objSheet As Excel.Worksheet = CType(objExcel.Worksheets(1), Excel.Worksheet)
        objSheet.Visible = True
 
        Dim bolFlag As Boolean = True
        Dim excelRow = 6
        Dim excelCol = 1
        Dim DGVRow = 0
 
    Try
       Do While bolFlag = True
 
       With DataGridView1
          .Rows.Add()
          DGVRow += 1
          excelRow += 1
 
           If objSheet.Cells(excelRow, 0) = "" Then
               bolFlag = False
               Exit Do
           End If
 
          .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 0) 
          .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 1)  
          .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 2) 
          .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 3)  
          .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 4)  
          .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 5)  
          .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 6)  
          .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 7) 
          .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 8)  
 
      End With
     Loop
 
 Catch ex As Exception
         MessageBox.Show(ex.Message)
 
 Finally
     objBook.Close()
     objExcel.Quit()
End Try
    End Sub

Thank you for your help

Have a Good Day.
Cheers,
Lennie