'Opening a file throws error: "Cannot find project or library"

I have an Excel file that uses VBA to calculate active ranges for a few sheets to make a .csv file to be picked up by a reporting server.

Every once in a while, opening the file throws a "Cannot find project or library" as an exception. Clicking anything after that causes Excel to crash.

Similar questions have been asked. I cannot use any of the solutions.

"Cant find project or library" : This solution asks me to find VBA references and uncheck the one that is missing.

Fails because I cannot click anything after the message appears. So I cannot go to references.

"Disable Com Add-Ins": This did no changes.

"Use office configuration analyser": This gives me no error messages

Reinstalled Office

Use on another computer: This works for a bit but after a few uses, exhibits the same behaviour.

More Background: I inherited this file from a user who was frustrated with the frequent crashing.

  • This file was saved on the network drive and has been alternately accessed by both 32 and 64 bit versions of Excel.
  • I have attached the screenshot for the references used by an older version of this book
  • The only thing that works is if I explore the workbook as a .zip file and replace the vbaProject.bin file from an older version. I can now script this process but I lose all work after the last save
  • If you look at the event viewer logs as this application crashes, it says that the faulting module is VBA7.DLL

EDIT:

Currently Trying this solution

Version: Office 2013 Professional Plus with Service Pack One. All updates installed as of 5/1/2018

References in Project

These are the references installed

Event Viewer Logs

Event Viewer Logs



Solution 1:[1]

Let me expand on Mathieu Guindon's comment.

You mentioned that you were able to get the workbook to work for a bit by using on another computer. Do that again, but this time copy all of the contents from that workbook into a new fresh workbook. I recommend doing copy and paste values so you get just the content and none of the formatting. Do not right click on the worksheet and copy the whole worksheet. That will copy the formatting with it.

The intent is to keep the content but leave behind the corrupted portion of the Excel file. Because you can only get the Excel file working for a brief period, it sounds like identifying and debugging are impossible. It's time to ditch that Excel file and start with a new Excel file. But I bet you want your data, which is why I recommend that you use copy and paste values to retain your data but get a fresh new housing.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 irockyoursocks