CODEWITHSUNDEEP
vbaexcelproject
elbillaf
elbillaf

Reputation: 1984

excel vba projects not closing

I'm going through 100s of excel files in VBA, extracting certain data and copying it to a main spreadsheet in a main workbook. I have a VBA script that resides in this main spreadsheet.

I'm trying to get each source workbook to close after I open it and get what I need. It looks something like this:

dim main_wb
dim source_wb
set main_wb = activeworkbook

Loop thru workbook names
  set source_wb = workbooks.open(a_workbook_name)

  do some stuff
  eventually copy a few rows from various sheets into the main wb

  source_wb.close()
  set source_wb = Nothing
End Loop

The problem is that it SEEMS like the system is continuing to keep the file open in the project explorer ... and eventually it runs out of memory or something. All files work fine individually. It's only when I attempt to process them all at once that I have a problem. The workbook "closes()" but the project still exists in the project explorer in the developer window.

How do I tell it to close out a project. I need to be able to, no BS, close the project and go on to the next one for hundreds and potentially thousands of files - automatically, in code, no intervention from user.

Upvotes: 3

Views: 13139

Answers (5)

Michael Serretta
Michael Serretta

Reputation: 1

workbook.close should work fine as you are using it and I don't believe its directly related to the bug you're dealing with. (you could add a true or false to handle how you want to save but thats off topic).

The bug of projects not closing has been around for a very long time and seems to come and go. While some causes have been found, they are not comprehensive.

The following work around seems to have bypassed the bug for me.

I've found that clicking the stop button in vbe (the square button next to run, tooltip labels it "reset") actually clears the excess projects. This was weird as the code had already stopped running but it worked.

This led to my current work around, using "End" instead of relying on "end sub" to clear memory.

Sub exampleSub()

    'normal code (including calls to other subs and proceedures)

    
    End 'this ends all running code (and is not best practice as you may want to call this sub later from another macro) but
    'seems to solve duplicate vba projects.

End Sub

Upvotes: 0

John John
John John

Reputation: 55

I recently had this problem: I have a workbook that grabs data from other workbooks that I use as databases. On one of these, I inadvertently placed some code. This caused the workbook to remain visible in VBE even after it had been closed. My solution was to keep my database workbooks free of code, and that solved the problem.

Upvotes: 1

lu76fer
lu76fer

Reputation: 1

Solution : Manage your Save (Yes, No, Cancel) Destroy links to Addins in your Application Close these Addins Close your Application

Upvotes: -1

matthu
matthu

Reputation: 9

It seems that the VBE editor is not always visible to the workbook that is being closed.

I included the following code in my ThisWorkbook module which comes from a comment in another thread and this resolved matters.

http://dailydoseofexcel.com/archives/2004/12/11/google-desktop/

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
' -------------------------------------------------------------
' this code ensures that the VBA project is completely removed
' when the workbook is closed
' http://dailydoseofexcel.com/archives/2004/12/11/google-desktop/
' -------------------------------------------------------------
 If Not (Application.VBE.MainWindow.Visible) Then
 Application.VBE.MainWindow.Visible = True
 Application.VBE.MainWindow.Visible = False
 End If
 End Sub

Upvotes: 0

Mike
Mike

Reputation: 269

try... It works for me in a similar type of program.

'closes data workbook
source_wb.Close False

Upvotes: 1

Related Questions