在从VB6导出excel的最后一行中添加数据 [英] Add data in last row in excel exporting from VB6

问题描述

亲爱的，



我想在vb6中从flexgrid导出到excel。我正在使用下面的代码，但我需要在最后一行添加行的总和。我不知道如何编写代码。如果可能的话，请帮助我



我尝试过：

< pre> Private Sub Command3_Click（）
 
 Dim xlObject As Object 
 Dim xlWB As Object 
 
 
设置xlObject = CreateObject（Excel.Application）
 
'这个添加一个新的woorkbook，你可以从文件中打开工作簿也是
设置xlWB = xlObject.Workbooks.Add 
 
 Clipboard.Clear'清除剪贴板
 
使用xlObject .ActiveWorkbook.Activesheet 
 
 xlObject.range（A1：H1）。选择
 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。单元格（2,1）.Value = DTPicker1.Value 
 xlObject.Cells（2,2）.Value =T o
 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 
以
 
结束使用MSGrid1 
'选择完整内容（您也可以选择部分内容）内容）
 .Col = 0'从第一列
 .Row = 0'从第一行（标题）
 .ColSel = .Cols  -  1'选择所有列
 .RowSel = .Rows  -  1'选择所有行
 
 Clipboard.SetText .Clip'发送到剪贴板
 
结束使用
 
使用xlObject.ActiveWorkbook.Activesheet 
 .range（A3）。选择'选择单元格A1（将从此处粘贴到不同的单元格）
 .Paste '粘贴剪贴板内容
 
结束使用
 
 
'这使得Excel可见
 xlObject.Visible = True 
 End Sub 

这是excel表

 DXTREAM FITNESS 
 10/01/2018至16/01/2018 
日期ID名称收据编号：类型金额增值税总额
 13/01/2018 0 zaki 111新150 7.5 142.5 
 13/01 / 2018 0 zaki 111新150 7.5 142.5 
 13/01/2018无无11每日收集10 0.5 9.5 

解决方案

您可以在最后一行添加公式以自动计算总和。请参阅 Range.Formula [ ^ ]。

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

解决方案

You can add a formula in the last row to do the calculation of the sum automatically. See Range.Formula[^].

这篇关于在从VB6导出excel的最后一行中添加数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！