Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: Excel process is not terminated

    Hi Gurus,

    I have a simple access vba sub to delete excel rows in a excel worksheet.

    Then I have a command button on a form to call the delete sub to delete the rows. However, when I try to manually open the excel to check if the rows actually be deleted, I can not open it. Then I check the windows task manager, I found that the excel process is not terminated. I have to end the excel process in windows task manager to open the excel file manaully. The rows actually had been deleted.

    Would you please help me out what's wrong with the code and why the excel process is not terminated after the deletion.

    Thank you very much!

    Public Sub delete()

    Dim oXL As Object
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet

    Set oXL = CreateObject("Excel.Application")


    Set oBook = oXL.Workbooks.Open("C:\Refs.xls")
    Set oSheet = oBook.Worksheets(1)
    oXL.Visible = False

    oXL.DisplayAlerts = False
    Rows("1:6").delete

    Set oSheet = Nothing
    oBook.Close SaveChanges:=True



    Set oBook = Nothing

    oXL.Quit

    Set oXL = Nothing


    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try by creating a reference to the Microsoft Excel xx Object Library (In VBA Editor: Tools --> References) and Dim the Excel object instead of using CreateObject. Here is the general schema:
    Code:
    Function OpenExcelDocument(DocName As String)
    
        Dim appXL As Excel.Application
        
        Set appXL = New Excel.Application
        With appXL
            .Workbooks.Open Filename:=DocName
            '
            ' Your code goes here
            '
            .ActiveWorkbook.Save
            .ActiveWorkbook.Close
            .Quit
        End With
        Set appXL = Nothing
    
    End Function
    Have a nice day!

  3. #3
    Join Date
    Aug 2007
    Posts
    88
    Thank you so much for help.

    Sorry. I am not familiar with the syntax of Access VBA with Excel manipulation.

    I changed the code to use New Excel.Application rather than create an Excel application object. However, the Excel process is still in the Windows Task manager.

    Is it due to the order of set oSheet=Nothing, set oBook=Nothing, set oXL = Nothing??

    Thanks again!

    Public Sub delete()

    Dim oXL As Excel.Application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet

    Set oXL = New Excel.Application

    Set oBook = oXL.Workbooks.Open("C:\Refs.xls")

    Set oSheet = oBook.Worksheets(1)
    oXL.Visible = False

    oXL.DisplayAlerts = False
    Rows("1:6").delete

    oXL.ActiveWorkbook.Save
    Set oSheet = Nothing

    oXL.ActiveWorkbook.Close


    Set oBook = Nothing

    oXL.Quit

    Set oXL = Nothing




    End Sub


    Quote Originally Posted by Sinndho View Post
    Try by creating a reference to the Microsoft Excel xx Object Library (In VBA Editor: Tools --> References) and Dim the Excel object instead of using CreateObject. Here is the general schema:
    Code:
    Function OpenExcelDocument(DocName As String)
    
        Dim appXL As Excel.Application
        
        Set appXL = New Excel.Application
        With appXL
            .Workbooks.Open Filename:=DocName
            '
            ' Your code goes here
            '
            .ActiveWorkbook.Save
            .ActiveWorkbook.Close
            .Quit
        End With
        Set appXL = Nothing
    
    End Function

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I suspect it's this line:

    Rows("1:6").delete

    Because you didn't use the variable, Access initiated another instance of Excel. Easy to test; either add the variable to that or comment that line out and see if you still get a hanging process.
    Paul

  5. #5
    Join Date
    Aug 2007
    Posts
    88
    Thank you so much! You are absolutely right about it.

    I change it to

    oSheet.Rows("1:6").delete


    rather than

    Rows("1:6").delete

    and it works fine now.

    Thanks again!


    Quote Originally Posted by pbaldy View Post
    I suspect it's this line:

    Rows("1:6").delete

    Because you didn't use the variable, Access initiated another instance of Excel. Easy to test; either add the variable to that or comment that line out and see if you still get a hanging process.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

  7. #7
    Join Date
    Aug 2007
    Posts
    88
    Thank you so much for being such a nice guy!

    Quote Originally Posted by pbaldy View Post
    Happy to help!

  8. #8
    Join Date
    May 2012
    Posts
    2
    Hello,
    I have the same problem as mentioned above. I use the code below to add a new sheet to an excel file.The code works ,but i noticed that when the code ends there is still an instance of Excel running in Task Manager. I think that i have followed the instruction given to earlier posts but with no luck..

    Thank you in advance for your help

    Code:
    Dim str As String
    Dim rs As Recordset
    Dim WKBOOK As Excel.Workbook
    Dim oapp2 As Excel.Application
    Dim oSheet As Excel.Worksheet
    
     Set oapp2 = New Excel.Application
       
       
        LEXCELDOC = "C:\Desktop\test.xlsx"
        oapp2.Visible = True
        Set WKBOOK = oapp2.Workbooks.Open(LEXCELDOC)
    
    WKBOOK.Sheets.Add after:=Sheets(Sheets.Count)
    WKBOOK.Sheets(Sheets.Count).Name = "New"
    oapp2.ActiveWorkbook.Save
    
     Set oSheet = Nothing
    
     WKBOOK.Close
    Set WKBOOK = Nothing
    
    oapp2.Quit
    Set oapp2 = Nothing

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    My guess would be the references to Sheets.Count.
    Paul

  10. #10
    Join Date
    May 2012
    Posts
    2
    you are right!
    Thank you

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problemo!
    Paul

Posting Permissions

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