需求是将结果集的内容导到指定的excel中,如果该excel之前已存在则先删除,然后创建并保存。
发现执行完后系统总会残留一个excel进程,当打开刚才保存的excel文件的同时会自动打开一个命名为book1的excel(估计就是那个excel进程的内容),我在程序中已经用了.Quit 的方法了,为什么还没有完全退出excel呢?附代码如下:
Public Sub exportToExcel(rs As ADODB.Recordset)
Dim lRow As Long
Dim sXLSPath As String Dim objWork As Excel.Workbook
Dim objSheet As Excel.Worksheet
sXLSPath = "f:\" & "maindata.xls"
Set fso = CreateObject("scripting.filesystemobject") If fso.FileExists(sXLSPath) Then '判断要保存的excel文件是否已存在
Set myfile = fso.getfile(sXLSPath)
myfile.Delete
End If
Set objExcel = CreateObject("excel.application")
Set objWork = objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveSheet
objSheet.Range(Chr(65) & "1") = "用户"
objSheet.Range(Chr(65) & "1").ColumnWidth = 20
objSheet.Range(Chr(66) & "1") = "附件操作时间"
objSheet.Range(Chr(66) & "1").ColumnWidth = 10
objSheet.Range(Chr(67) & "1") = "附件涉及模块"
objSheet.Range(Chr(67) & "1").ColumnWidth = 8
objSheet.Range(Chr(68) & "1") = "附件操作描述"
objSheet.Range(Chr(68) & "1").ColumnWidth = 35
If rs.EOF = True Then
rs.Close: Set rs = Nothing
Exit Sub
End If
Do While rs.EOF = False
lRow = lRow + 1
objSheet.Cells(lRow + 1, 1) = rs.Fields(0)
objSheet.Cells(lRow + 1, 2) = rs.Fields(1)
objSheet.Cells(lRow + 1, 3) = rs.Fields(2)
objSheet.Cells(lRow + 1, 4) = rs.Fields(3)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
objExcel.DisplayAlerts = False
objWork.SaveAs FileName:="f:\" & "maindata.xls", FileFormat:=xlNormal, Password:="", writerespassword:="", ReadOnlyRecommended:=false, CreateBackup:=False
objWork.Application.Quit
objExcel.Application.Quit
Set objSheet = Nothing
Set objWork = Nothing
Set objExcel = Nothing
Set fso = Nothing
End Sub
发现执行完后系统总会残留一个excel进程,当打开刚才保存的excel文件的同时会自动打开一个命名为book1的excel(估计就是那个excel进程的内容),我在程序中已经用了.Quit 的方法了,为什么还没有完全退出excel呢?附代码如下:
Public Sub exportToExcel(rs As ADODB.Recordset)
Dim lRow As Long
Dim sXLSPath As String Dim objWork As Excel.Workbook
Dim objSheet As Excel.Worksheet
sXLSPath = "f:\" & "maindata.xls"
Set fso = CreateObject("scripting.filesystemobject") If fso.FileExists(sXLSPath) Then '判断要保存的excel文件是否已存在
Set myfile = fso.getfile(sXLSPath)
myfile.Delete
End If
Set objExcel = CreateObject("excel.application")
Set objWork = objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveSheet
objSheet.Range(Chr(65) & "1") = "用户"
objSheet.Range(Chr(65) & "1").ColumnWidth = 20
objSheet.Range(Chr(66) & "1") = "附件操作时间"
objSheet.Range(Chr(66) & "1").ColumnWidth = 10
objSheet.Range(Chr(67) & "1") = "附件涉及模块"
objSheet.Range(Chr(67) & "1").ColumnWidth = 8
objSheet.Range(Chr(68) & "1") = "附件操作描述"
objSheet.Range(Chr(68) & "1").ColumnWidth = 35
If rs.EOF = True Then
rs.Close: Set rs = Nothing
Exit Sub
End If
Do While rs.EOF = False
lRow = lRow + 1
objSheet.Cells(lRow + 1, 1) = rs.Fields(0)
objSheet.Cells(lRow + 1, 2) = rs.Fields(1)
objSheet.Cells(lRow + 1, 3) = rs.Fields(2)
objSheet.Cells(lRow + 1, 4) = rs.Fields(3)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
objExcel.DisplayAlerts = False
objWork.SaveAs FileName:="f:\" & "maindata.xls", FileFormat:=xlNormal, Password:="", writerespassword:="", ReadOnlyRecommended:=false, CreateBackup:=False
objWork.Application.Quit
objExcel.Application.Quit
Set objSheet = Nothing
Set objWork = Nothing
Set objExcel = Nothing
Set fso = Nothing
End Sub
Set objSheet = Nothing
Set objWork = Nothing
objExcel.Quit
Set objExcel = Nothing
试了一下,还不行,还是会有那个book1的excel进程在,另外说明一下,我是在form中的一个按钮的单击事件中用到了上面的导出方法的。
一次性使用的变量都应该在过程中定义,fso也一样、并且不用时(在操作 Excel 前)要马上释放。