Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel persists after closing (XP)

    Can anyone see why the instance of Excel the enclosed code starts persists, after the resulting Excel app is closed? Excel wasn
    Regards,
    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel persists after closing (XP)

    I see you starting the application by creating objExcel (although I have no idea where you declared the variable or what kind it is), but I don't see anything that exits the application, only a line that sets the object variable to Nothing. You save the workbook but you don't quit Excel.
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel persists after closing (XP)

    Thanks for the reply Charlotte. objExcel is dimmed as Public objExcel As Excel.Application.
    If I use objExcel.Quit, then the current Workbook closes. (I want it open). However, I did try that, and used FollowHyperlink to open it again. A pain, because you have get users to click on the
    Regards,
    Peter

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel persists after closing (XP)

    I'm confused. If you have an activeworkbook open, how can the Excel process be closed?
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel persists after closing (XP)

    When I close the Workbook, either by code or manually, there is still an invisible instance of Excel running. This totally messes up any further attempts to do any more work with Excel. Even ending the process in task manager doesn
    Regards,
    Peter

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel persists after closing (XP)

    I copied your code to one of my test databases, and adapted it only as far as needed to use existing objects. If Excel wasn't open before running the code, the instance of EXCEL.EXE disappears from Task Manager the moment I close the workbook, so your code in itself seems to be OK.

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel persists after closing (XP)

    Drats. that's exactly what I didn't want to hear.
    Thanks for your effort Hans. That is way above & beyond the call of duty.
    I
    Regards,
    Peter

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel persists after closing (XP)

    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Are you sure that you're not creating an instance of Excel somewhere else in your database? There is nothing in the code you posted that would keep Excel in memory after the user closes the workbook, and you wrote that you have used similar code in other databases without problems.

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel persists after closing (XP)

    I can
    Regards,
    Peter

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel persists after closing (XP)

    I can't EXPLAIN why Excel persists, but I think I have found which parts of the code cause it, and how to get around it.

    1. The <range>.Cut Destination:=... instructions.

    Workaround: split them into separate Cut, Select and Paste instructions.

    .Columns("A:A").Insert Shift:=xlToRight 'Insert a blank column
    .Columns("C:C").Cut
    .Columns("A:A").Select
    .ActiveSheet.Paste 'Move Progtitle col there
    .Columns("B:B").Insert Shift:=xlToRight 'Insert a blank column
    .Columns("F:F").Cut
    .Columns("B:B").Select
    .ActiveSheet.Paste 'Move Optus# col there
    .Columns("E:E").Insert Shift:=xlToRight 'Insert a blank column
    .Columns("H:H").Cut
    .Columns("E:E").Select
    .ActiveSheet.Paste 'Move Optus# col there

    2. The use of Range instead of .Range in

    ActiveSheet.PageSetup.PrintArea = Range("A1:G" & NumRecs).Address

    Solution:

    ActiveSheet.PageSetup.PrintArea = .Range("A1:G" & NumRecs).Address

    Hope this works for you.

  11. #11
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel persists after closing (XP)

    Thanks Hans. Interesting thought. I tried it but Excel still persists. Weird eh? There
    Regards,
    Peter

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel persists after closing (XP)

    I will tell you what I did. I commented out everything between With objExcel and the corresponding (final) End With. Excel didn't remain in memory then. I started to uncomment the instructions one by one and tested the code each time, until Excel remained in memory. Then, I just fiddled with the offending instruction until Excel was removed again. (If it didn't, I removed Excel manually using Task Manager, and quit and restarted Access.) I repeated this until all instructions were uncommented.

    To make sure, I just tested my "solution" again, and it still works OK, so I am afraid I can't assist you further.

  13. #13
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel persists after closing (XP)

    Wow, Hans, you sure were thorough. I used your method to eventually fix the problem. Although I still couldn
    Regards,
    Peter

Posting Permissions

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