Results 1 to 4 of 4
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Excel Process Not terminated from ACCESS OLE Aut (2000)

    I am using ACCESS 2000 on Windows 2000 Platform
    I have some code on a button that opens an Excel spreadsheet puts data into it and closes it.
    No matter what I do, at the end of the procedure, a PROCESS is left running in the TASK Manager.
    This is causing an OLE Server Errors if the procedure is run a second time.
    But only if the code has to Insert Rows.
    However, it will insert rows with no problem on the first attempt.

    Under Windows 98 it leaves the Application running hidden.

    How can I stop this ?
    Should I use Create Object instead of the Code below?

    CODE Bits
    =========
    The Excel Objects are defined as below

    Dim oXL As New Excel.Application
    Dim oXB As New Excel.Workbook
    Dim oXS As New Excel.Worksheet
    Dim oXR As Range

    The Excel Objects are assigned in this next bit

    oXL.Workbooks.Open (strPath & strBook & ".xls")
    Set oXB = oXL.Workbooks(strBook & ".xls")
    Set oXS = oXB.Worksheets(strSheet)
    oXS.Select

    Data is then written and at the end the objects are closed off with

    oXB.SaveAs strName
    oXB.Close Savechanges:=False
    oXL.Quit

    WriteCostingOK:
    Set dbs = Nothing
    Set rst = Nothing
    Set oXR = Nothing
    Set oXS = Nothing
    Set oXB = Nothing
    Set oXL = Nothing

    I have tried variations of this, but no matter what the Task Monitor
    shows the Excel as a Process after the routine has finished.
    This stays in the Process Stack until the database is closed down.

    Surely it should be cleared at the end of the procedure.

    Any help on this gratefully appreciated.
    Andrew

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Process Not terminated from ACCESS OLE Aut (2000)

    You could try using Dim As New rather than Setting objects.

    Dim xLApp As New Excel.Application
    With xLApp
    .Visible = True
    .Workbooks.Open Filename:="Catamobs1.xls"

    .. clever stuff here


    .ActiveWorkbook.Close
    .Quit
    End With

    This seems to clear Excel ok and has the advantage of being able to use the Object Browser to find commands.

    You will need to set a reference to Excel under Tools/Refrences to make this work though.

    HTH

    Peter

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Excel Process Not terminated from ACCESS OLE Aut (2000)

    I already had Excel defined in this way.
    However, I have altered the code to avoid using any set statements
    and enclosed the block as a with (as per your example).
    NO JOY.
    First time run it completes the procedure OK.
    Second time it crashes with a message

    Error 462 The Remote Server machine does not exist or is unavailable.

    Also when you look, Excel is left as a Process although the APP itself is NOT visible and has completed the QUIT method.

    If all you do is Write DATA to the sheet it always works ok
    If you try to insert a new row

    e.g. The Code has to insert a block of cells
    'Add Rows As Needed
    If intR >= intMeatRows Then
    .Range(Cells(intR + intSMRow - 1, 1), Cells(intR + intSMRow - 1, 7)).Insert (xlShiftDown)
    .Cells(intR + intSMRow - 2, 7).Copy Destination:=Cells(intR + intSMRow - 1, 7)
    intSORow = intSORow + 1
    End If
    This is the TRIP up line.
    There is no problem with the code, it works ok.
    BUT if this conditional line is executed, the process is left open
    If it isn't Excel process terminates completely.

    Then the next time it encounters this line it fails, because excel is left in process stack

    I've tried my original method
    Your method
    Even just using CreateObject.

    It worked just fine in Office 97, but not in OFFICE 2000 or OFFICE XP.

    If you can see anything wrong with the INSERT ROWS code please let me know.
    I cannot, and as I said, it executes flawlessly the first time.
    It is only the second time it hits it that it fails.

    Maybe this is a FEATURE?
    Any ideas gratefully received.
    Andrew

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Excel Process Not terminated from ACCESS OLE Aut (2000)

    Result
    I have answered my own question
    I still do not know why it goes wrong when using the early binding method of Dim oXL As New.
    However, the following works ok in ALL versions.

    1. Do NOT have a reference to the Excel Library in the final working version.
    2. Dim Excel as a standard object using Dim oXL As Object
    3. Create the EXCEL object with Late Binding using Set oXL=CreateObject("Excel.Application")
    4. Keep the existing code as is with the exceptions
    a. Use Range References when making structure changes, and do not use the Excel Intrinsic Variable
    e.g. My Rows are Inserted and Formulas Copied using the lines

    If intR >= intMeatRows Then
    .Range("A" & (intR + intSMRow - 1) & ":G" & (intR + intSMRow - 1)).Insert (2)
    .Range("G" & (intR + intSMRow - 2)).Copy Destination:=.Range("G" & (intR + intSMRow - 1))
    intSORow = intSORow + 1
    End If

    Where Insert(2) represents the intrinsic variable xlShiftDown

    This works perfectly and NO leftovers on the stack.

    It should be noted that
    1. The Code changes STILL fail if I change back to EARLY binding with the DIM
    2. I still have NO explanation as to WHY Excel is left on the Process Heap causing the Server Error
    with the early binding approach.

    Still that's the joy of Microsoft.
    A patch a day helps you waste time not play.

    Thanks for the ideas anyway.
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •