strsql = Text2.Text Set rs = ExecuteSQL(strsql, msgtext) For i = 6 To rs.RecordCount + 5 xlapp1.ActiveSheet.Rows(i).Insert xlapp1.Worksheets(1).Cells(i, 1) = i - 5 xlapp1.Worksheets(1).Cells(i, 2) = rs.Fields("单位名称") xlapp1.Worksheets(1).Cells(i, 3) = rs.Fields("计划总额") xlapp1.Worksheets(1).Cells(i, 4) = rs.Fields("付款总额") xlapp1.Worksheets(1).Cells(i, 5) = rs.Fields("完成资产金额") xlapp1.Worksheets(1).Cells(i, 6) = rs.Fields("预付款金额") xlapp1.Worksheets(1).Cells(i, 7) = rs.Fields("付款金额") xlapp1.Worksheets(1).Cells(i, 8) = rs.Fields("差额") jhze = jhze + rs.Fields("计划总额") wczcje = jhje + rs.Fields("完成资产金额") yfkje = jhje + rs.Fields("预付款金额") fkje = fkje + rs.Fields("付款金额") fkze = fkze + rs.Fields("付款总额") ce = ce + rs.Fields("差额") rs.MoveNext Next i xlapp1.ActiveSheet.Rows(5).Delete xlapp1.Worksheets(1).Cells(4, 3) = jhze xlapp1.Worksheets(1).Cells(4, 4) = fkze xlapp1.Worksheets(1).Cells(4, 5) = wczcje xlapp1.Worksheets(1).Cells(4, 6) = yfkje xlapp1.Worksheets(1).Cells(4, 7) = fkje xlapp1.Worksheets(1).Cells(4, 8) = ce With CommonDialog1 .DialogTitle = "生成Excel" .FileName = "*.xls" .Filter = "(Excel)*.xls|*.xls" .CancelError = True .ShowOpen '.ShowSave End With 'xlapp1.Save xlapp1.ActiveWorkbook.SaveAs (CommonDialog1.FileName)
xlapp1.Quit MsgBox "数据导Excel完成!", 48, "信息" rs.Close Set rs = Nothing Exit Sub ErrHandler: '用户按了“取消”按钮 MsgBox "用户取消从Excel导出数据操作!", 48, "提示" Exit Sub End Sub
mshflexgrid的记录是记录集吧,也就是rs
参考一下
Private Sub cmdExcel_Click()
On Error GoTo ErrHandler
Set xlapp1 = CreateObject("excel.application") 'create the excel object
xlapp1.Workbooks.Open (App.Path & "\按单位查询模板.xls") 'FileName changed
xlapp1.Workbooks("按单位查询模板.xls").Activate
xlapp1.Worksheets(1).Cells(1, 1) = Text1.Text & "年按单位统计的完成资产统计表"
strsql = Text2.Text
Set rs = ExecuteSQL(strsql, msgtext)
For i = 6 To rs.RecordCount + 5
xlapp1.ActiveSheet.Rows(i).Insert
xlapp1.Worksheets(1).Cells(i, 1) = i - 5
xlapp1.Worksheets(1).Cells(i, 2) = rs.Fields("单位名称")
xlapp1.Worksheets(1).Cells(i, 3) = rs.Fields("计划总额")
xlapp1.Worksheets(1).Cells(i, 4) = rs.Fields("付款总额")
xlapp1.Worksheets(1).Cells(i, 5) = rs.Fields("完成资产金额")
xlapp1.Worksheets(1).Cells(i, 6) = rs.Fields("预付款金额")
xlapp1.Worksheets(1).Cells(i, 7) = rs.Fields("付款金额")
xlapp1.Worksheets(1).Cells(i, 8) = rs.Fields("差额")
jhze = jhze + rs.Fields("计划总额")
wczcje = jhje + rs.Fields("完成资产金额")
yfkje = jhje + rs.Fields("预付款金额")
fkje = fkje + rs.Fields("付款金额")
fkze = fkze + rs.Fields("付款总额")
ce = ce + rs.Fields("差额")
rs.MoveNext
Next i
xlapp1.ActiveSheet.Rows(5).Delete
xlapp1.Worksheets(1).Cells(4, 3) = jhze
xlapp1.Worksheets(1).Cells(4, 4) = fkze
xlapp1.Worksheets(1).Cells(4, 5) = wczcje
xlapp1.Worksheets(1).Cells(4, 6) = yfkje
xlapp1.Worksheets(1).Cells(4, 7) = fkje
xlapp1.Worksheets(1).Cells(4, 8) = ce
With CommonDialog1
.DialogTitle = "生成Excel"
.FileName = "*.xls"
.Filter = "(Excel)*.xls|*.xls"
.CancelError = True
.ShowOpen
'.ShowSave
End With
'xlapp1.Save
xlapp1.ActiveWorkbook.SaveAs (CommonDialog1.FileName)
xlapp1.Quit
MsgBox "数据导Excel完成!", 48, "信息"
rs.Close
Set rs = Nothing
Exit Sub
ErrHandler:
'用户按了“取消”按钮
MsgBox "用户取消从Excel导出数据操作!", 48, "提示"
Exit Sub
End Sub