2

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.

Luuklag
  • 3,897
  • 11
  • 38
  • 57
elbillaf
  • 1,952
  • 10
  • 37
  • 73
  • A year or two ago I saw something about this problem being associated with Google Desktop Search - do you have that? May also be associated with certain COM add-ins (Acrobat?) – Tim Williams Jul 30 '13 at 18:52
  • I'd better have look at this: closed workbooks persist in the VBA Project Explorer window when the files are opened and closed manually, too - and it's all too easy to end up editing VBA in modules that *look* like the project you want to work on, and lose your work because you were actually working on a 'Ghost Project' for a file with the same name - a file that isn't actually open, but still shows up in the Project Explorer. – Nigel Heffernan Mar 06 '15 at 17:03

4 Answers4

1

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

'closes data workbook
source_wb.Close False
Mike
  • 269
  • 6
  • 10
  • 18
  • Cr@p! That seems to have worked! Thanks! Never would have occurred to me that that would make a difference! – elbillaf Jul 30 '13 at 17:54
  • Nope. It worked one time, but failed the second run...maybe that's the thing ... I have to exit out and run again every time. Weird. – elbillaf Jul 30 '13 at 18:00
  • Not working at all now. Interesting ... it worked one time and not again. So strange. – elbillaf Jul 30 '13 at 18:04
  • Working consistently now - in the sense that it doesn't fail, but all those workbooks are still listed in the project browser. – elbillaf Jul 30 '13 at 18:52
  • Weirdness. I don't know what I did, but after the process runs, the workbooks are no longer listed. That works! – elbillaf Jul 30 '13 at 18:59
  • wonderful.. i have never had trouble with this but I know sometimes excel is funky for a little bit then starts working again.. glad it is working! – Mike Jul 30 '13 at 19:02
1

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.

John John
  • 55
  • 10
0

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
matthu
  • 9
  • 1
  • The problem has resurfaced recently, and following this solution, ensuring that the VBE window is closed when the workbook is closed, helps as often as not following it. – Jon Peltier Feb 22 '20 at 18:46
-1

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

lu76fer
  • 1