2 Replies - 309 Views - Last Post: 18 February 2018 - 02:02 PM Rate Topic: -----

#1 shinus   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 18-February 18

actions based on checkedlistbox

Posted 18 February 2018 - 04:41 AM

I'm a newbie to visual studio.

I want to export different datagridviews from multiple forms to an excel workbook as different sheets on the same workbook based on whether it is checked in checkedlist box.

Basically I am doing a daily checklist for our school on location basis where the user can export checklist floor wise on the corresponding form of each floor, but also can export a multiple sheet workbook containing diff floors as per its checked in checklistbox, any help please? I am stuck at checkedlistbox. currently i am doing as below: but gives me an exception at the second sheet.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim excel As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()

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

    Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing

    Me.Cursor = Cursors.WaitCursor

    Dim sheetnumber As Integer = 1

    If CheckedListBox1.GetItemChecked(0) = True Then

        worksheet = workbook.Sheets(sheetnumber)

        sheetnumber = sheetnumber + 1

        worksheet.Name = "Anim_Check List_"

        Dim cellRowIndex As Integer = 1

        Dim cellColumnIndex As Integer = 1

        For i As Integer = 0 To Form7.DataGridView1.Rows.Count - 1

            For j As Integer = 0 To Form7.DataGridView1.Columns.Count - 1

                If cellRowIndex = 1 Then

                    worksheet.Cells(cellRowIndex, cellColumnIndex) = Form7.DataGridView1.Columns(j).HeaderText

                Else
                    worksheet.Cells(cellRowIndex, cellColumnIndex) = Form7.DataGridView1.Rows(i).Cells(j).Value.ToString()

                End If

                cellColumnIndex += 1

            Next

            cellColumnIndex = 1

            cellRowIndex += 1

        Next

    End If

    If CheckedListBox1.GetItemChecked(1) = True Then

        workbook.Worksheets.Add(sheetnumber)

        excel.Worksheets(sheetnumber).activate

        sheetnumber = sheetnumber + 1

        worksheet.Name = "Edits 1-5_"

        Dim cellRowIndex As Integer = 1

        Dim cellColumnIndex As Integer = 1

        For i As Integer = 0 To Form8.DataGridView1.Rows.Count - 1

            For j As Integer = 0 To Form8.DataGridView1.Columns.Count - 1

                If cellRowIndex = 1 Then

                    worksheet.Cells(cellRowIndex, cellColumnIndex) = Form8.DataGridView1.Columns(j).HeaderText

                Else

                    worksheet.Cells(cellRowIndex, cellColumnIndex) = Form8.DataGridView1.Rows(i).Cells(j).Value.ToString()

                End If

                cellColumnIndex += 1

            Next

            cellColumnIndex = 1

            cellRowIndex += 1

        Next

    End If

    Dim saveDialog As New SaveFileDialog()

    saveDialog.FileName = workbook.Name

    saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"

    saveDialog.FilterIndex = 1


    If saveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

        workbook.SaveAs(saveDialog.FileName)

        MessageBox.Show("Export Successful")

    End If

End Sub

This post has been edited by andrewsw: 18 February 2018 - 12:18 PM
Reason for edit:: added missing [code][/code] tags


Is This A Good Question/Topic? 0
  • +

Replies To: actions based on checkedlistbox

#2 andrewsw   User is online

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6551
  • View blog
  • Posts: 26,561
  • Joined: 12-December 12

Re: actions based on checkedlistbox

Posted 18 February 2018 - 12:22 PM

What exception? On what line?



Please wrap code in code tags, I have done it for you on this occasion.
Was This Post Helpful? 0
  • +
  • -

#3 shinus   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 18-February 18

Re: actions based on checkedlistbox

Posted 18 February 2018 - 02:02 PM

View Postandrewsw, on 18 February 2018 - 12:22 PM, said:

What exception? On what line?



Please wrap code in code tags, I have done it for you on this occasion.


i get COM Exception at this line: workbook.Worksheets.Add(sheetnumber)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1