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?

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
ups.com...
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?

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" wrote in message
...
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
ups.com...
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?

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
ups.com...
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?

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.

Set wb = Workbooks(s)

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" wrote in message
...
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
ups.com...
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?

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.

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.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
oups.com...
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,

Thank you _very_ much for item 7, uninstalling Bissantz SparkMaker.

I've been having this problem ever since updating to Excel2003, an
uninstalling SparkMaker solved it. Never would have found it on m
own.

Phil Brosna

--
pbrosna
-----------------------------------------------------------------------
pbrosnan's Profile: http://www.officehelp.in/member.php?userid=536
View this thread: http://www.officehelp.in/showthread.php?t=125777

Posted from - http://www.officehelp.i

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

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