Calculate sum in last row?

[Aditya jha]

I want to export data from listview to an blank excel file then after exporting data in last row of that excel it will calculate total sum of that column.

My code is below for export data to excel but after this i can't create any logic , so please help me...

Dim XLAPP As Object
Dim XLWB As Object
Dim XLWS As Object
Dim icol As Integer
Set XLAPP = CreateObject("E xcel.Applicatio n")
Set XLWB = XLAPP.Workbooks .Add
Set XLWS = XLWB.Worksheets ("Sheet1")
Dim FLDcount As Integer
XLAPP.Visible = True
XLAPP.UserContr ol = True


FLDcount = ListView1.Colum nHeaders.Count

For icol = 1 To FLDcount
XLWS.Cells(1, icol).Value = ListView1.Colum nHeaders(icol). Text

XLWS.Cells(1, icol).Font.Bold = True
XLWS.Cells(1, icol).Font.Size = 13
XLWS.Cells(1, icol).Font.Colo r = vbRed
Next

Dim lColumn As Long, lRow As Long

For lRow = 1 To ListView1.ListI tems.Count
XLWS.Cells(lRow + 1, 1) = ListView1.ListI tems(lRow).Text
lColumn = 2
Do While lColumn <= ListView1.Colum nHeaders.Count
XLWS.Cells(lRow + 1, lColumn) = ListView1.ListI tems(lRow).SubI tems(lColumn - 1)
lColumn = lColumn + 1
Loop
Next



Set XLWS = Nothing
Set XLWB = Nothing
Set XLAPP = Nothing

[ADezii]

You will need to programmaticall y Insert Formulas into the last Row of Data + 1 for each Column in the Worksheet. You can use the Formula Property of the Range Object. You already have the necessary Objects Declared and Instantiated.

[ADezii]

Once the Worksheet has been populated:

'*****************************************************************************
Dim intFormulaRow As Integer
Dim bytNumOfCols As Byte
Dim intCtr_2 As Integer
Dim intLastDataRow As Integer

intFormulaRow = ListView1.ListItems.Count + 1
bytNumOfCols = ListView1.ColumnHeaders.Count
intLastDataRow = (intFormulaRow - 1)


For intCtr_2 = 2 To bytNumOfCols       Start at 'B'
  XLWS.Range(Chr$(64 + intCtr_2) & CStr(intFormulaRow)).Formula = _
             "=SUM(" & Chr$(64 + intCtr_2) & "1:" & Chr$(64 + intCtr_2) & _
             CStr(intLastDataRow) & ")"
Next
'*****************************************************************************