Workbook listed as VBA project after closing

[Tom Urtis]

Hello all,

Does this happen to anyone, 'cause it's happened to me. Some (a relative few, but enough to bug me) of my workbooks remain listed in the VBA Project Explorer even after I close them. It's odd, because just looking at the VBA PE, if you didn't otherwise know that the file was closed, you'd think it was open. When this occurs, I can view all the sheet objects and macros in the PE just as if the file was still open. Then, if I re-open the actual file (without rebooting inbetween), the file will appear listed twice in the PE.

This has happened to me on different OS's (98, NT, etc), in Excel2000. It seems to happen with files that contain a Workbook_Open event, especially when that open procedure involves opening another Excel file in order to import data.

Am I missing something obvious, maybe with my Close event, to convince Excel that it should now remove this file project from the Project Explorer? Any advice?

Thanks!!!

Tom Urtis

 

[MrExcel]

That is bizarre. I've never seen it.

Do you mean that you can still view the macros, or just the list of modules?

If you can still see the code in the macros, - what would happen if you change a macro and try to click on the Save icon in VBE? Do you get a horrific crash and burn? Does it appear to save? Or just nothing?

What does the code look like in your Before_Close event?

Bill

_________________
MrExcel.com Consulting
This message was edited by MrExcel on 2002-03-15 08:17

 

[Mark O'Brien]

I tried to duplicate your problem, but can't get it.

Are the worksheets visible when you open them? i.e. are you instantiating an XL object using something like:<pre>

Dim XL as object

Set XL = CreateObject("Excel.Application")
etc.</pre>

(I don't expect that code to work since I just pulled it out of my *** and only use it as an example.)

Or do you open these workbooks using something like:<pre>
Workbooks.Open "C:TempBook1.xls"</pre>


I had a similar problem to you a few years ago in Excel 97 SR-2 on Win NT4, but the problem "went away" when I upgraded to Excel 2000. (The fact that you're using 2000 makes me scratch my head some more).

_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-03-15 08:24

 

[Ivan F Moala]

Tom
These workbooks haven't by any chance been
used as a server ie. used to connect to
the internet....??

I know there can be issues when using IE


Ivan

 

[Tom Urtis]

Thanks Bill, Mark, and Ivan for the responses. Answers to your questions:

Bill:
When this happens, I can view the macro and "edit" it, but I've never had the nerve to save it in the VBE and risk a crash; frankly I've been too timid to try the experiment. What I have done, is to try running a macro from the file when I open it a second time, but (and this is really wierd) the macros do not run, nor are they listed in the Macro Name box where you'd ordinarily see them via Tools > Macro > Macros (Alt+F8). Though, the file project name is then listed twice in the VBE PE. So I close out Excel and re-open the file, but then the same problem persists. There's a follow up to this, 4 paragraphs down.

My Before_Close events seem innocuous. Most of my clients have many end users who impose splits, or pane freezes, or gridlines for their own temporary viewing preference, but which annoys their colleagues if left that way after closing, so I usually involve a restoration of the windows' original fresh look for the next user to alter as they like, with an autosave.

Mark:
This has never happened while an XL object was created as you described. Sometimes my apps have hidden sheets, sometimes not.

Ivan:
Thanks, good idea, but unfortunately in my case I was not using Excel in that capacity.

The good news is, this is a rare occurrence, having happened to me maybe half a dozen times over the years, but most recently last week on a job site. That time, the procedure was in a Workbook_Open event, and to work around the problem, I placed the code into a regular module, to be triggered by a Forms button, and deleted the Workbook_Open event altogether, as the procedure's purpose was to import data. I had no problems thereafter with the file, but it wasn't as sharp as it could've been, because the import was not truly automatic; it required a button click action from the user, which to me reduced the convenience factor for the client.

This situation is not happening to me at the moment, but I'm being a little proactive today for if/when it does, and how to avoid it.

Thanks again for your ideas; much appreciated. If I find a cause and/or solution, I'll be sure to post it in case anyone else has experienced this.

 

[kirkjt]

For me, this was caused by the "Acrobat PDFMaker Office COM Addin". Try deactivating that add-in and restarting Excel. This fixed that incredibly annoying issue for me. I was using Office 2007 or 2010.

 

[Tom Urtis]

Thank you, kirkjt, for that follow-up info...noted.

 

[aliibrahem]

Hello Everyone,

I hope a fix has been found for this problem...

this happens to me after inserting an excel object using this code, which is triggered by selectionchange:

ActiveSheet.OLEObjects.Add(Filename:= _
        FileToShow, Link:= _
        False, DisplayAsIcon:=False).Name = "PreView"

then using this code to close the workbook

Workbooks(VBA.FileSystem.Dir(FileToShow)).Close savechanges:=False

but the VBAProject(Book13) remains open "13 is incremented number"

I tried to open the workbook again and close using the following code but nothing happens

Workbooks.Open (FileToShow)
       Workbooks(VBA.FileSystem.Dir(FileToShow)).Close savechanges:=False

in the immediate window, I managed to close it using

workbooks("book18").Close savechanges:=false

all I want to preview and excel or word file inside a workbook.

is there a fix for this problem? How to automate closing workbooks(book & some number)

any help is appreciated..
Thanks

Ali