11

I have created a macro that opens a excel workbook and performs some operations and then at the end it closes the file.The macro is running fine.

Now, when I open my VBA Project explorer in the vba editor. It shows the list of files that have been opened by the macro even though they are closed. How do i remove them from the project explorer window ? why its still showing the excel workbooks, which are already closed ? is there anyway to fix it or its a limitation?

niko
  • 9,285
  • 27
  • 84
  • 131

3 Answers3

17

I found the answer myself.

Dim Excel_workbook As Excel.Workbook
Set Excel_workbook = Workbooks.Open("somefile name");
' some code goes here 
' at the end write the below statement
Set Excel_workbook = Nothing 'worked, I found at the 12 page of google search

This is the link that helped me http://www.mrexcel.com/forum/showthread.php?t=50086

ZygD
  • 22,092
  • 39
  • 79
  • 102
niko
  • 9,285
  • 27
  • 84
  • 131
  • 4
    Self answered questions are fine. In fact you should also accept your own answer – chris neilsen Oct 11 '11 at 07:57
  • 1
    I know this was from ages ago but felt the need to add something. I believe this was the cause of the Automation Errors I got when opening the VBA editor as it hadn't cleared the wb variable properly even after .close. Found it better to do Workbooks(name).Close and then Set wb = Nothing. – Joss Jul 05 '17 at 11:05
  • I am having exact similar issue. But I tried the above methods, but still workbooks appears in the project window. Any other possible resolution for this? – Anu Aug 11 '18 at 07:02
  • The following link got picture too [link](https://stackoverflow.com/questions/51740482/why-files-still-appears-in-project-window-even-after-closed-that-excel-file) . I hope this is the same issue – Anu Aug 11 '18 at 07:04
2

Further intel. I had the same problem and was using Google Drive (previously Google desktop). I found I could resolve the problem while still using Google Drive by not only ensuring set wb = nothing, but also checking that Application.EnableEvents = True when the files were programmatically closed.

Hope this helps someone - as the above helped me

RussellJM95
  • 21
  • 2
  • I've posted a similar problem (my case happen to all xlsm file, even the one with one module, one sub contains only `msgbox ""`). When I read your post mention Google Drive, I copy one of xlsm file (say, test.xlsm) from D drive to K drive which is Google Drive drive. Then I open test.xlsm, the one in K drive. Close the file, open VBA editor, there's no test.xlsm seen in the VBA editor. But if I open the test.xlsm which is in D drive, close the file, open VBA editor, the test.xlsm is still seen in the VBA editor. Can it be that Google Drive (installed in my computer) causing this problem ? – karma May 01 '22 at 01:37
1

I know this is an old topic but I had the same thing happening but it wasn't because of the same issue.

In my case the problem was the Microsoft Data Streamer for Excel add-in - once I deactivated it the projects disappeared from VBA when it's workbook was closed.

jbmb2000
  • 61
  • 9
  • These Add-Ins are a bit hidden. You find them in Excel via 1) Menu _File_ 2) _Options_ -> _Add-Ins_ 3) In the _Manage_ drop-down box, select _COM Add-Ins_ and click the _Go_ button 4) Un-check the corresponding box or remove the Add-In (It might be necessary that you started Excel as Admin) 5) Click _OK_ 6) Restart Excel – Traveler Feb 19 '22 at 18:25