VBNet2008 Retrieve Data from Excel to fill DataGrid


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: VBNet2008 Retrieve Data from Excel to fill DataGrid

  1. #1
    Join Date
    Jan 2005
    Location
    New Zealand
    Posts
    319

    [RESOLVED] VBNet2008 Retrieve Data from Excel to fill DataGrid

    Hi Friends,
    I am using VBNEt2008 Window Application.

    I am having problem trying to retrieve data from Excel Spreadsheet to fill DataGridView.

    These 2 line of coding generate same error so far.
    Coding : If objSheet.Cells(excelRow, 0) = "" Then
    .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 0)


    error : Exception from HRESULT : 0x800A03EC

    ----------------------------------------------------------

    Here are the overall coding
    Code:
    Option Explicit On
    
    Imports System.Data.SqlClient
    Imports System.Data
    Imports System.Text
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Windows.Forms
    
    
    Public Class FrmGetExcel
    
        Dim sqlconn As SqlConnection
        Dim sqlcmd As SqlCommand
        Dim DA As SqlDataAdapter
        Dim DR As SqlDataReader
        Dim DS As System.Data.DataSet
        Dim DT As System.Data.DataTable
    
        'common variable
        Dim connstr As String
        Dim excelPathName As String = String.Empty
        Dim bolUpdate As Boolean = False  
    
    ---------------------------------------------------------------------------------------
      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
    
    
    ----------------------------------------------------------------------------------------
      Private Sub btnOpenExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
                             Handles btnOpenExcel.Click
            
            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 long = 6
            Dim excelCol As long  = 1
            Dim DGVRow As long  = 0
    
            Try
                Do While bolFlag = True
                
                        If objSheet.Cells(excelRow, 0) = "" Then <--- cause error
                            bolFlag = False
                            Exit Do
                        End If
     
                  With DataGridView1
                        .Rows.Add()
                        DGVRow += 1
                        excelRow += 1
    
                        .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
    End class
    Last edited by Lennie; 05-29-2010 at 02:47 AM. Reason: TYPE RESOLVED
    Cheers,
    Lennie

  2. #2
    Join Date
    Feb 2006
    Posts
    133
    Hi Lennie,

    first remark objSheet.Cells(excelRow, 0) should be objSheet.Cells(excelRow, 0).value.

    If this doesn't work try to adapt your code like this:
    Code:
      dim objExcel as object
      objExcel = CreateObject("Excel.application")
      objexcel.workbooks.open(excelPathName)
    msgbox   objexcel.activeworkbook.sheets(1).cells(excelrow,0).value
    Do a messagebox to check if the value is correctly retrieved from excel.
    If not the error is with the datagrid.

    Hope this helps.
    Benjamin

  3. #3
    Join Date
    Jan 2005
    Location
    New Zealand
    Posts
    319

    [RESOLVED] VBNet2008 Retrieve Data from Excel to fill DataGrid

    Hi Benjamin,

    Thank you for your support and help. You are jus awesome and generous in sharing information.

    Finally, the application is working now based on your suggestion which inspired me to try out different logics.

    I am posting the the amended coding from my original posting which is now working to share it with other Newbies who may have similar problems.

    Code:
    
     Private Sub FDataReadExcel()
            '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
            Dim strCell7 As String
            Dim strCell8 As String
            Dim strCell9 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)
                        strCell7 = CType(objSheet.Cells(excelRow, 7).value, String)
                        strCell8 = CType(objSheet.Cells(excelRow, 8).value, String)
                        strCell9 = CType(objSheet.Cells(excelRow, 9).value, String)
    
                        .Rows.Add(New String() {strCell1, strCell2, strCell3, strCell4, strCell5, strCell6, strCell7, strCell8, strCell9})
    
     
                        DGVRow += 1
                        excelRow += 1
    
                        strCell1 = ""
                        strCell2 = ""
                        strCell3 = ""
                        strCell4 = ""
                        strCell5 = ""
                        strCell6 = ""
                        strCell7 = ""
                        strCell8 = ""
                        strCell9 = ""
    
    
                    End With
    
                Loop
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
    
            Finally
    
                objBook.Close()
                objExcel.Quit()
            End Try
        End Sub
    
    
    Cheers,
    Lennie

Similar Threads

  1. exporting data from DataGrid to Excel
    By singoi in forum VB Classic
    Replies: 3
    Last Post: 12-01-2008, 09:04 AM
  2. Replies: 5
    Last Post: 07-21-2008, 09:41 PM
  3. Replies: 0
    Last Post: 02-22-2001, 12:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center