Click here to Skip to main content
15,880,392 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi dear ,

i want to export to excel from flexgrid in vb6. i am using the below code for that but i need to add the sum of Rows in a last row .i dont know how to write the code for that .kindly help me if possible

What I have tried:

<pre>Private Sub Command3_Click()

Dim xlObject As Object
Dim xlWB As Object

        
    Set xlObject = CreateObject("Excel.Application")
 
    'This Adds a new woorkbook, you could open the workbook from file also
    Set xlWB = xlObject.Workbooks.Add
                
    Clipboard.Clear 'Clear the Clipboard
    
   With xlObject.ActiveWorkbook.Activesheet

   xlObject.range("A1:H1").Select
   xlObject.Selection.merge
  
   xlObject.Cells(1, 1).Value = "DXTREAM FITNESS"
   xlObject.Cells(1, 1).Font.Size = 12
   xlObject.Cells(1, 1).Font.fontstyle = "Bold"
   xlObject.Cells(2, 1).Value = DTPicker1.Value
   xlObject.Cells(2, 2).Value = "To"
   xlObject.Cells(2, 3).Value = DTPicker2.Value
   
   xlObject.Cells(1, 1).columnwidth = 9.86
   xlObject.Cells(1, 3).columnwidth = 25
   xlObject.Cells(1, 4).columnwidth = 10.43
   xlObject.Cells(1, 5).columnwidth = 7.57
   xlObject.Cells(1, 6).columnwidth = 7.14
   xlObject.Cells(1, 7).columnwidth = 6.87
   End With
   
    With MSGrid1
        'Select Full Contents (You could also select partial content)
        .Col = 0            'From first column
        .Row = 0               'From first Row (header)
        .ColSel = .Cols - 1    'Select all columns
        .RowSel = .Rows - 1    'Select all rows
    
        Clipboard.SetText .Clip 'Send to Clipboard
        
    End With
            
    With xlObject.ActiveWorkbook.Activesheet
        .range("A3").Select 'Select Cell A1 (will paste from here, to different cells)
        .Paste              'Paste clipboard contents
        
    End With
    

    ' This makes Excel visible
    xlObject.Visible = True
End Sub



this is the excel sheet
DXTREAM FITNESS							
10/01/2018	To	16/01/2018					
Date	ID	Name	Receipt No:	Type	Amount	Vat	Gross
13/01/2018	0	zaki	111	New	150	7.5	142.5
13/01/2018	0	zaki	111	New	150	7.5	142.5
13/01/2018	None	None	11	Daily Collection	10	0.5	9.5
Posted
Updated 16-Jan-18 1:35am
v2
Comments
[no name] 16-Jan-18 7:29am    
but i need to add the sum of Rows in a last row.Your question is quite confusing.Please be specific if you want a row to be created in spread sheet with total sum of Amount because looking at your question it is quite impossible to sum up date fields ,Name,Receipt etc.
zakariyaptpl 17-Jan-18 1:50am    
hi sir , i need to add a new row on bottom to mention the total of amount, Vat and Gross .
[no name] 17-Jan-18 2:16am    
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-object-excel
zakariyaptpl 17-Jan-18 7:48am    
i got it with flexgrid row count.thanks for your support

xlObject.Cells(MSGrid1.RowSel + 3, 6).Value = "CCC"

1 solution

You can add a formula in the last row to do the calculation of the sum automatically. See Range.Formula[^].
 
Share this answer
 
Comments
zakariyaptpl 17-Jan-18 2:03am    
Worksheets("Sheet1").Range("A1").Formula . in this i dont know how to get the last row number because the number of rows will varry according to the flexgrid contents . i have tried the below code but not working
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Richard MacCutchan 17-Jan-18 10:58am    
Count the number of rows in your grid. The only way I have found with a worksheet is to go through each row to see if there is some content - usually checking a column that always has an entry. When you hit a row with no content then you have past the last row.
zakariyaptpl 19-Jan-18 9:24am    
i got it with flexgrid row count.thanks for your support

xlObject.Cells(MSGrid1.RowSel + 3, 6).Value = "CCC"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900