Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Not Closing (2003)

    Hi,
    I have a database that exports some data to an Excel file. The export works good, but my code leaves Excel open. When I go to the Windows Task Manager, I'll have several instances of Excel.exe open. I've tried several things, making sure whenever I've opened the workbook I close it, but I'm missing something. The code is attached.
    Thanks,
    Deb

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

    Re: Excel Not Closing (2003)

    You have commented out xlApp.Quit in PopulatePartsByVendor, so each time this procedure is called, it leaves an instance of Excel running.

    BTW, PopulatePartsByVendor starts an instance of Excel, but it is called from cmdExportExcelQuoteByVendor which in turn is called from cmdExportExcelQuote_Click, and this procedure also starts an instance of Excel. Moreover, they seem to open the same workbook. Is that your intention?

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Not Closing (2003)

    When I uncomment that line (xlApp.Quit), it will close the instances of Excel, but then I get the message if the user wants to save changes to Quote.xls, which I don't want them to get, I don't want them to change that file at all.

    As to your second question. I don't think that's my intention, but I had help with this, and i'm quite the novice, so If you see a cleaner way....I'd be greatful.
    Thanks
    Deb

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Not Closing (2003)

    Close the file before you quit excel.

    xlapp.Workbooks(theTemplateFile).Close SaveChanges:=False
    xlApp.Quit
    Francois

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

    Re: Excel Not Closing (2003)

    You could pass the Excel application object and the worksheet object to cmdExportExcelQuoteByVendor and from there to PopulatePartsByVendor, just like you pass theWorksheet to PopulateParts.

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Not Closing (2003)

    Francois,
    When I add that line in, I get subscript out of range message.

    This is what my code looks like at the end.
    xlApp.activeworkbook.Close False
    xlapp.Workbooks(theTemplateFile).Close SaveChanges:=False
    xlApp.Quit
    Set mySheet = Nothing
    Set xlApp = Nothing

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

    Re: Excel Not Closing (2003)

    The two lines

    xlApp.activeworkbook.Close False
    xlapp.Workbooks(theTemplateFile).Close SaveChanges:=False

    are probably trying to close the same workbook. You must use either the first or the last one, not both.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Not Closing (2003)

    And do I do this in both areas?

  9. #9
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Not Closing (2003)

    I changed my code to the following and I still the subscript out of range error message. When I click debug it highlights the first line under PopulatePartsByVendor.

    xlApp.Workbooks(theTemplateFile).Close SaveChanges:=False
    xlApp.Quit
    Set mySheet = Nothing
    Set xlApp = Nothing

Posting Permissions

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