Save DataGridView to Excel in VB

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 765 Views - Last Post: 26 July 2019 - 01:18 PM Rate Topic: -----

#1 Samuel75   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 12-July 18

Save DataGridView to Excel in VB

Posted 26 July 2019 - 01:22 AM

I create a program for student data in visual basic.Net. I used datagridview to input student data. The program must print data as well as saving data in excel for more editing. But coding for save is not working, I received an error when I run the program. Attach my code for saving datagridview to excel.
[Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
        SaveToExcel()
    End Sub

    Private Sub SaveToExcel()
        Dim excel As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
        Dim workbook As Microsoft.Office.Interop.Excel._Application = excel.Workbooks.Add(Type.Missing)
        Dim worksheet As Microsoft.Office.Interop.Excel._Workbook = Nothing

        Try

            worksheet = workbook.ActiveSheet
            worksheet.Name = "ExportedFromDatGrid"

            Dim CellRowIndex As Integer = 1
            Dim cellColumnIndex As Integer = 1

            For J As Integer = 0 To DataGridView1.ColumnCount = -1
                worksheet.cells(CellRowIndex, cellColumnIndex) = DataGridView1.Columns(J).HeaderText
                cellColumnIndex += 1

            Next
            cellColumnIndex = 1
            CellRowIndex += 1

            For i As Integer = 0 To DataGridView1.Rows.Count = -2
                For J As Integer = 0 To DataGridView1.ColumnCount - 1
                    worksheet.cells(CellRowIndex, cellColumnIndex) = DataGridView1.Rows(i).Cells(J).Value.ToString()
                    cellColumnIndex += 1


                Next
                cellColumnIndex = 1
                CellRowIndex += 1
            Next
            Dim SaveDialog As New SaveFileDialog()
            SaveDialog.Filter = "Excel Files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
            SaveDialog.FilterIndex = 2

            If SaveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                workbook.saveAs(SaveDialog.FileName)
                MessageBox.Show("Export Successful")
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            excel.Quit()
            workbook = Nothing
            excel = Nothing
        End Trycode]
:code:

This post has been edited by modi123_1: 26 July 2019 - 06:58 AM
Reason for edit:: In the future, highlight the text that is your code and click the [code] tag button in the editor.


Is This A Good Question/Topic? 0
  • +

Replies To: Save DataGridView to Excel in VB

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6818
  • View blog
  • Posts: 28,229
  • Joined: 12-December 12

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 03:09 AM

What error? On what line?
Was This Post Helpful? 0
  • +
  • -

#3 Samuel75   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 12-July 18

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 03:23 AM

Line 10
[worksheet.Name = "ExportedFromDatGrid"
code][/code]
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6818
  • View blog
  • Posts: 28,229
  • Joined: 12-December 12

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 03:45 AM

What error? The details are telling you and us what went wrong.
Was This Post Helpful? 0
  • +
  • -

#5 Samuel75   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 12-July 18

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 04:35 AM

When I run the system it says "Property Name ready Only". I dont know why
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6818
  • View blog
  • Posts: 28,229
  • Joined: 12-December 12

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 05:09 AM

You should quote the EXACT details of the error.

Anyway, you have declared worksheet to be a workbook object.
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15262
  • View blog
  • Posts: 61,178
  • Joined: 12-June 08

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 07:02 AM

Quote

When I run the system it says "Property Name ready Only". I dont know why

It's a read only property.

You can verify this with the MSDN docs.
https://docs.microso...sheetClass_Name
Was This Post Helpful? 1
  • +
  • -

#8 Samuel75   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 12-July 18

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 11:16 AM

I checked based on reference but I'm totally lost on actually what need to be done
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15262
  • View blog
  • Posts: 61,178
  • Joined: 12-June 08

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 11:48 AM

08        Dim worksheet As Microsoft.Office.Interop.Excel._Workbook = Nothing

I would suggest evaluating if something called 'worksheet' should be a _workbook and not a worksheet.
Was This Post Helpful? 0
  • +
  • -

#10 Samuel75   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 12-July 18

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 12:16 PM

Your correct I put Workbook instead of worksheet. However, there's another new error message when I run the programme. The error is in line 07 in my code. The error is "Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel._Application'"

[Dim workbook As Microsoft.Office.Interop.Excel._Application = excel.Workbooks.Add(Type.Missing)code][/code]
A
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15262
  • View blog
  • Posts: 61,178
  • Joined: 12-June 08

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 12:22 PM

        Dim workbook As Microsoft.Office.Interop.Excel._Application = excel.Workbooks.Add(Type.Missing)

Why do you have a variable called 'workbook' when it is type '_application'? Doesn't make much sense.
Was This Post Helpful? 0
  • +
  • -

#12 Samuel75   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 12-July 18

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 12:35 PM

So do you mean Workbook is not supposed to be declared since is an application?
Help me out here I'm stuck with this item (save datagridvew to excel) the other
controls works well
Was This Post Helpful? 0
  • +
  • -

#13 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15262
  • View blog
  • Posts: 61,178
  • Joined: 12-June 08

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 12:37 PM

It seems you have a pattern of labeling variables one name but they are not actually that thing. I would clean those up. Seems part of the confusion is in what is what and what needs an actual parameter and of what type.
Was This Post Helpful? 0
  • +
  • -

#14 Samuel75   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 12-July 18

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 12:56 PM

I'm totally lost
Was This Post Helpful? 0
  • +
  • -

#15 Samuel75   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 12-July 18

Re: Save DataGridView to Excel in VB

Posted 26 July 2019 - 01:17 PM

I finally got it right. The correct declaration was to clean unwanted item

Thanks Guys!!

[codeDim workbook As Microsoft.Office.Interop.Excel._Application = excel.Workbooks.Add]
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2