VBA中EXCEL报表的问题!
用如下函数实现报表自动生成时,运行结果对的,但会出现异常,请高手帮忙看看什么原因?代码和异常提示如下:
Private Sub ribao_linshi(time1 As String)
Dim strsource1, strsource, strdestination, strdestination1, strdestination2, strdestination3 As String
Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application"): xlapp.Application.Visible = False
strdestination = "d:\baobiao\ribao\linshi.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)
If ((time1 >= "07" And time1 <= "09")) Then
xlsheet.Cells(8, 2) = Format(Time, "hh:mm")
xlsheet.Cells(8, 3) = var1
xlsheet.Cells(8, 4) = var2
xlsheet.Cells(8, 5) = var3
xlsheet.Cells(8, 6) = var4
xlsheet.Cells(8, 7) = var5
xlbook.Save
End If
If ((time1 >= "15" And time1 <= "17")) Then
xlsheet.Cells(9, 2) = Format(Time, "hh:mm")
xlsheet.Cells(9, 3) = var6
xlsheet.Cells(9, 4) = var7
xlsheet.Cells(9, 5) = var8
xlsheet.Cells(9, 6) = var9
xlsheet.Cells(9, 7) = var10
xlbook.Save
End If
If ((time1 >= "00" And time1 <= "01") Or (time1 >= "23")) Then
xlsheet.Cells(10, 2) = Format(Time, "hh:mm")
xlsheet.Cells(10, 3) = var11
xlsheet.Cells(10, 4) = var12
xlsheet.Cells(10, 5) = var13
xlsheet.Cells(10, 6) = var14
xlsheet.Cells(10, 7) = var15
'xlbook.Save
'写日报
xlsheet.Cells(8, 1) = Format(Date, "yyyy.mm.dd")
xlsheet.Cells(11, 3) = xlsheet.Cells(8, 3) + xlsheet.Cells(9, 3) + xlsheet.Cells(10, 3)
xlsheet.Cells(11, 4) = xlsheet.Cells(8, 4) + xlsheet.Cells(9, 4) + xlsheet.Cells(10, 4)
xlsheet.Cells(11, 5) = xlsheet.Cells(8, 5) + xlsheet.Cells(9, 5) + xlsheet.Cells(10, 5)
xlsheet.Cells(11, 6) = xlsheet.Cells(8, 6) + xlsheet.Cells(9, 6) + xlsheet.Cells(10, 6)
xlsheet.Cells(11, 7) = xlsheet.Cells(8, 7) + xlsheet.Cells(9, 7) + xlsheet.Cells(10, 7)
'xlbook.Save
xlbook.SaveAs ("d:\baobiaoku\ribao\" + Format(Date, "yyyy_mm_dd") + ".xls")
If fileexists(strdestination) Then
Kill (strdestination)
End If
End If
If ((time1 >= "00" And time1 <= "01") Or (time1 >= "23")) Then
'写月报
Dim xlbook1 As Excel.Workbook
Dim xlsheet1 As Excel.Worksheet
Dim dest1 As String
dest1 = "d:\baobiaoku\yuebao\" + Format(Date, "yyyy_mm") + ".xls"
If Not fileexists(dest1) Then
strsource1 = "d:\template\template3.xls"
FileCopy strsource1, dest1
End If
Set xlbook1 = xlapp.Workbooks.Open(dest1)
Set xlsheet1 = xlbook1.Sheets(1)
'判断哪一行
Dim rowflag As Integer
rowflag = Format(Date, "dd")
xlsheet1.Cells(rowflag + 4, 3) = xlsheet.Cells(11, 3)
xlsheet1.Cells(rowflag + 4, 4) = xlsheet.Cells(11, 4)
xlsheet1.Cells(rowflag + 4, 5) = xlsheet.Cells(11, 5)
xlsheet1.Cells(rowflag + 4, 6) = xlsheet.Cells(11, 6)
xlsheet1.Cells(rowflag + 4, 7) = xlsheet.Cells(11, 7)
xlsheet1.Cells(36, 3) = xlsheet1.Cells(36, 3) + xlsheet1.Cells(rowflag + 4, 3) '计算总量
xlsheet1.Cells(36, 4) = xlsheet1.Cells(36, 4) + xlsheet1.Cells(rowflag + 4, 4)
xlsheet1.Cells(36, 5) = xlsheet1.Cells(36, 5) + xlsheet1.Cells(rowflag + 4, 5)
xlsheet1.Cells(36, 6) = xlsheet1.Cells(36, 6) + xlsheet1.Cells(rowflag + 4, 6)
xlsheet1.Cells(36, 7) = xlsheet1.Cells(36, 7) + xlsheet1.Cells(rowflag + 4, 7)
xlsheet1.Cells(5, 1) = Format(Date, "yyyy_mm")
xlbook1.Save
End If
xlapp.Quit
Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
Set xlbook1 = Nothing
Set xlsheet1 = Nothing
End Sub
单步调试时,异常出现在倒数第三行“Set xlbook1 = Nothing”,异常提示为“Excel产生了错误,会被Windows关闭,您需要重新启动程序。正在创建错误日志”,请大侠帮忙看看错误在哪里,由于我不大懂VBA,很多代码写的不规范,请指教。