[RESOLVED] VBNet2008 Retrieve Data from Excel to fill DataGrid

[Lennie]

Hi Good Guys,
I need your help. Please help me.

I am trying to retrieve data from Excel spreadsheet and fill DataGridView with it for display prior to updating SQL SERVER Table with DataGridView Row individually. I have not done this coding before and encounter this error message Public member 'WorkbookOpen' on type 'ApplicationClass' not found.

cause by this coding:

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

Here are the coding to prompt user to get the 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 With

    End Sub

Here are the coding to fill the DataGridView with Excel Data:

 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))  <---- Error message
        Dim objSheet As Excel.Worksheet = CType(objExcel.Worksheets(1), Excel.Worksheet)
        objSheet.Visible = True
    
        Dim bolFlag As Boolean = True
        Dim excelRow as integer = 6
        Dim excelCol as integer = 1
        Dim DGVRow as integer  = 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