VBA project explorer still showing the excel workbooks which are closed by the macro

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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