VB 2003 - Pushing excess rows to another Excel worksheet?

[amrik]

I have a VB program that displays data in table form from a database, and it also has the ability to export the data to Excel via button clicking. I'm trying to limit the amount of rows of data that get exported onto a single worksheet, and if there are more rows than the worksheet is supposed to fit, I want the extra rows to be exported onto the next worksheet. For example if I have 50 rows of data and I plan to fit only 20 rows per sheet, there should be a total of 3 worksheets, with two completely filled and one filled halfway.

My problem is that instead of finishing with filling up the first worksheet before moving onto the second when I have a data set that exceeds the my row limit, all the data would just get dumped onto the second sheet, at the same time disregarding the row limit. So if this applied to the previous example, there would be 50 rows on the second worksheet with the first worksheet being empty. I was wondering if you all can help with catching whatever kink is causing this to happen from the code below. Thanks beforehand!

Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
            Dim intRowCountExport As Int16

            If tbSpecNumber.Text = "" Then
                MsgBox("Error: no Spec Number issued.")
                Exit Sub
            Else
                intRowCountExport = dsGeneral.Tables("SPEC_PROPERTIES").Rows.Count

                If intRowCountExport < 17 Then
                    intPageCount = 1
                    subExporttoExcel1()
                    xlapp.Visible = True
                ElseIf 33 > intRowCountExport AndAlso intRowCountExport >= 17 Then
                    intPageCount = 2
                    subExporttoExcel1()
                    subExporttoExcel2()
                    xlapp.Visible = True
                End If
    End Sub
   
Private Sub subExporttoExcel1()
            Dim intRowCountExcel As Int16 'Rows of Properties in DataTable
            Dim intProgressExcel As Int16 'Starting row for Properties
            Dim intCounterExcel As Int16 'Row position for DataTable

            '''Spreadsheet location
            xlapp = New Excel.Application
            xlwb = xlapp.Workbooks.Add("file path here")

            xlws1 = xlwb.Worksheets("Specification_1")

            '''Common Data
            xlws1.Cells(9, 2) = tbCustName.Text 'Customer Name
            xlws1.Cells(10, 6) = tbVersion.Text 'Revision #
            xlws1.Cells(11, 6) = cbDateMonth.Text & "/" & cbDateDay.Text & "/" & cbDateYear.Text 'Revision Date
            xlws1.Cells(10, 2) = cbProduct.Text 'Product
            xlws1.Cells(9, 6) = tbSpecNumber.Text

            intRowCountExcel = dsGeneral.Tables("SPEC_PROPERTIES").Rows.Count
            intProgressExcel = 15 'Begins @ row 15
            intCounterExcel = 0 'Row position for DataTable

            Do While intRowCountExcel > 0

                xlws1.Cells(intProgressExcel, 1) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(10) 'Property
                xlws1.Cells(intProgressExcel, 2) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(11) 'Unit
                xlws1.Cells(intProgressExcel, 3) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(12) 'Test Method
                xlws1.Cells(intProgressExcel, 4) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(3) 'Maximum
                xlws1.Cells(intProgressExcel, 5) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(5) 'Target
                xlws1.Cells(intProgressExcel, 6) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(4) 'Minimum

                intRowCountExcel -= 1
                intProgressExcel += 1
                intCounterExcel += 1
            Loop
    End Sub
Private Sub subExporttoExcel2()
            Dim intRowCountExcel As Int16 'Rows of Properties in DataTable
            Dim intProgressExcel As Int16 'Starting row for Properties
            Dim intCounterExcel As Int16 'Row position for DataTable

            '''Spreadsheet location
            xlapp = New Excel.Application
            xlwb = xlapp.Workbooks.Add("file path here")

            xlws2 = xlwb.Worksheets("Specification_1")

            '''Common Data
            xlws2.Cells(9, 2) = tbCustName.Text 'Customer Name
            xlws2.Cells(10, 6) = tbVersion.Text 'Revision #
            xlws2.Cells(11, 6) = cbDateMonth.Text & "/" & cbDateDay.Text & "/" & cbDateYear.Text 'Revision Date
            xlws2.Cells(10, 2) = cbProduct.Text 'Product
            xlws2.Cells(9, 6) = tbSpecNumber.Text

            intRowCountExcel = dsGeneral.Tables("SPEC_PROPERTIES").Rows.Count
            intProgressExcel = 15 'Begins @ row 15
            intCounterExcel = 0 'Row position for DataTable

            Do While intRowCountExcel > 0

                xlws2.Cells(intProgressExcel, 1) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(10) 'Property
                xlws2.Cells(intProgressExcel, 2) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(11) 'Unit
                xlws2.Cells(intProgressExcel, 3) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(12) 'Test Method
                xlws2.Cells(intProgressExcel, 4) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(3) 'Maximum
                xlws2.Cells(intProgressExcel, 5) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(5) 'Target
                xlws2.Cells(intProgressExcel, 6) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(4) 'Minimum

                intRowCountExcel -= 1
                intProgressExcel += 1
                intCounterExcel += 1
            Loop
    End Sub

[westconn1]

you need something like

toprow = 15   ' 1st row of data
sht = xlwb.Worksheets("Specification_1")
snum = 1 
for rec = 1 to dsGeneral.Tables("SPEC_PROPERTIES").Rows.Count
   if rec mod 21 = 0 then
     snum = snum + 1
     sht = xlwb.sheets.add     '  if there are already enough worksheets then you could just use sht = xlwb.sheets(snum)
     sht.name = "Specification_" & snum      ' or whatever sheet names you want to use for each additional sheet
  end if
'  common data here
  sht.cells((rec mod 21) + toprow, 1) = sht.Cells(intProgressExcel, 1) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(10) 'Property
'  other fields etc here
next

you still need all your code to create an excel object, you can change back to you own variable names
i would use a variable for the number of rows per page, then very easy to change
rowsperpage = 20
if rec mod (rowsperpage + 1) then

i have not tested this at all, may contain typos or code errors
i believe this should work in .net, but is basically VBA code modified very slightly

[Spooman]

amrik

At 1st glance, this segment of your 1st sub seems to be "hard-wired"
for 1 or 2 pages only, ie, the possibility of a page 3 isn't covered.

If intRowCountExport < 17 Then
    intPageCount = 1
    subExporttoExcel1()
    xlapp.Visible = True
ElseIf 33 > intRowCountExport AndAlso intRowCountExport >= 17 Then
    intPageCount = 2
    subExporttoExcel1()
    subExporttoExcel2()
    xlapp.Visible = True
End If

Edit:

I was going to suggest using a loop to make it more generic,
but leave it to WestConn to beat me to the post ,,

Spoo

[westconn1]

it occurred to me later that the mod calculation is not correct

should more likely be

toprow = 15   ' 1st row of data
rowsperpage = 20
sht = xlwb.Worksheets("Specification_1")
snum = 1 
for rec = 0 to dsGeneral.Tables("SPEC_PROPERTIES").Rows.Count - 1
   if (rec  + 1) mod rowsperpage = 0 then
     snum = snum + 1
     sht = xlwb.sheets.add     '  if there are already enough worksheets then you could just use sht = xlwb.sheets(snum)
     sht.name = "Specification_" & snum      ' or whatever sheet names you want to use for each additional sheet
     '  common data here
  end if

  sht.cells((rec mod rowsperpage) + toprow, 1) = sht.Cells(intProgressExcel, 1) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(rec).Item(10) 'Property
'  other fields etc here
next

[Spooman]

amrik

If you are not familiar with the Mod operator, this may help

Simple Even/Odd identification

If num Mod 2 = 0 Then
    ' comes here if num is even
ElseIf num Mod 2 = 1 Then
    ' comes here if num is odd
End If

In your case, max rows per page is 20.

So, the "trigger" number is 21, 41, 61, etc.
That is, if the "next" rec will be one of these numbers, you want to start a new page.

That is what westconn1 is accomplishing here..

If (rec  + 1) Mod rowsperpage = 0 then
     snum = snum + 1
     sht = xlwb.sheets.add     '  if there are already enough worksheets then you could just use sht = xlwb.sheets(snum)
     sht.name = "Specification_" & snum      ' or whatever sheet names you want to use for each additional sheet
     '  common data here
End If

He has provided the outer loop (For..Next).
The key is that the If..End If branch gets "triggered" at the key points in the loop

Hope that helps

Spoo

[amrik]

it occurred to me later that the mod calculation is not correct

should more likely be

toprow = 15   ' 1st row of data
rowsperpage = 20
sht = xlwb.Worksheets("Specification_1")
snum = 1 
for rec = 0 to dsGeneral.Tables("SPEC_PROPERTIES").Rows.Count - 1
   if (rec  + 1) mod rowsperpage = 0 then
     snum = snum + 1
     sht = xlwb.sheets.add     '  if there are already enough worksheets then you could just use sht = xlwb.sheets(snum)
     sht.name = "Specification_" & snum      ' or whatever sheet names you want to use for each additional sheet
     '  common data here
  end if

  sht.cells((rec mod rowsperpage) + toprow, 1) = sht.Cells(intProgressExcel, 1) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(rec).Item(10) 'Property
'  other fields etc here
next

I tried my best to adapt your suggestions into my code and executed it, but this time all the rows are being exported to the first worksheet instead of the second. What am I doing wrong? Here's my updated code.

Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
        Dim toprow As Int16 = 15   ' 1st row of data
        Dim rowsperpage As Int16 = 16
        Dim snum As Int16 = 1
        Dim rec As Int16
        For rec = 0 To dsGeneral.Tables("SPEC_PROPERTIES").Rows.Count - 1

            Dim intRowCountExcel As Int16 'Rows of Properties in DataTable
            Dim intProgressExcel As Int16 'Starting row for Properties
            Dim intCounterExcel As Int16 'Row position for DataTable

            '''Spreadsheet location
            xlapp = New Excel.Application
            xlwb = xlapp.Workbooks.Add("file path here")
            xlws1 = xlwb.Worksheets("Specification_1")

            xlws1.Cells(9, 2) = tbCustName.Text 'Customer Name
            xlws1.Cells(10, 6) = tbVersion.Text 'Revision #
            xlws1.Cells(11, 6) = cbDateMonth.Text & "/" & cbDateDay.Text & "/" & cbDateYear.Text 'Revision Date
            xlws1.Cells(10, 2) = cbProduct.Text 'Product
            xlws1.Cells(9, 6) = tbSpecNumber.Text

            intRowCountExcel = dsGeneral.Tables("SPEC_PROPERTIES").Rows.Count
            intProgressExcel = 15 'Begins @ row 15
            intCounterExcel = 0 'Row position for DataTable

            Do While intRowCountExcel > 0

                xlws1.Cells(intProgressExcel, 1) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(10) 'Property
                xlws1.Cells(intProgressExcel, 2) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(11) 'Unit
                xlws1.Cells(intProgressExcel, 3) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(12) 'Test Method
                xlws1.Cells(intProgressExcel, 4) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(3) 'Maximum
                xlws1.Cells(intProgressExcel, 5) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(5) 'Target
                xlws1.Cells(intProgressExcel, 6) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(4) 'Minimum

                intRowCountExcel -= 1
                intProgressExcel += 1
                intCounterExcel += 1
            Loop

            If (rec + 1) Mod rowsperpage = 0 Then
                snum = snum + 1
                Dim sht As Excel.Worksheet
                sht = xlwb.Sheets.Add()   '  if there are already enough worksheets then you could just use sht = xlwb.sheets(snum)
                sht.Name = "Specification_" & snum      ' or whatever sheet names you want to use for each additional sheet

                sht.Cells(9, 2) = tbCustName.Text 'Customer Name
                sht.Cells(10, 6) = tbVersion.Text 'Revision #
                sht.Cells(11, 6) = cbDateMonth.Text & "/" & cbDateDay.Text & "/" & cbDateYear.Text 'Revision Date
                sht.Cells(10, 2) = cbProduct.Text 'Product
                sht.Cells(9, 6) = tbSpecNumber.Text

                intRowCountExcel = dsGeneral.Tables("SPEC_PROPERTIES").Rows.Count
                intProgressExcel = 15 'Begins @ row 15
                intCounterExcel = 0 'Row position for DataTable

                Do While intRowCountExcel > 0

                    sht.Cells(intProgressExcel, 1) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(10) 'Property
                    sht.Cells(intProgressExcel, 2) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(11) 'Unit
                    sht.Cells(intProgressExcel, 3) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(12) 'Test Method
                    sht.Cells(intProgressExcel, 4) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(3) 'Maximum
                    sht.Cells(intProgressExcel, 5) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(5) 'Target
                    sht.Cells(intProgressExcel, 6) = dsGeneral.Tables("SPEC_PROPERTIES").Rows(intCounterExcel).Item(4) 'Minimum

                    intRowCountExcel -= 1
                    intProgressExcel += 1
                    intCounterExcel += 1
                Loop
            End If
        Next
    End Sub

[westconn1]

'''Spreadsheet location
xlapp = New Excel.Application
xlwb = xlapp.Workbooks.Add("file path here")
xlws1 = xlwb.Worksheets("Specification_1")
xlws1.Cells(9, 2) = tbCustName.Text 'Customer Name
xlws1.Cells(10, 6) = tbVersion.Text 'Revision #
xlws1.Cells(11, 6) = cbDateMonth.Text & "/" & cbDateDay.Text & "/" & cbDateYear.Text 'Revision Date
xlws1.Cells(10, 2) = cbProduct.Text 'Product
xlws1.Cells(9, 6) = tbSpecNumber.Text

this part should not be within the loop, should be before the loop and includes the common data for the first worksheet

you only need one worksheet object, i used sht, but you can call it whatever you want, you do not also need xlws1
you should only need one loop For rec = 0 To dsGeneral.Tables("SPEC_PROPERTIES").Rows.Count - 1 the do loops and counters are no longer required, you should no longer need all the counters, the rec variable should count for all

the mod criteria should be the first part, within the loop, before putting any data to any worksheet, as it resets which worksheet that is written to

you are not disposing of your excel object when finished

you actually need much less code than as you have it now