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
Endif
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
Else
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
objBook.Activate()
Dim objSheet As Excel.Worksheet = CType(objExcel.Worksheets(1), Excel.Worksheet)
objExcel.Visible = True
objSheet.Activate()
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 <--- 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
Loop
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
objBook.Close()
objExcel.Quit()
End Try
End Sub
