Workbooks still appear in Project Explorer after closing in Excel?

[robotman]

I'm having a weird problem when my workbooks still show up in the VBA
Project Explorer even after I've closed them in Excel.

Sometimes if I open the same workbook multiple times in a session, I'll
see it listed several times with the same name in the Project Explorer.
From the Project Explorer, I still can access the VB code and forms
even though the workbook itself is closed and not in the main Excel
interface.

The workbooks are not hidden. And if I close and reopen Excel they
disappear form the Project Explorer.

Anyone have any ideas what is going on?

 

[Jon Peltier]

I experience that once in a while, but today it seemed that every workbook I
closed stuck around in the VBE. I ended up quitting Excel several times
during the day, whenever I had a dozen or so strays.

I don't know how to avoid these ghost projects or how to get rid of them, so
I just ignore them.

- Jon

 

[Peter T]

Hi Jon,

Similar for me occasionally but frequently, if not always, after accessing
Xlusrgal.xls or XL8Gallery.xls

I've found that this works -
set wb = workbooks("phantom.xls")
and if it's window.count = 0 close it
But it doesn't exist in the workbooks collection so simply looping that
doesn't help.

This seems to work for me though, at least with the above mentioned files.

Sub ClosePhantoms()
Dim s$
Dim wb As Workbook
Dim vbp As Object

For Each vbp In Application.VBE.vbprojects
On Error GoTo errH
s = vbp.Filename
If Len(s) Then
s = Mid(s, InStrRev(s, "\") + 1, 200)

Set wb = Workbooks(s)

If wb.Windows.Count = 0 And _
LCase(Right(s, 4)) = ".xls" Then
Debug.Print s
wb.Close
End If
End If
resNext:
Next
On Error GoTo 0

Exit Sub
errH:
Resume resNext
End Sub

Regards,
Peter T

 

[Jon Peltier]

Peter -

Thanks. This seems like it would work, but I have two issues.

LCase(Right(s, 4)) = ".xls" Then

Many of these are add-ins under development, so the extension is ".xla".
It's difficult to separate them from normally installed add-ins.

Set wb = Workbooks(s)

This usually gives a Subscript Out of Range error.

There are other instabilities associated with the phantom VB projects. I
think I am safer just restarting Excel every so often.

- Jon

 

[Peter T]

Hi Jon,

I only test for previously saved ".xls" because in the next check if the
book is an addin it would, ie should, have a window count of only 0 which
therefore doesn't prove it's a phantom, most likely normal.

This usually gives a Subscript Out of Range error.

Perhaps 's' was not correctly passed from fullname. But if it's correct,
with the project selected can you do this in the immediate window (ie with a
'phantom' file)

?thisworkbook.name
and go on to set a ref using the debugged string

There are other instabilities associated with the phantom VB projects. I
think I am safer just restarting Excel every so often.

You are probably right. But to date haven't noticed problems running that
code, many times with the particular files I mentioned though only a handful
with other files.

Regards,
Peter T

 

[Jon Peltier]

Hey Peter -

Perhaps 's' was not correctly passed from fullname. But if it's correct,
with the project selected can you do this in the immediate window (ie with
a
'phantom' file)

?thisworkbook.name
and go on to set a ref using the debugged string

This sometimes works, but more often not.

Also, if I type the name of the workbook exactly as it appears:

Workbooks("MyPhantomBook.xls").Close

sometimes it closes but more often I get the subscript out of error message.

For both of these cases, it seems the longer I've gone without a restart of
Excel, the less chance the line of code will execute. Sometimes, Excel even
crashes, and if I let it recover and restart, it becomes unstable sooner.

You are probably right. But to date haven't noticed problems running that
code, many times with the particular files I mentioned though only a
handful
with other files.

Well, I may just have a more screwed up system than you <g>. Including but
not limited to the Office 2007 beta.

Thanks for your suggestions,
- Jon

 

[NickHK]

I seem to remember this can be caused by a few "unrelated" situations.
For some reason "Google Desktop" :
http://www.dicks-blog.com/archives/2004/12/11/google-desktop/

NickHK

 

[Peter T]

Wouldn't surprise me at all, though I don't have it. The problem only rarely
manifests for me other than with the system chart files which almost always
ghost themselves.

Dread to think what's going on in Jon's system though <g>

Regards,
Peter T

 

[Jon Peltier]

I followed up on a number of things.

1. The phantom projects appear for deleted Excel workbooks, but not for
deleted Word documents nor for deleted PowerPoint presentations.

2. It happens with any workbooks (brand new Book1, Book2), not just with old
corrupt ones.

3. I created a new user account in Windows, and the problem persists for the
new user.

4. Repair and Reinstall from the Control Panel's Add/Remove Programs did not
help with the phantom VB projects, though it cleared up a problem with VBA's
Chart.Export for GIF and PNG files, which had been caused by Office 2007
overwriting the graphics filters files.

5. The Google thing was not a problem; I changed the LoadBehavior of another
add-in, which had no effect.

6. I removed all add-ins (Excel and VBE) and all XLSTART files, cleaned TEMP
and the Recycle Bin. No effect.

- Jon

 

[Jon Peltier]

7. Uninstalling the Bissantz SparkMaker utility from the computer has solved
the problem with phantom VB projects. Too bad, because that was a promising
tool.

- Jon

 

[Peter T]

I wonder if it's specifically your Bissantz SparkMaker utility or something
it has in common other things that also give rise to the same effect. Only
mention because I don't have that particular utility yet still get those
phantoms from time to time.

Regards,
Peter T

 

[Jon Peltier]

Peter -

I don't know. I did notice that only Excel was affected, so after removing
all the add-ins, I was mucking about in the registry, and noticed there was
still a key for the SparkMaker add-in. It uses VSTO, so you might look for
VSTO-based add-ins as your culprit.

- Jon

 

[Peter T]

Hi Jon,

For me this has always been a more a minor irritation than a major problem.
But you've got me curious now so I'm going to have to check it out!

I have a few well known VBE addins that may well originate from VSTO.
Unchecking then from Load on startup hasn't changed anything but reluctant
to fully uninstall them.

Just had another look in XL97 and also get phantoms, (the two chart files
after applying a custom chart format). This doesn't have any com addins of
course and from memory same occurred when this was the only XL version I
had.

The fact that uninstalling your utility has made a radical difference would
seem indicative of something.

Regards,
Peter T

 

[Jon Peltier]

In the past I recall seeing the chart gallery workbooks in the project
explorer. In the meantime I've had problems with the user gallery, error
messages something like "file already exists with that name...", so I don't
use the gallery any more. I find that a quick VBA procedure is better suited
to the kind of chart types I'd otherwise use the gallery for, and for the
lifetime of the need for any particular chart.

I've put MZ Tools, Code Indenter, and VBE Tools back into service with no
ill effects; this is very fortunate because I use them all the time. I also
heard from the Bissantz people, and they wonder what they might be tying
into which is causing the problem. To help them out, I plan to install their
utility on another computer. I'm not convinced it's their utility, it could
be any number of other things.

- Jon

 

[Peter T]

Hi Jon,

In the past I recall seeing the chart gallery workbooks in the project
explorer. In the meantime I've had problems with the user gallery, error
messages something like "file already exists with that name...", so I don't
use the gallery any more. I find that a quick VBA procedure is better suited
to the kind of chart types I'd otherwise use the gallery for, and for the
lifetime of the need for any particular chart.

I recall another discussion about this, for my puposes the user gallery is
very handy and avoids writing a lot of code to replicate what it serves very
well.

I've put MZ Tools, Code Indenter, and VBE Tools back into service with no
ill effects; this is very fortunate because I use them all the time. I also
heard from the Bissantz people, and they wonder what they might be tying
into which is causing the problem. To help them out, I plan to install their
utility on another computer. I'm not convinced it's their utility, it could
be any number of other things.

The very utilities I was reluctant to uninstall!

I'm fresh out of ideas as to the cause of all this. When the would be
phantom is closed it's removed from Excel's Workbooks collection but not
from the VBE.VBprojects collection, some internal reference not released
perhaps.

- Jon
-------

Regards,
Peter T

 

[robotman]

Wow... glad to know that I'm not the only one with this problem. For
me the phantom file problem has seemed to gotten worse lately, but I
can't think of anything I've changed to cause this. Restarting Excel
clears the problem, but it is irritating when I'm opening and closing a
lot of the same file during macro testing.

Do you guys use ASAP Utilities? This is the only add-in on my system I
can think that would be doing something funky to the VBE.

Thanks for all your ideas. Hopefully someone will get to the bottom of
this!

Do you see the problem in Excel 2007 VBE?

John

 

[Jon Peltier]

When I uninstalled ASAP, the problem remained. After eliminating the problem
(by uninstalling another add-in), I reinstalled ASAP, and the problem stayed
fixed. So ASAP should be safe.

I didn't see the problem with 2007, but I didn't try the offending add-in in
that version of Excel.

- Jon

 

[robotman]

There must be something else that is causing the problem for me because
I don't use the SparkMaker add-in.

Would you guess the phantoming be add-in related?

John

 

[robotman]

Hmmm.... the problem is something else on my system because I don't use
the SparkMarker. I never really noticed the problem until the last
couple months, so I would imagine it has something to do with the
system rather than a bug in Excel.

What does SparkMarker do? Any idea what type of programs might be
possilble causes of the phantoming?

Still puzzled....

John