2

Running Windows 10 Microsoft Excel 16 64 bit

Just encountered this issue - Opening a normal file with a small amount of VBA coding.

On opening file today, ran into error "Can't find project or library"

Error appeared in a few files, and after restarting was localized to a single file.

Mike
  • 133
  • 1
  • 1
  • 13

4 Answers4

12

I have found a solution to the problem which works 100% of the time, however it's slightly inconvenient.

When trying to open the VBA > References, it will crash therefore I cannot find the missing reference.

My workaround is to clear all trusted documents, which brings the 'Enable Content' pop up. Before enabling content, I will go back to VBA > References, remove and re-add OLE Automation.

The next step is to save the file, enable content and you're good to go!

Greg_dodds
  • 121
  • 1
  • 2
6

When all else failed, this worked for me. I opened my workbook in Excel online (Office 365, in the browser, which doesn't support macros anyway), saved it with a new file name (still using .xlsm file extension), and reopened in the desktop software. It worked.

Sean McCarthy
  • 4,838
  • 8
  • 39
  • 61
  • 1
    Thank you, this worked for me. Not sure what caused the corruption in the first place, but very handy tip. I did lose some active X/form control buttons but better than losing the VBA! – bawpie Jun 02 '20 at 10:55
  • 2
    This also worked for me. I've encountered this problem several times, and it's a real bear to troubleshoot because it tends to make a workbook almost unusable (crashing even in safe mode), and Microsoft's official answer of "go to References and make sure the the library is available" is useless. – Mako212 Jun 10 '20 at 20:08
2

Steps:

  1. Repair Microsoft Office
  2. remove personal.xlsb file from XLStart to test
  3. Opene blank Excel, Alt + F11 to open VBA
  4. Tools -> References -> Object Library
  5. Search for missing objects
  6. Correct any if found
  7. If the above steps didn't resolve, rename the file opened in safe mode (win + R type Excel.exe /safe)
  8. Update dates, change a few items, and save
  9. Open file normally, issue resolved.

Alternatively, try to have someone else open the file/update name and change a few things for you. The file is likely corrupt.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Mike
  • 133
  • 1
  • 1
  • 13
1

Try opening it in the Mac version of Excel. I tried this in last ditch desperation after no other recommendations worked (I couldn't try opening in online version because the file is password protected which isn't supported there). Mac Excel was able to open it without any complaints. I was then able to save it out just fine, and Windows (10, 64 bit- where the file was originally created) Excel was then able to read like normal. Using the various recovery methods with Windows Excel, the best I could get was a version that preserved the formulas but lost all of the VBA and all of the named ranges.

BTW, one other feature of my problem: I was working on the file for about a week. In that time, I never closed and reopened it. I did, however, save many, many times. Including saving versions in new files. I discovered the problem when I finally did close & reopen. And I discovered that every single version I had saved in the past five days was also corrupt.

Charlie
  • 11
  • 1