0

I want to open an Excel file to read some values from it and then close it again. I use the following, simplified code for it.

Dim wbImportFile As Workbook
Dim sFilePath As String

sFilePath = "C:\...\Kundenstamm...xlsx"

'open the workbook read only
Set wbImportFile = Workbooks.Open(sFilePath, ReadOnly:=True)

'Read some values from the open Excel file - nothing very complicated happening here
'...........
'...........


'Close file
wbImportFile.Close False
Set wbImportFile = Nothing

However, after closing the file, it still shows up in the project explorer and each time I run the macro, one more project is added to the project explorer:

Project explorer in VBA showing the none-closed Excel workbooks

I found several similar questions on Stackoverflow, like this one, where Set Workbook = Nothing did the trick, but I am doing that one already.

The accepted answer in this question did not help me either (specifying SaveAs:=False).

I have to add that the problem only occures when the VBE is open. If I do not have the code editor open and run the code, no additional projects will appear. However, closing and opening the VBE does not remove the unwanted projects from the project explorer.

Since these projects do not appear when the VBE is closed, I am wondering if this is actually a problem? And if so, what am I doing wrong and can I fix it?

Michael Wycisk
  • 1,590
  • 10
  • 24
  • 1
    Usual bug... Not sure how Excel actually shows open workbooks in VBA application window and what causes it, but... Simply run the macros code without VBA application Window open, once finished, open it and check, - you will not see such a thing. I simply ignore it. P.S. `Set Workbook = Nothing` is a good practice anyway. – NoobVB Feb 17 '22 at 08:50
  • @RolandasKol I am considering as well, that it might be a bug, but good to hear some opinion/knowledge from other skilled people. – Michael Wycisk Feb 17 '22 at 08:54

1 Answers1

1

The original post is a year old, but I was running into the same problem with projects left open. Sometimes, it would leave them open and sometimes it wouldn't. It happens a greater percentage of the time when VBE is open. There seems to be varying reasons from what I've seen, but the fix for me is setting the object to Nothing prior to closing the workbook. If I go:

wb.Close SaveChanges:=False

Set wb = Nothing

Then project more often than not is left open. My guess was as the object still being held in memory keeps the project open. Then, releasing the object isn't closing the project consistently. Again, it doesn't happen every time which to means it's an Excel problem. If I go:

Set wb = Nothing

Workbooks(ABC).Close SaveChanges:=False

The project closes 100% of the time.

ENIAC
  • 813
  • 1
  • 8
  • 19
Lamanzo23
  • 11
  • 2