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

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