这段写EXCEL报表的程序运行时出现异常!

lcj49997 2005-10-04 09:10:07
一个产生日报的函数如下,在有些机器上调用时会出现异常,大致是:Excel应用程序关闭...在创建错误日志。报表还是正确生成了,就是每次调用的时候弹出一个异常,很不方便的,请高手帮我看一下是不是代码有问题。
Private Sub input_ribao(strdate As String)

Dim strsource1, strsource, strdestination, strdestination1, strdestination2, strdestination3 As String

Dim xlapp As Excel.Application
Dim xlbook1 As Excel.Workbook
Dim xlsheet1 As Excel.Worksheet

Dim xlbook2 As Excel.Workbook
Dim xlsheet2 As Excel.Worksheet
Dim xlbook3 As Excel.Workbook
Dim xlsheet3 As Excel.Worksheet

Set xlapp = New Excel.Application
Set xlapp = CreateObject("Excel.Application")

strsource1 = "d:\template\template1.xls"
strdestination1 = "d:\baobiaoku\banbao_beiyong\" + Format(strdate, "yyyy_mm_dd") + "_00" + ".xls"
If Not fileexists(strdestination1) Then
FileCopy strsource1, strdestination1
End If
Set xlbook1 = xlapp.Workbooks.Open(strdestination1)
Set xlsheet1 = xlbook1.Sheets(1)

strdestination2 = "d:\baobiaoku\banbao_beiyong\" + Format(strdate, "yyyy_mm_dd") + "_08" + ".xls"
If Not fileexists(strdestination2) Then
FileCopy strsource1, strdestination2
End If
Set xlbook2 = xlapp.Workbooks.Open(strdestination2)
Set xlsheet2 = xlbook2.Sheets(1)

strdestination3 = "d:\baobiaoku\banbao_beiyong\" + Format(strdate, "yyyy_mm_dd") + "_16" + ".xls"
If Not fileexists(strdestination3) Then
FileCopy strsource1, strdestination3
End If
Set xlbook3 = xlapp.Workbooks.Open(strdestination3)
Set xlsheet3 = xlbook3.Sheets(1)


strdestination = "d:\baobiaoku\ribao\ribao.xls"
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
If Not fileexists(strdestination) Then
strsource1 = "d:\template\template2.xls"
FileCopy strsource1, strdestination
End If
Set xlbook = xlapp.Workbooks.Open(strdestination)
Set xlsheet = xlbook.Sheets(1)

Dim L As Single
L = 7
xlsheet.Cells(L, 5) = xlsheet1.Cells(25 - L, 4) + xlsheet2.Cells(25 - L, 4) + xlsheet3.Cells(25 - L, 4)
xlsheet.Cells(L, 7) = xlsheet1.Cells(25 - L, 7) + xlsheet2.Cells(25 - L, 7) + xlsheet3.Cells(25 - L, 7)
xlsheet.Cells(L, 9) = xlsheet1.Cells(25 - L, 10) + xlsheet2.Cells(25 - L, 10) + xlsheet3.Cells(25 - L, 10)
xlsheet.Cells(L, 11) = xlsheet1.Cells(25 - L, 13) + xlsheet2.Cells(25 - L, 13) + xlsheet3.Cells(25 - L, 13)
xlsheet.Cells(L, 6) = xlsheet1.Cells(26 - L, 4) + xlsheet2.Cells(26 - L, 4) + xlsheet3.Cells(26 - L, 4)
xlsheet.Cells(L + 1, 6) = xlsheet1.Cells(27 - L, 4) + xlsheet2.Cells(27 - L, 4) + xlsheet3.Cells(27 - L, 4)
xlsheet.Cells(L + 2, 6) = xlsheet1.Cells(28 - L, 4) + xlsheet2.Cells(28 - L, 4) + xlsheet3.Cells(28 - L, 4)
xlsheet.Cells(L + 3, 6) = xlsheet1.Cells(29 - L, 4) + xlsheet2.Cells(29 - L, 4) + xlsheet3.Cells(29 - L, 4)
xlsheet.Cells(L + 4, 6) = xlsheet1.Cells(30 - L, 4) + xlsheet2.Cells(30 - L, 4) + xlsheet3.Cells(30 - L, 4)
xlsheet.Cells(L, 8) = xlsheet1.Cells(26 - L, 7) + xlsheet2.Cells(26 - L, 7) + xlsheet3.Cells(26 - L, 7)
xlsheet.Cells(L + 2, 8) = xlsheet1.Cells(28 - L, 7) + xlsheet2.Cells(28 - L, 7) + xlsheet3.Cells(28 - L, 7)
xlsheet.Cells(L + 1, 8) = xlsheet1.Cells(27 - L, 7) + xlsheet2.Cells(27 - L, 7) + xlsheet3.Cells(27 - L, 7)
xlsheet.Cells(L + 3, 8) = xlsheet1.Cells(29 - L, 7) + xlsheet2.Cells(29 - L, 7) + xlsheet3.Cells(29 - L, 7)
xlsheet.Cells(L + 4, 8) = xlsheet1.Cells(30 - L, 7) + xlsheet2.Cells(30 - L, 7) + xlsheet3.Cells(30 - L, 7)
xlsheet.Cells(L, 10) = xlsheet1.Cells(26 - L, 10) + xlsheet2.Cells(26 - L, 10) + xlsheet3.Cells(26 - L, 10)
xlsheet.Cells(L + 1, 10) = xlsheet1.Cells(27 - L, 10) + xlsheet2.Cells(27 - L, 10) + xlsheet3.Cells(27 - L, 10)
xlsheet.Cells(L + 2, 10) = xlsheet1.Cells(28 - L, 10) + xlsheet2.Cells(28 - L, 10) + xlsheet3.Cells(28 - L, 10)
xlsheet.Cells(L + 3, 10) = xlsheet1.Cells(29 - L, 10) + xlsheet2.Cells(29 - L, 10) + xlsheet3.Cells(29 - L, 10)
xlsheet.Cells(L + 4, 10) = xlsheet1.Cells(30 - L, 10) + xlsheet2.Cells(30 - L, 10) + xlsheet3.Cells(30 - L, 10)
xlsheet.Cells(L, 12) = xlsheet1.Cells(26 - L, 13) + xlsheet2.Cells(26 - L, 13) + xlsheet3.Cells(26 - L, 13)
xlsheet.Cells(L + 1, 12) = xlsheet1.Cells(27 - L, 13) + xlsheet2.Cells(27 - L, 13) + xlsheet3.Cells(27 - L, 13)
xlsheet.Cells(L + 2, 12) = xlsheet1.Cells(28 - L, 13) + xlsheet2.Cells(28 - L, 13) + xlsheet3.Cells(28 - L, 13)
xlsheet.Cells(L + 3, 12) = xlsheet1.Cells(29 - L, 13) + xlsheet2.Cells(29 - L, 13) + xlsheet3.Cells(29 - L, 13)
xlsheet.Cells(L + 4, 12) = xlsheet1.Cells(30 - L, 13) + xlsheet2.Cells(30 - L, 13) + xlsheet3.Cells(30 - L, 13)
xlbook.SaveAs ("d:\baobiaoku\ribao\" + Format(Date, "yyyy_mm_dd") + ".xls")

xlapp.Quit



Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
Set xlbook1 = Nothing
Set xlsheet1 = Nothing
Set xlbook2 = Nothing
Set xlsheet2 = Nothing
Set xlbook3 = Nothing
Set xlsheet3 = Nothing

End Sub
...全文
66 点赞 收藏 3
写回复
3 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
lcj49997 2005-10-15
谢谢两位高手的答复!
回复
gowowo 2005-10-06
我不能再现你的问题只能估计原因为:
(1)重复创建 Excel.Application对象 (2)Excel.Application对象不可视.
解决办法:
(1) 删除 “ Set xlapp = New Excel.Application ” 行
(2) 把 “ Set xlapp = CreateObject("Excel.Application") ” 改为
“ Set xlapp = CreateObject("Excel.Application"):xlapp.Application.Visible = True ”

回复
faysky2 2005-10-06

你把捕捉异常的语句去掉,看错误出现在哪一语句上
回复
发帖
VBA
创建于2007-09-28

2062

社区成员

VBA(Visual Basic for Applications)是Visual Basic的一种宏语言,是在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。
申请成为版主
帖子事件
创建了帖子
2005-10-04 09:10
社区公告
暂无公告