Morning Peter, happy Monday!

Cheers!

Well I'm fresh out of ideas, again!

Perhaps I might recap what we have so far in case anyone else ever wants to
delve in further -

It seems phantoms manifest in different degrees of ghostliness, mine the
least so and yours the most.

1. (mine) Exists in the VBProjects collection but not in the Workbooks
collection. However it is a Workbook and can be closed, either by
Workbooks("myPhantom.xls").close, or
ThisWorkbook.close in the immediate window.
I've never been able to directly attribute my phantoms to some other Com or
dll type addin or third party app (I recall similar in XL97 with no such
apps) and never noticed them lead to other problems (Dave Peterson commented
similarly re no apparent problems).

2. (Jon Peltier's) He reported no errors with the tests in the immediate
window which suggests it also exist as a Workbook, albeit not in the
collection. However he could not close which suggests something preventing
that, some sort of circular object reference perhaps. He clearly attributed
the cause to a particular third party app and also said the existence of a
phantom can lead to other problems.

3. (yours) All three tests in the Immediate window failed which suggest it
does not exist at all as a workbook. It does exist in the VBProjects
collection (subject your earlier report was not referring to an innocuous
unsaved wb whose project name is also VBAProject). Leads to serious memory
leak.

Regards,
Peter T

"Gizmo63" wrote in message
...
Morning Peter, happy Monday!

In the project window the naming of phantoms is indestinguisable from a
real
file:
e.g. "VBAProject (Consolidated WSSI 2007.xls)"
All the coding is also visible and editable.

?thisworkbook.fullname returns 'Runtime Error 429': ActiveXcomponent can't
create object
?Workbooks("myPhantomWorkbookName.anyExt").fullnam e returns a 'Runtime
error
9': Subscript out of range
"thisworkbook.close" returns a 'Compile Error'

It's also retaining a memory allocation for these phantoms; I have a
consolidation that opens a load of individual workbooks, copies info and
then
closes them again.
Each of these sticks around as a phantom and at the end of the process I
am
getting 'out of memory' messages.

Make any sense to you?

Giz


"Peter T" wrote:

Your observations are consistent with what you would see when looking at
"vbp" that's been set as a reference to the project of an unsaved file,
and
one whose project name has not been changed from the default
"VBAProject".
Doesn't confirm either way but could it perhaps be referring to the
unsaved
wb in which you are running the test macro.

If you look at the list of files on the left they'll be listed like
this -
ProjectName(WorkbookName)
How is you phantom listed ?

In the Immediate window (Ctrl-g) can you do any of these:
- first select your phantom in project explorer
- type without quotes "?thisworkbook.fullname" and Enter
- type ?Workbooks("myPhantomWorkbookName.anyExt").fullnam e and Enter
- type without quotes "thisworkbook.close" and Enter

Regards,
Peter T

"Gizmo63" wrote in message
...
Possibly not being too clear here.
No not an actual reference to the phantom.
It correctly identifies the correct number of phantoms in that the
name
shown in the debug.print is simply " - VBA Project" and not something
like
"mybook.xls".

Looking in the locals; whenever it encounters a phantom it shows the
vbp
Filename as <Path not found
BuildFileName becomes "VBAProject.DLL"
wb value becomes 'Nothing'

This is my first foray into this area (Locals) so I'm not sure what
should
be there but I'm looking for difference between valid and phantom
books.

Any clue to what you may expect me to find?

Giz

"Peter T" wrote:

So you are saying you managed to set wb as a reference to the
phantom
workbook (an xls or addin?). Can you see anything in Locals that's
different
to what you might expect, look both at wb and vbp.

Regards,
Peter T

"Gizmo63" wrote in message
...
Hi Peter,
Both methods identify the existance of the phantoms but wb.close
does
not
have any effect.

Cheers

Giz

"Peter T" wrote:

Personally I could never attribute the problem to some other
addin,
though
in discussion you referred to Jon Peltier seemed to suspect
otherwise.

The ClosePhantoms routine was designed only to close a phantom
"xls"
that
had a path (ie previously saved) and assuming its fullname could
be
obtained
and a reference set to it as a workbook.

If your phantoms are not as described above, ie previously saved
addins,
try
the following and see if you can at least obtain a handle to
your
phantom(s).

Sub Test()
Dim s$
Dim idx As Long, i As Long
Dim wb As Workbook
Dim vbp As Object

'' try both types of Loop, ie 'For Each' & 'For i = 1'
'' (comment 'Set vbp =' if looping 'For Each' )

'For Each vbp In Application.VBE.vbprojects
For i = 1 To Application.VBE.vbprojects.Count

idx = idx + 1
s = " - ": Set wb = Nothing: Err.Clear

On Error GoTo errH
Set vbp = Application.VBE.vbprojects(i) 'comment if For
Each

On Error Resume Next

s = vbp.Filename
If s = " - " Then
' could be a non-phantom unsaved wb, eg "Book1"
' but not possible get the wb.name from vbp
' to set a wb reference
Err.Clear
s = s & vbp.Name

Else

s = Mid$(s, InStrRev(s, "\") + 1, 200)

Set wb = Workbooks(s)

End If
resNext:
Debug.Print idx, Err.Number, Not wb Is Nothing, s

Next

Exit Sub
errH:
Resume resNext

End Sub


If in this test you can set a reference to your phantom, put a
break
on
the
debug line and when you get to your phantom try -
wb.Close

If (?) that works it should be possible to adapt to distinguish
normally
loaded files and attempt to delete only the phantom(s).

Regards,
Peter T


"Gizmo63" wrote in message
...
Hi all,

These well discussed phantom projects that remain even when
the
workbooks
are closed are a real pain.
Concensus seems to point to add-ins or VSTO items but here's
the
rub:
Corporately we all have the same build but no others have the
same
problem
so that's VSTO out of my equation.

Of the other folk with this problem are any of you using
Microstrategy
Office, Microstrategy Desktop or FastExcel. Office wise the
only
thing
I
have
that no one else does is MSAccess.

These are the unique aspects to my pc.

FastExcel has been removed and the pc rebooted with no
effect -
could
something be left lurking?
Microstrategy is a pain to remove and reinstall so I'd like to
leave
this
as
a last option.

Peter T's subroutine to kill of the phantoms has no effect
either.

Need some help - these ghosts are getting to me!