-
Apr 3rd, 2017, 03:38 PM
#1
Thread Starter
New Member
VB 2003 - Pushing excess rows to another Excel worksheet?
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!
Code:
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
-
Apr 3rd, 2017, 04:28 PM
#2
Re: VB 2003 - Pushing excess rows to another Excel worksheet?
you need something like
Code:
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
Last edited by westconn1; Apr 3rd, 2017 at 04:31 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 3rd, 2017, 04:36 PM
#3
Re: VB 2003 - Pushing excess rows to another Excel worksheet?
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.
Code:
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
-
Apr 4th, 2017, 03:42 AM
#4
Re: VB 2003 - Pushing excess rows to another Excel worksheet?
it occurred to me later that the mod calculation is not correct
should more likely be
Code:
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
Last edited by westconn1; Apr 4th, 2017 at 03:55 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 4th, 2017, 05:51 AM
#5
Re: VB 2003 - Pushing excess rows to another Excel worksheet?
amrik
If you are not familiar with the Mod operator, this may help
Simple Even/Odd identification
Code:
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..
Code:
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
-
Apr 4th, 2017, 11:19 AM
#6
Thread Starter
New Member
Re: VB 2003 - Pushing excess rows to another Excel worksheet?
Originally Posted by westconn1
it occurred to me later that the mod calculation is not correct
should more likely be
Code:
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.
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
Last edited by amrik; Apr 4th, 2017 at 11:45 AM.
-
Apr 4th, 2017, 04:18 PM
#7
Re: VB 2003 - Pushing excess rows to another Excel worksheet?
'''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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|