locked
XLAs Build Up - How To Close RRS feed

  • Question

  • I have written an application that is opens lots of files (read only), one at a time, a long list and 'processes them', then closes them, obviously without saving any changes.

    I have the looping process of opening and closing the file written, together with my process.  However the problem I am having is that the files I am opening have references to other XLAs, and that once these are open, closing the source file leaves these open.  I see them in the VBA editor, and eventually my process fails over, excel crashes, because I run out of resources.

    I have added a procedure which after every file attempts to close all unwanted workbooks, and while this works for workbooks, it does not do anything with the XLS files which remain open.

    I have tried opening the files using Application.AutomationSecurity = msoAutomationSecurityForceDisable, this supresses the opening of the referencd XLAs, but when I do this I cannot search through the VBA code and this is part of my process.  I need the VBA code modules available.  My code works were it not for the build up of all the open XLAs.

    Does anyone know of a way to closing these XLAs so they do not build up?

    PWS

    Tuesday, January 14, 2014 6:08 PM

All replies

  • Re:  How to close Excel add-ins

    Just like any other workbook...  Workbooks("File Name.xla").Close
    -or-
    Another way...  Excel.AddIns("Analysis ToolPak").Installed = False
    (you must use the name displayed in the add-ins list, not the file name)

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, October 25, 2016 1:38 AM
    Wednesday, January 15, 2014 12:41 AM
  • But how do you find the names of the open add-ins, they are not part of the workbooks collection?
    Wednesday, January 15, 2014 6:37 AM
  • Try this

    Sub CloseUninstalledAddins() Dim addwb As AddIn For Each addwb In Application.AddIns2 If Not addwb.Installed And addwb.IsOpen Then Workbooks(addwb.Name).Close End If Next addwb End Sub



    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Wednesday, January 15, 2014 8:54 AM