如何在当将查询的记录集插入到excel里后,在最后的记录以后插入刚才插入的记录的汇总记录如何做成自动的呢
就是如果插入的记录集有两个字段:
a b
1 3
2 5
3 4
4 8
当插入这四条记录后想在最后的也就是第五条记录开始应该是a5 和 b5分别插入 ‘汇总’ 和 ‘20’请问用代码怎么实现,记录集是动态生产的,但是字段是固定的 。
就是如果插入的记录集有两个字段:
a b
1 3
2 5
3 4
4 8
当插入这四条记录后想在最后的也就是第五条记录开始应该是a5 和 b5分别插入 ‘汇总’ 和 ‘20’请问用代码怎么实现,记录集是动态生产的,但是字段是固定的 。
sum里面的格式不一定对,有段时间不用EXCEL,忘得差不多了。
主要意思就是利用EXCEL的公式计算功能,让它来做汇总的工作就可以了。
Private Sub Command1_Click()
Dim mRst As New ADODB.Recordset
Dim mCon As New ADODB.Connection
Dim mSum
mRst.CursorLocation = adUseClient
mRst.Open "Select * From [Sheet2$]", mCnnString, adOpenStatic, adLockOptimistic
mSum = 0
Do Until mRst.EOF
mSum = mSum + mRst("b")
mRst.MoveNext
Loop
mCon.CursorLocation = adUseClient
mCon.Open mCnnString
mCon.Execute "Insert Into [Sheet2$] Values('" & "汇总'," & CStr(mSum) & ")"
mCon.Close
mRst.Close
Set mCon = Nothing
Set mRst = Nothing
End Sub
mCnnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= " & App.Path & "\bb.xls;" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"
End Sub
由于引用ADO对象操作直接操作EXCEL不支持复杂一点的SQL语句(例如SELECT SUM(b) AS mSum FROM [Sheet2$])所以只能用打开记录集的方法一条一条加了。
Private Sub Command1_Click()
Dim xlApp As Excel.Application '定义EXCEL类
Dim xlBook As Excel.Workbook '定义工件簿类
Dim xlsheet As Excel.Worksheet '定义工作表类
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
xlApp.Visible = True '设置EXCEL可见
Set xlBook = xlApp.Workbooks.Open(App.Path & "\bb.xls") '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(2) '打开EXCEL工作表
xlsheet.Activate '激活工作表
xlsheet.Cells(6, 1) = "汇总"
xlsheet.Cells(6, 2) = xlApp.WorksheetFunction.Sum(xlsheet.Range("B2:B5"))
End Sub
xlsheet.Cells(6, 2) = xlApp.WorksheetFunction.Sum(xlsheet.Range("B2:B5"))
里面的数字都是动态的由所插入的记录集所决定的,能不能改成都是动态的呀
'工程->引用Microsoft Excel x.0 Object Library
Private Sub Command1_Click()
Dim xlApp As Excel.Application '定义EXCEL类
Dim xlBook As Excel.Workbook '定义工件簿类
Dim xlsheet As Excel.Worksheet '定义工作表类
Dim pubConn As New ADODB.Connection
Dim rsTable As New ADODB.Recordset
Dim strConn As String
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
xlApp.Visible = False '设置EXCEL可见
Set xlBook = xlApp.Workbooks.Open(App.Path & "\Book1.xls") '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表
xlsheet.Activate '激活工作表
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb" & ";Persist Security Info=False"
pubConn.Open strConn
rsTable.CursorLocation = adUseClient
rsTable.Open "Select * From Table2", pubConn, adOpenStatic, adLockOptimistic
xlApp.Worksheets(1).Range("A2").CopyFromRecordset rsTable
xlsheet.Cells(rsTable.RecordCount + 2, 1) = "汇总"
xlsheet.Cells(rsTable.RecordCount + 2, 2) = xlApp.WorksheetFunction.Sum(xlsheet.Range("B2:B" & CStr(rsTable.RecordCount + 1)))
xlBook.Save
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
rsTable.Close
Set rsTable = Nothing
pubConn.Close
Set pubConn = Nothing
MsgBox "Ok"
End Sub