Hi Friends,
I need your help. Please help me.
I am trying to retrieve data from Excel Spreadsheet to fill DataGridView for display prior to updating SQL SERVER using DataGridView Row individually.

But the coding is not working as this is the first time I done the coding.

this code snippet generate this error message
If objSheet.Cells(excelRow, 0) = "" Then
Exception from HRResult: 0X800A)3EC

These coding prompt the user to select Excel file 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))

                If (excelPathName.Length) <> 0 Then
                    Me.txtExcelFolderName.Text = excelPathName

                End If
            End If
        End With

 End Sub

Here is the coding to retrieve data from Excel to fill DataGridView

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.Workbooks.Open(excelPathName), Excel.Workbook) <--- Error 

     Dim objSheet As Excel.Worksheet = CType(objExcel.Worksheets(1), Excel.Worksheet)
     objExcel.Visible = True

     Dim bolFlag As Boolean = True
     Dim excelRow As Integer = 6
     Dim excelCol As Integer = 1
     Dim DGVRow As Integer = 0

        Do While bolFlag = True
           With DataGridView1
               DGVRow += 1
               excelRow += 1

              If objSheet.Cells(excelRow, 0) = "" Then <--- error 
                 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

        Catch ex As Exception


        End Try
 End Sub

Recommended Answers

All 5 Replies

Yeah. Why not look into OleDb instead?
With OleDb you can treat the Excel file as a Database source, and from there import the information into DataSets and DataTables.
Which in turn can be used as datasource for your DataGridView.

Hi Oxiegen,
The way it was coding was bases on the System Analyst requirements and specification. He want to validate all the data from Excel Row.Cells prior to fill the DataGridView and update the SQL SERVER. I have to follow instructions.

Regarding using DataSet or DataTable, I know how to do it.
Being a good and obedient Analyst Programmer I have to be humble and follow instructions

I understand. Forgive my presumption. :)

Ok. So what does the first error say?
Is excelPathName a valid path and filename?

hello ppl,
I have done connectivity between excel and datagrid in vb.net buti am unable to fill the datagrid plz suggest me the coding in vb.net codes

This thread has been dead for three years. Please start a new thread.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.