'引用Microsoft Excel X.0 Object Library Private Sub Command1_Click() Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xSheet As Excel.Worksheet Set xlApp = New Excel.Application Set xlBook = xlApp.Workbooks.Open("C:\Book1.xls") Set xSheet = xBook.Worksheets(1) xlApp.Visible=True '...... xlBook.Close False xlApp.Quit ------> '关闭进程 Set xlBook = Nothing Set xlApp = Nothing end sub
'//导出到EXCEL表格中... Private Sub OutEXCEL(sFileName As String) '生成EXCEL表格 Dim ExcelObject As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim lXunHuan As Long, lCount As Long Set ExcelObject = CreateObject("Excel.Application") Set xlBook = ExcelObject.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) xlSheet.Activate '//显示表头 xlSheet.Range("A1:N1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Rows("1:1").RowHeight = 30 xlSheet.Range("A1:N1").Select ActiveCell.FormulaR1C1 = sReportName xlSheet.Range("A2").Select
'最后再居中什么的 For lXunHuan = 1 To frm8.lvwDriverData.ColumnHeaders.Count With xlSheet.Cells(2, lXunHuan) .FormulaR1C1 = frm8.lvwDriverData.ColumnHeaders.Item(lXunHuan).Text .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter '.Font.Bold = True '严重影响打开excel的速度 .RowHeight = 22 .EntireColumn.AutoFit End With Next
'依次读入lvw中的条目 For lXunHuan = 0 To frm8.lvwDriverData.ListItems.Count - 1 xlSheet.Cells(lXunHuan + 3, 1).FormulaR1C1 = frm8.lvwDriverData.ListItems(lXunHuan + 1).Text xlSheet.Cells(lXunHuan + 3, 1).EntireColumn.AutoFit For lCount = 1 To frm8.lvwDriverData.ColumnHeaders.Count - 1 With xlSheet.Cells(lXunHuan + 3, lCount + 1) .FormulaR1C1 = frm8.lvwDriverData.ListItems(lXunHuan + 1).SubItems(lCount) .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .EntireColumn.AutoFit End With Next Next
'设置报表的边框为黑线 xlSheet.Range("A1:N" & CStr(frm8.lvwDriverData.ListItems.Count + 2)).Select With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With
xlBook.SaveAs sFileName Set xlSheet = Nothing xlBook.Close Set xlBook = Nothing ExcelObject.Quit Set ExcelObject = Nothing 'ExcelObject.Visible = True MsgBox "导出完成。" End Sub
Private Sub Command1_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xSheet As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("C:\Book1.xls")
Set xSheet = xBook.Worksheets(1)
xlApp.Visible=True
'......
xlBook.Close False
xlApp.Quit ------> '关闭进程
Set xlBook = Nothing
Set xlApp = Nothing
end sub
可我希望是执行完导出过程后,EXCEL进程就退出。
这又该如何解决?
Set xlSheet = Nothing
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
http://blog.joycode.com/mvm/archive/2004/04/25/20208.aspx
另外已经setnothing 的对象一定不要在引用任何 的属性什么的
引用 和关闭的顺序 也要注意
Private Sub OutEXCEL(sFileName As String)
'生成EXCEL表格
Dim ExcelObject As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim lXunHuan As Long, lCount As Long
Set ExcelObject = CreateObject("Excel.Application")
Set xlBook = ExcelObject.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Activate '//显示表头
xlSheet.Range("A1:N1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Rows("1:1").RowHeight = 30
xlSheet.Range("A1:N1").Select
ActiveCell.FormulaR1C1 = sReportName
xlSheet.Range("A2").Select
'最后再居中什么的
For lXunHuan = 1 To frm8.lvwDriverData.ColumnHeaders.Count
With xlSheet.Cells(2, lXunHuan)
.FormulaR1C1 = frm8.lvwDriverData.ColumnHeaders.Item(lXunHuan).Text
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
'.Font.Bold = True '严重影响打开excel的速度
.RowHeight = 22
.EntireColumn.AutoFit
End With
Next
'依次读入lvw中的条目
For lXunHuan = 0 To frm8.lvwDriverData.ListItems.Count - 1
xlSheet.Cells(lXunHuan + 3, 1).FormulaR1C1 = frm8.lvwDriverData.ListItems(lXunHuan + 1).Text
xlSheet.Cells(lXunHuan + 3, 1).EntireColumn.AutoFit
For lCount = 1 To frm8.lvwDriverData.ColumnHeaders.Count - 1
With xlSheet.Cells(lXunHuan + 3, lCount + 1)
.FormulaR1C1 = frm8.lvwDriverData.ListItems(lXunHuan + 1).SubItems(lCount)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.EntireColumn.AutoFit
End With
Next
Next
'设置报表的边框为黑线
xlSheet.Range("A1:N" & CStr(frm8.lvwDriverData.ListItems.Count + 2)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
xlBook.SaveAs sFileName
Set xlSheet = Nothing
xlBook.Close
Set xlBook = Nothing
ExcelObject.Quit
Set ExcelObject = Nothing
'ExcelObject.Visible = True
MsgBox "导出完成。"
End Sub