Access引用Excel为何excel进程关不掉

Henry D. Sy

Access引用Excel为何excel进程关不掉
下面的附件是
在Access中引用Excel,生成excel数据透视表到工作表.
第一次运行一切正常,再次运行即出现错误.
查出出错原因是进程里还有excel程序
请大家帮忙看看.

1. Private Sub CreatePivotTable_Click()
   
2. 
   
3.     Dim XLA As New Excel.Application
   
4.     Dim XLB As Workbook
   
5.     Dim XLS As Worksheet
   
6.     Dim PC As PivotCache
   
7.     Dim PT As PivotTable
   
8.     Dim rs As New ADODB.Recordset
   
9.     Dim sSQL As String
   
10. 
    
11. 
    
12.     Set XLA = CreateObject("Excel.Application")
    
13.     Set XLB = Nothing
    
14.     Set XLS = Nothing
    
15.     Set XLB = XLA.Workbooks().Add
    
16. 
    
17.     XLB.SaveAs CurrentProject.Path & "\PivotTable.xlsx"
    
18. 
    
19.     Set XLS = XLB.Worksheets.Add
    
20.     XLS.Name = "PivotSheet"
    
21.     XLS.Activate
    
22. 
    
23.     rs.CursorLocation = adUseClient
    
24. 
    
25.     sSQL = "SELECT * FROM ORD"
    
26.     rs.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    
27. 
    
28.     Set PC = ActiveWorkbook.PivotCaches.Create(xlExternal)
    
29.     Set PC.Recordset = rs
    
30. 
    
31.     Set PT = ActiveSheet.PivotTables.Add(PC, Range("A1"), "A")
    
32. 
    
33.     With ActiveSheet.PivotTables("A")
    
34.         .PivotFields("STYLE").Orientation = xlPageField
    
35.         .PivotFields("PO").Orientation = xlRowField
    
36.         .PivotFields("COLOR").Orientation = xlRowField
    
37.         .PivotFields("pack").Orientation = xlRowField
    
38.         .PivotFields("SIZE").Orientation = xlColumnField
    
39.         .PivotFields("QUANTITY").Orientation = xlDataField
    
40.     End With
    
41. 
    
42.     rs.Close
    
43. 
    
44.     XLB.Save
    
45. 
    
46.     MsgBox "PivotTable Saved On " & CurrentProject.Path
    
47. 
    
48.     Set PC = Nothing
    
49.     Set PT = Nothing
    
50. 
    
51. 
    
52.     Set rs = Nothing
    
53.     Set XLB = Nothing
    
54.     Set XLS = Nothing
    
55.     Set XLA = Nothing
    
56.     XLA.Quit '特意加上这句还是留住在进程里
    
57. 
    
58. End Sub
    
59. 
    

复制代码

tmtony

Excel 也要调用自己的先关闭一下
然后先关闭 小的对象
再关闭大的对象
最后再设置 为nothing

Henry D. Sy

tmtony 发表于 2016-9-13 21:15
Excel 也要调用自己的先关闭一下
然后先关闭 小的对象
再关闭大的对象

谢谢站长,我试试.
Excel 也要调用自己的先关闭一下
这句话的意思,是指?

Henry D. Sy

我原来是生成好透视表,excel是可见的
然后,手工关闭excel
XLA.Visible = True
XLA.WindowState = xlMaximized

liwen

Private Sub CreatePivotTable_Click()

    Dim XLA As New Excel.Application
    Dim XLB As Workbook
    Dim XLS As Worksheet
    Dim PC As PivotCache
    Dim PT As PivotTable
    Dim rs As New ADODB.Recordset
    Dim sSQL As String


    Set XLA = CreateObject("Excel.Application")
    Set XLB = Nothing
    Set XLS = Nothing
    Set XLB = XLA.Workbooks().Add

    XLB.SaveAs CurrentProject.Path & "\PivotTable.xlsx"

    Set XLS = XLB.Worksheets.Add
    XLS.Name = "PivotSheet"
    XLS.Activate

    rs.CursorLocation = adUseClient
    sSQL = "SELECT style,po,color,pack,quantity FROM ORD"
    rs.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

    Set PC = XLA.ActiveWorkbook.PivotCaches.Create(xlExternal)
    Set PC.Recordset = rs

    Set PT = XLA.ActiveSheet.PivotTables.Add(PC, XLA.ActiveSheet.Range("A1"), "A")

    With XLA.ActiveSheet.PivotTables("A")
        .PivotFields("STYLE").Orientation = xlPageField
        .PivotFields("PO").Orientation = xlRowField
        .PivotFields("COLOR").Orientation = xlRowField
        .PivotFields("pack").Orientation = xlRowField
'      .PivotFields("SIZE").Orientation = xlColumnField
        .PivotFields("QUANTITY").Orientation = xlDataField
    End With

    rs.Close

    XLB.Save

    MsgBox "PivotTable Saved On " & CurrentProject.Path
    Set PC = Nothing
    Set PT = Nothing
    Set rs = Nothing
    Set XLS = Nothing

XLB.Close

    Set XLB = Nothing
    XLA.Quit
    Set XLA = Nothing

End Sub

Henry D. Sy

liwen 发表于 2016-9-13 22:16
Private Sub CreatePivotTable_Click()

    Dim XLA As New Excel.Application

谢谢liwen版,可以了.
对excel这是一点不懂,没有方向.

roych

Henry D. Sy 发表于 2016-9-14 01:00
谢谢liwen版,可以了.
对excel这是一点不懂,没有方向.

顺序上，应该是先关闭工作簿，再退出excel组件。最后再设置nothing，例如：
wkbkObj.close
exlObj.Quit

Henry D. Sy

roych 发表于 2016-9-14 12:24
顺序上，应该是先关闭工作簿，再退出excel组件。最后再设置nothing，例如：
wkbkObj.close
exlObj.Qu ...

谢谢你
透视表生成完毕,excel是可见的,
然后在手工关闭的.
程序出现错误,除了顺序有点问题,
主要原因还是关键字"SIZE"引起的