MSEXCEL 2016 VBE : Can't find project or Library error

[ambly]

At my Workplace which is a Home Office, running PC:
Windows 10 Pro
MS Office 2016, released via Office 365

Problem with: A specific MS Excel File (xlsm), that has been working perfectly fine since 2013. MS Office 365, has been on the machine for past 2 years.

The Wb (lets say MyWB) is behaving as a database. Within the Project it has Forms, Modules, code that controls how it opens, closes, activates, presents and so on. It has 33300 records and counting. The file size is about 6.5MB. Aside from the recurring issue below, it operates smoothly.

Issue: Since the start of this year, MyWB has 'crashed' 4 times - in as much as it wont load the Project (msg: "Can't find project or library".

If I persist, the dialog : 'Visual Studio Just-in-time Debugger' pops up, stating "an unhandled win32 exception occurred in EXCEL.EXE (2960)" with The Possible Debuggers: New Instance of Mirosoft Office Studio 2010"

I have many other xlsm workbooks, which open fine. It's just this one.

Twice I have 'rebuilt' the broken File - by exporting each 'form''module' 'this workbook' out of a back up copy of MyWB- then creating a new workbook, moving the up-to-date worksheets from MyWB, then importing the project items ( 'form''module' 'this workbook') back in.

Then off it goes, fine until the next time it happens.

The last time it happened, I began to wonder if it was some update that is causing the problems. Or if the something that out ESET security app was doing. So rather than 'rebuild' I decided I would take the misbehaving MyWB home to my own PC (I emailed it to myself). At home, where I also run Office 365, it opened perfectly fine. So I saved it on myPC, then emailed it back to work. When I opened it at work, saved it back onto my Work PC, it opened fine, no problems - this was maybe 2 weeks ago - and it's been okay until today.

I see by google searching the "Can't find Project or Library" error, that a lot of the solutions seem to involve opening up the file, going into VBE, > Tools> References Dialog where I look for and uncheck specific "MISSING" items.

However, when I try do this, I cannot even get the Reference Dialog to open up at all. It's not greyed out, but it will not fire up. Same if I open the file in SAFE mode.

Help! Sorry for long msg, but I'm trying to provide as much info as I think you might need to help me.

Thank you for all help offered.

Cheers
Annie

 

[ranman256]

then something has happend to your Excel.
The problem is in Tools, References....but if you cannot even get to it, then Excel is damaged.
FIRST,
close all excels,
open excel, and a blank spreadsheet,
alt-F11, to get to VBE, and then tools, references.
Now you should see the refs. IF NOT, then you need to re-install Excel. References should be accessable.
if you CAN get in Refs, check for any MISSING checked items, and uncheck them.

 

[ambly]

Hi Ranman256,

Thanks for your reply! I did already try that - ie I close all other excel files & open up a new excel file and yes, then I can get into Tools>References Dialog. There are No MISSING items listed at all.
All that is ticked is:
* Visual Basic for Applications
* Microsoft Office 16.0 Object Library
* OLE Automation
* Microsoft Office 16.0 Object Library (again)

(Is that the right amount of ticked files? Why would the MS Office 16 Object Library be there twice?)

So, I can have 1) the 'problem' excel file open, and also 2) any other excel file open at same time (either an existing file (xlsm or otherwise ) or a new blank unsaved wb open).

All the files in 2) will work fine, macros work, they open and close fine, they save properly, I can get into VBE if it's xlsm, and I can bring up the Tools>References Dialog.
And If I close any of the 2) files they do so, properly.

BUT, if I attempt close the problem file, it fails to save then shuts down excel app entirely including any other file in 2) that might still be open - then does the whole try to recover workbooks thing.

Yesterday, I tried the problem file 'taking it home to my own PC" again. This time, that didn't work. The same error occurred at home. My Home Excel is fine, it's not broken.
So it must something within the file itself? Something that should be there is not loading when the file is is opening up and caused inaccessibility to the the VBE Project for that specific file.

Today, just now- it's done it again - to a much more important file, to which it has never happened before .
When I opened the file, none of the buttons (assigned to macros) worked. I can get into VBE and see the Project listed with all it's sheets, modules etc, but nothing will show up in the Properties pane nor in the main editing pane - and the References dialog will not open.

The Visual Studio Just-In-Tim debugger dialog cam up as a result of the shut down.
An Unhandled win32 exception occurred in EXCEL.EXE [9700]

I don't know what that means and I'm nearing the end of my range of knowledge!

Many of these newly minted problem files are ALL years old, some based annually on Templates created back in 2010.

Within those years, my work PC has been updated to where it is now Windows 10 (has been for a while).
Office 365 has been running on it for a couple of years now, Excel 2016 ( I have to say, right from the get go - I thought Excel functioned with less stability and less speed within the Office 365 set up.

Could it be an update issue with 365?
Could it be our ESET Smart Security (which has caused a ton of other problems this year with over zealous blocking of stuff and a gazillion parts all in uber tech speak)
I have no idea what to do.

Given all the above, does it still sound like a re-install of Excel is the best next thing to do?

Thanks again,
AMBLY

 

[ambly]

To Add:
I used the Quick Repair function on Office 365 intallation. No Change.
I then used the full repair function on Office 365 - which is essentially, a re-install , that what all their pop ups indicate thru the process. Again, No change.

Maybe the problem is fixed, maybe not - maybe it's just the 2 gone bad files that can't be repaired. But I can't tell.

What next? Wait and see?
Unistall and Reinstall 365, as opposed to their Repair/Install?
Windows 10 Updates?
Move out of the direct line of fire of ESET which is attached to our Network - I am actually trying this by moving one of the rebuilt files to a Local Drive instead of the Network Drive. (I have no idea if this is even a sensible thing to try)

Cheers
Annie

 

[MikeDBMan]

I have a similar problem in Excel. Did you ever figure out a solution? I am getting "UNSAVED: VBAProject" in my available references if I can even see it. And the file as soon as I try to save it or do any copying, it just shuts down excel and then tries to restart excel with this "bad" file opening up all over again. Very similar to what you describe. So please let me know if you have any suggestions. One solution I have read about is to uninstall Google Desktop Search but I can't even tell if I have it installed or not. I am using Office 365 and Windows 7. Thanks.

 

[ambly]

Hi Mike, I did find myself a solution - though the issue itself is on going! I am only on my phone at mo - i need to get to my files. Essentially, i believe in my case key dll files - a particular one possibly - are not being stored or saved in the right place or move, depending on certain configurations of open xlsm wbs. I think versions of Excel being installed over or beside each other is the main culprit. I also firmly feel excel is nowhere near as robust as it used to be pre 365. Eg: The “ excel is not responding” banner message while long or complicated code is running, infuriates me!! But I digress.....

My fix:1) . Close all open wbs. From file explorer, Copy the misbehaving wb to another location. In my case the home of the file is on a Network drive G:/ (and is a “trusted document” ie: I never need to enable macros). I then copy the file to a folder in my C:/. This causes excel to NOT trust the document in C:/

2) open the misbehaving wb in C:/. Do NOT enable macos...and immediately do as follows:

3) Via Developers tab, go into VB. Expand the Modules attached to your project . In each module in turn, go into the module. At the top, type a note preceded by the apostrophe - I write:
‘ Recompile 270310 error.

The date being the date today and error being the error msg (i have a few).

4) on the debug tab, select and click the compile option.

I do this on each module then save, then exit vb. Then close wb (save if prompted )

5) copy the wb in c:/ onto clipboard.

6) Navigate back to G:/ to where the original broken file is. Rename the broken file as eg: RC270319_mywb. Then paste in the fixed file. ItÂ’s original file name is still intact.

7) now open that fixed file; earn its trust again by enabling macros if asked. And it works again, until next time.

8) the broken file and the file you moved to c:/, can be deleted - tho I keep them in a folder just to build up a picture of when this occurs. I also never delete or over write the
‘Recompile message in the modules, for same reason.

Maybe this works for you! Let me know how you go.

 

[MikeDBMan]

I am not familiar with the recompile line. If I type exactly as shown:
‘ Recompile 270310 error.

(with and without the period after error, it shows as a red "errored" line. It returns "Expected: end of statement" if I try to run the Compiler.
If I remove everything after the word recompile it says "Invalid outside procedure".
So I am not sure how to get it to recompile. Also, one more question, once I figure out the recompile process, Do I comment out the ‘ Recompile 270310 error line from the code.

One more question, would the recompile line go above or under any global declarations at the top of the module? Or does it matter? I tried both.

I sure do appreciate the help. I feel oh-so close to "solving", at least temporarily each time.

 

[MikeDBMan]

3) Via Developers tab, go into VB. Expand the Modules attached to your project . In each module in turn, go into the module. At the top, type a note preceded by the apostrophe - I write:
‘ Recompile 270310 error.

Okay, I get it. The apostrophe is supposed to be a "real" apostrophe, not the grave symbol as you typed in above. Anyway, when do it as you suggest, behold, EVERYTHING IS SOLVED! You just saved me an hour or 2 or 3 a week plus untold agony all the time. I appreciate it so, so much. Thanks!!!!!

 

[jkpieterse]

You may benefit from two tools:

https://jkp-ads.com/download.asp#CopyVBAProject

and

http://www.appspro.com/Utilities/CodeCleaner.htm

 

[JustJane]

Hi Mike, I did find myself a solution - though the issue itself is on going! I am only on my phone at mo - i need to get to my files.

It worked! Wish Id known this a year ago! Have this problem frequently with my large excel file which usually breaks 90% way through producing loads of reports. Nice straight forward solution