I have 2 Excel Workbooks that I populate 45 Worksheets from 45 Tables in SQL Server. The Last Row on each worksheet contains a Total Count of how many Records were in the table. This ranges from 0 To 1500 This is my code:
Code:
strSQL = "SELECT * FROM worksheets;"
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do Until rs.EOF
irow = rs!startrow
WSName = rs!Worksheet
Debug.Print WSName
WSlastcol = rs!endcol
xlApp.Workbooks(rs!file).Sheets(WSName).Activate
strSQL = "Select * From " & rs!Table
'With xlApp.Workbooks(WBName).Sheets(rs!Worksheet)
rsout.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
lpctr = 1
Do Until rsout.EOF
strSQL = "Select * From tbl_worksheetformats Where Worksheet = """ & rs!Worksheet & """"
WS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do Until WS.EOF
Set cell = xlApp.Workbooks(rs!file).Sheets(WSName).Cells(irow, WS!Column)
strfld = WS!Field
cell.Value = rsout.Fields(strfld)
'cell.Font = "Times New Roman"
cell.Font.Size = 8
'cell.Borders.LineStyle = xlAutomatic
'cell.Borders (xlAll)
'Set all borders to continuous and thin
With cell.Borders
.LineStyle = xlContinuous
.Weight = xlMedium
'Sets the border color to RGB value. See Interior row above for more color options
.Color = RGB(255, 255, 255)
End With
'Set only top border as continuos and thin
'With Range("A1").Borders(xlEdgeTop)
'.LineStyle = xlContinuous
'.Weight = xlThin
'End With
If WSCols < WS!Column Then WSCols = WS!Column
WS.MoveNext
Loop
WS.Close
irow = irow + 1
rsout.MoveNext
lpctr = lpctr + 1
Loop
If lpctr = 1 Then
Set cell = xlApp.Workbooks(rs!file).Sheets(WSName).Cells(14, WSlastcol)
cell.Value = 0
sitot = 0
cell.Font.Bold = True
cell.Font.Size = 8
Set cell = xlApp.Workbooks(rs!file).Sheets(WSName).Cells(14, WSlastcol - 1)
cell.Value = "Total Count:"
cell.Font.Bold = True
cell.Font.Size = 8
Else
Set cell = xlApp.Workbooks(rs!file).Sheets(WSName).Cells(irow, WSlastcol)
cell.Value = lpctr - 1
sitot = lpctr - 1
cell.Font.Bold = True
cell.Font.Size = 8
Set cell = xlApp.Workbooks(rs!file).Sheets(WSName).Cells(irow, WSlastcol - 1)
cell.Value = "Total Count:"
cell.Font.Size = 8
cell.Font.Bold = True
End If
strSQL = "Select Row From tbl_system_inventory Where WS = """ & WSName & """"
rssysteminv.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not rssysteminv.EOF Then
Set cell = xlApp.Workbooks(1).Sheets("III System Inventory").Cells(rssysteminv!Row, 3)
cell.Value = sitot
End If
rssysteminv.Close
rsout.Close
Set cell = xlApp.Workbooks(rs!file).Sheets(WSName).Cells(3, 1)
cell.Value = StartDate() & " Through " & EndDate()
'End With
rs.MoveNext
'WSCols = 0
Loop
Set cell = xlApp.Workbooks(1).Sheets("III System Inventory").Cells(1, 2)
cell.Value = "Weekly Report"
Set cell = xlApp.Workbooks(1).Sheets("III System Inventory").Cells(1, 3)
cell.Value = "HVVMG"
Set cell = xlApp.Workbooks(1).Sheets("III System Inventory").Cells(1, 4)
cell.Value = StartDate() & " Through " & EndDate()
rs.Close
xlApp.Workbooks(1).Save
xlApp.Workbooks(2).Save
xlApp.Workbooks.Close
Call Masteremail
Exit Sub
ErrHandler:
End Sub
As you can see it calculates the row and puts the total in the last row prior to saving the workbook. Hopefully you can adapt this to your needs.