473,323 Members | 1,560 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Calculate sum in last row?

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("Excel.Application")
Set XLWB = XLAPP.Workbooks.Add
Set XLWS = XLWB.Worksheets("Sheet1")
Dim FLDcount As Integer
XLAPP.Visible = True
XLAPP.UserControl = True


FLDcount = ListView1.ColumnHeaders.Count

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

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

Dim lColumn As Long, lRow As Long

For lRow = 1 To ListView1.ListItems.Count
XLWS.Cells(lRow + 1, 1) = ListView1.ListItems(lRow).Text
lColumn = 2
Do While lColumn <= ListView1.ColumnHeaders.Count
XLWS.Cells(lRow + 1, lColumn) = ListView1.ListItems(lRow).SubItems(lColumn - 1)
lColumn = lColumn + 1
Loop
Next



Set XLWS = Nothing
Set XLWB = Nothing
Set XLAPP = Nothing
Dec 3 '10 #1
2 2927
ADezii
8,834 Expert 8TB
You will need to programmatically 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.
Dec 3 '10 #2
ADezii
8,834 Expert 8TB
Once the Worksheet has been populated:
Expand|Select|Wrap|Line Numbers
  1. '*****************************************************************************
  2. Dim intFormulaRow As Integer
  3. Dim bytNumOfCols As Byte
  4. Dim intCtr_2 As Integer
  5. Dim intLastDataRow As Integer
  6.  
  7. intFormulaRow = ListView1.ListItems.Count + 1
  8. bytNumOfCols = ListView1.ColumnHeaders.Count
  9. intLastDataRow = (intFormulaRow - 1)
  10.  
  11.  
  12. For intCtr_2 = 2 To bytNumOfCols       Start at 'B'
  13.   XLWS.Range(Chr$(64 + intCtr_2) & CStr(intFormulaRow)).Formula = _
  14.              "=SUM(" & Chr$(64 + intCtr_2) & "1:" & Chr$(64 + intCtr_2) & _
  15.              CStr(intLastDataRow) & ")"
  16. Next
  17. '*****************************************************************************
Dec 3 '10 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: hunt4grouse | last post by:
I'm looking for C code that will give me the last day of the month. Input - any day that I pass in as a variable.... input = 20050201 Output - calculate and format with slashes.... output =...
9
by: griemer | last post by:
Hi every one, Is there a way to calculate the timestamp of 00:00:00 last Sunday. For me, this is the begin of the current week.. Or, how old is this week, in seconds Any ideas? Regards,
2
by: foleyflint | last post by:
Hello, I'm trying to calculate the interest from a bankaccount which is displayed in a text file as followed: 9 2 345 3 3 300 etc. (there are 19 lines in total) the first number...
13
by: Greg | last post by:
Most suggestions on this topic recommend to use a page footer and make it visible only on the last page. My problem is that the footer is half of the height of a page which means the detail would...
3
by: Gretsch | last post by:
Web, html, javascript, Hi, I need to calculate the time since this .htm file was last modified. {which I can then use in a calculation, rather than display, so days&decimals format would be OK}...
1
by: mukeshrasm | last post by:
Hello Every one I want to calculate the last updated time in database. means it should display like last updated 2 hours before or 2 days before so how can i do that? I am sending the...
4
by: khicon73 | last post by:
Hello All, I would like to calculate weeknumber and period from first day of the week (sunday) and last day of the week (saturday) and week number falls from 1 to 52 only. If week number >= 53 then...
20
by: madhu3437 | last post by:
i am in big problem Under unix C Language i want return date format like MM/DD/YYYY this function should return above format . using system date calculate last 6 days mean clearly i am...
5
rcollins
by: rcollins | last post by:
I have a query that runs off a criteria form. What I need to do is fix it so that when I select June from the combo box, it gives me back June, May, April and March. The format in the combo box...
1
by: mzovko20 | last post by:
Hello ! I´m having problem with this. I already created index.html document which allows user to input his/her date of birth. It looks like this : <html> <body> <center> <h3>How old are you...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.