Access引用Excel為何excel進(jìn)程關(guān)不掉

Henry D. Sy

Access引用Excel為何excel進(jìn)程關(guān)不掉
下面的附件是
在Access中引用Excel,生成excel數(shù)據(jù)透視表到工作表.
第一次運(yùn)行一切正常,再次運(yùn)行即出現(xiàn)錯(cuò)誤.
查出出錯(cuò)原因是進(jìn)程里還有excel程序
請大家?guī)兔�看�?

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 '特意加上這句還是留住在進(jìn)程里
    
57. 
    
58. End Sub
    
59. 
    

復(fù)制代碼

tmtony

Excel 也要調(diào)用自己的先關(guān)閉一下
然后先關(guān)閉 小的對象
再關(guān)閉大的對象
最后再設(shè)置 為nothing

Henry D. Sy

tmtony 發(fā)表于 2016-9-13 21:15
Excel 也要調(diào)用自己的先關(guān)閉一下
然后先關(guān)閉 小的對象
再關(guān)閉大的對象

謝謝站長,我試試.
Excel 也要調(diào)用自己的先關(guān)閉一下
這句話的意思,是指?

Henry D. Sy

我原來是生成好透視表,excel是可見的
然后,手工關(guān)閉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 發(fā)表于 2016-9-13 22:16
Private Sub CreatePivotTable_Click()

    Dim XLA As New Excel.Application

謝謝liwen版,可以了.
對excel這是一點(diǎn)不懂,沒有方向.

roych

Henry D. Sy 發(fā)表于 2016-9-14 01:00
謝謝liwen版,可以了.
對excel這是一點(diǎn)不懂,沒有方向.

順序上，應(yīng)該是先關(guān)閉工作簿，再退出excel組件。最后再設(shè)置nothing，例如：
wkbkObj.close
exlObj.Quit

Henry D. Sy

roych 發(fā)表于 2016-9-14 12:24
順序上，應(yīng)該是先關(guān)閉工作簿，再退出excel組件。最后再設(shè)置nothing，例如：
wkbkObj.close
exlObj.Qu ...

謝謝你
透視表生成完畢,excel是可見的,
然后在手工關(guān)閉的.
程序出現(xiàn)錯(cuò)誤,除了順序有點(diǎn)問題,
主要原因還是關(guān)鍵字"SIZE"引起的