[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

[Siddharth Rout]

Try this...

Dim objBook As Excel.Workbook = CType(objExcel.Workbooks.Open(excelPathName))

[Lennie]

Hi koolsid
I have tested out your sample code but it's generated these error message

Dim objBook As Excel.Workbook = CType(objExcel.Workbooks.Open(excelPathName))
Error 1 Syntax error in cast operator; two arguments separated by comma are required.
Error 2 Name 'objBook' is not declared
Error 3 Name 'objBook' is not declared.

regardless, thank to you for trying to help me. Appreciate that very much. With your help of sample coding and I get my application working I will post the coding here to share with other Newbies.

Have a Good Day,
Cheers,
Lennie

[Siddharth Rout]

Try this...

'~~> On Top
Imports Excel = Microsoft.Office.Interop.Excel

Dim excelPathName As String

'~~> Rest of Code

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)
    
    '~~> Rest of code
End Sub

[Lennie]

Hi Hoolsid
I have declared the appropriate name spaces at the top and also the common variables excelPathName as well

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 ' false = update not done

'conection string class
Dim clsconnsrv As ClassConnectionString

[Lennie]

Hi Hoolsid

Thank you for your suggestion.

I have change the coding using your sample and this time this error message Exception from HRResult: 0x800A03C was caused by this coding

If objSheet.Cells(excelRow, 0) = "" Then <--- cause error message
bolFlag = False
Exit Do
End If

[Siddharth Rout]

First things first... it is not Hoolsid...

Secondly can you paste the complete set of code after the modifications that I suggested.

Also declare excelrow as long and then set its value.

[Lennie]

Hi KoolSid,
this is Warm Lennie saying Thank you for your suggestion. As requested List below is the completed coding with changes using your suggestions.

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

[Siddharth Rout]

Hi Warm Lennie

There are no rows/columns which start with 0 in Excel

So

objSheet.Cells(excelRow, 0) should be objSheet.Cells(excelRow, 1) if you are referring to the first column.

Few examples

Cell A1 can also be written as Cell(1,1)
Cell A2 can also be written as Cell(1,2)
Cell B1 can also be written as Cell(1,2)
Cell B2 can also be written as Cell(2,2)

You will have to change the same in the code below as well...

[Lennie]

Hi KoolSid,
Regarding the excelworksheet.cells row and column, I cannot hard coded it but make the row variable excelRow generic.

With DataGridView1
.Rows.Add()
DGVRow += 1 <---- DataGridView row
excelRow += 1 <---- excelRow is generic variable


.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



------------------------------------------------------------
This is the layout of the Excel Spreadsheet:-
Row 1 and Row 2 are the MAIN HEADER BANNER of the Excel Spreadsheet
Eg. Row 1 Customer Name : Malcomn FoodStuff Ltd
Row 2 Order Details From 1/01/1990 To 31/12/2010


because at row 6 is where the Column Headers are.
OrderID|OrderDate|RequiredDate|ShipDate|TransportCompany|ProductName|UnitPrice|Quantity|Discount|

Starting from Row 7 is where the data are located from column A to column I

[Siddharth Rout]

I understand that part but the first column is never referred to as 0. It is referred as 1.

Keep the excelRow as generic but you can hard code the column as you know which column the data starts from and it is constant.

Can I see a sample of your workbook?

[Lennie]

Hi KoolSid,

Here is the sample of the WorkBook

Customer Name : Chop-suey Chinese <=== Header Banner Row 1
Order Details From 1/01/1990 To 31/12/2010 <=== Header Banner Row 2

Below is Columer Header at Row 6. The details data starting from Row7
OrderID OrderDate RequiredDate ShipDate TransportCompany ProductName UnitPrice Quantity Discount
10254 11/07/1996 08/08/1996 23/07/1996 United Package Guaran&#225; Fant&#225;stica $3.60 15 0.15
10254 11/07/1996 08/08/1996 23/07/1996 United Package P&#226;t&#233; chinois $19.20 21 0.15
10254 11/07/1996 08/08/1996 23/07/1996 United Package Longlife Tofu $8.00 21 0.00
10370 03/12/1996 31/12/1996 27/12/1996 United Package Chai $14.40 15 0.15
10370 03/12/1996 31/12/1996 27/12/1996 United Package Genen Shouyu $15.50 155 155.00
10370 03/12/1996 31/12/1996 27/12/1996 United Package Boston Crab Meat $18.40 205 205.00
10370 03/12/1996 31/12/1996 27/12/1996 United Package Camembert Pierrot $34.00 65 65.65
10370 03/12/1996 31/12/1996 27/12/1996 United Package Wimmers gute Semmelkn&#246;del $26.60 55 45.00
10370 03/12/1996 31/12/1996 27/12/1996 United Package Longlife Tofu $8.00 150 0.15
10519 28/04/1997 26/05/1997 01/05/1997 Federal Shipping Ikura $31.00 16 0.05
10519 28/04/1997 26/05/1997 01/05/1997 Federal Shipping Gnocchi di nonna Alice $38.00 40 0.00
10519 28/04/1997 26/05/1997 01/05/1997 Federal Shipping Camembert Pierrot $34.00 10 0.05
10731 06/11/1997 04/12/1997 14/11/1997 Speedy Express Sir Rodney's Scones $10.00 40 0.05
10731 06/11/1997 04/12/1997 14/11/1997 Speedy Express Manjimup Dried Apples $53.00 30 0.05
10746 19/11/1997 17/12/1997 21/11/1997 Federal Shipping Chai $18.00 20 25.00
10746 19/11/1997 17/12/1997 21/11/1997 Federal Shipping Konbu $6.00 6 0.00
10746 19/11/1997 17/12/1997 21/11/1997 Federal Shipping Boston Crab Meat $18.40 50 65.00
10746 19/11/1997 17/12/1997 21/11/1997 Federal Shipping Singaporean Hokkien Fried Mee $14.00 35 55.00
10746 19/11/1997 17/12/1997 21/11/1997 Federal Shipping Tarte au sucre $49.00 45 55.00
10746 19/11/1997 17/12/1997 21/11/1997 Federal Shipping Gudbrandsdalsost $36.00 40 65.00
10966 20/03/1998 17/04/1998 08/04/1998 Speedy Express Gravad lax $26.00 8 0.00
10966 20/03/1998 17/04/1998 08/04/1998 Speedy Express Gnocchi di nonna Alice $38.00 12 0.15
10966 20/03/1998 17/04/1998 08/04/1998 Speedy Express Tarte au sucre $49.30 12 0.15
11029 16/04/1998 14/05/1998 27/04/1998 Speedy Express Gnocchi di nonna Alice $38.00 20 0.00
11029 16/04/1998 14/05/1998 27/04/1998 Speedy Express Vegie-spread $43.90 12 0.00
11041 22/04/1998 20/05/1998 28/04/1998 United Package Chang $19.00 30 0.20
11041 22/04/1998 20/05/1998 28/04/1998 United Package Vegie-spread $43.90 30 0.00
11085 03/03/2010 04/03/2010 05/03/2010 ShannonKuah Aniseed Syrup $10.00 60 75.00
11086 07/03/2010 08/03/2010 09/03/2010 ShannonKuah Alice Mutton $39.00 25 25.00


The idea is to Loop through the Excel SpreadSheet row starting from 7 and retrieve the row column data and fill DataGridView Row and Column with it.

[Siddharth Rout]

Lennie, you are again missing the point....

Ok let me try a different approach.

Your 'OrderID' is in which column?

[Lennie]

Hi KoolSid,
Here are the Excel WorkBook layout

ColumnID Header
A OrderId
B OrderDate
C RequiredDate
D ShipDate
E TransportCompany
F ProductName
G UnitPrice
H Quantity
I Discount

[Siddharth Rout]

Exactly

So

When the loop runs for the 1st time, objSheet.Cells(excelRow, 1) is actually referring to objSheet.Cells(6, 1) which is Cell A6 as you have declared excelRow as 6 and 1 refers to Column A and when the loop runs for the 2nd time objSheet.Cells(excelRow, 1) is actually referring to objSheet.Cells(7, 1) which is Cell A7. So on...

So simply make these changes in your code...

        Try
            Do While bolFlag = True

                 If objSheet.Cells(excelRow, 1) = "" Then
                      bolFlag = False
                      Exit Do
                 End If

                With DataGridView1
                    .Rows.Add()
                    DGVRow += 1
                    excelRow += 1

                    .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)
                    .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 9)
                  
                End With
            Loop

Hope you are with me on this one?

[Lennie]

Exactly

So

When the loop runs for the 1st time, objSheet.Cells(excelRow, 1) is actually referring to objSheet.Cells(6, 1) which is Cell A6 as you have declared excelRow as 6 and 1 refers to Column A and when the loop runs for the 2nd time objSheet.Cells(excelRow, 1) is actually referring to objSheet.Cells(7, 1) which is Cell A7. So on...

So simply make these changes in your code...

        Try
            Do While bolFlag = True

                 If objSheet.Cells(excelRow, 1) = "" Then
                      bolFlag = False
                      Exit Do
                 End If

                With DataGridView1
                    .Rows.Add()
                    DGVRow += 1
                    excelRow += 1

                    .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)
                    .Rows(DGVRow).Cells(0).Value = objSheet.Cells(excelRow, 9)
                  
                End With
            Loop

Hope you are with me on this one?

--------------------------------------------------
Can you please show me what to change ? That's the reason why I posted the coding because I am not sure how to do it. If possible share with me the coding to fill DataGridView as well.

[Siddharth Rout]

Can you please show me what to change ?

But I already did that ?

The changes are highlighted in red. Compare it with your code in post 8.

[Lennie]

H KoolSid,
You mentioned that "but I alredy did that"

So based on the posting at Post 16,
How do you fill the DataGridView Row cells with data from Excel objsheet.cells ?

[Lennie]

Hi Friends
Thanks to all of you for your generousity in sharing information with me. Finally I got the coding working and would like to post it here to share with other Newbies who may have similar problems.

I am so glad to meet you helpers here. this forum is awesome.

Here are the Working Codes:

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   ' false = update not done

-------------------------------------------------------------------------
 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
       
        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
        '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 objExcel As Excel.Application = CType(CreateObject("Excel.Application"), Excel.Application)
        'Dim objBook As Excel.Workbook = CType(objExcel.Workbooks.Open(excelPathName), Excel.Workbook)
        '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 = 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
End clase