Errors detected while saving file

Question

Running Excel 2007. Receive message:

"Errors detected while saving 'C:\path and file name.xlsm."  Microsoft Excel may be able to save the file by removing or repairing some features.  To make the repairs in a new file, click Continue.  To Cancel saving the File, click Cancel."

Here's the background as best as I can remember: I have spent weeks writing VBA code for a friend's spreadsheet. Today I opened the last version of the spreadsheet and wanted to add some code to perform some simple operations before the spreadsheet is saved. I did alt/F11, double-clicked ThisWorkbook in the explorer pane, but did not see Workbook in the dropdown. In my naivete I pasted the following code into the "(General)" area:

   Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

       MsgBox "Test!"

       Cancel = True

   End Sub

Of course, when I went to save the spreadsheet with my macros, I got the msgbox and it wouldn't save (because Cancel=True).

Eventually I started over again, found Workbook in the dropdown, pasted the above code minus the "Cancel = True" line. Then the trouble started. I was able to save it once (cntl/s). But attempting to save it a second time (cntl/s) gave me the "Errors detected" message above. If I close Excel and reopen, try to get back to the Workbook macros, I get "Error in loading DLL" when I double-click on ThisWorkbook. I couldn't seem to be able to access any of my existing code under Modules either.

Despite my panic at losing all of my recent work, I copied the file to another pc, running Excel 2010. It loaded fine, I was able to save repeatedly, getting my "Test!" msgbox, with no issues. I saved my file under a new name and took it back to the Excel 2007 pc.

I rebooted the original pc, loaded the good version of the file, but the problem continues. I have a good copy of the file with no code in the Workbook section. When I go to see what's there, it adds the Workbook_Open method. I delete those lines, add an empty Workbook_BeforeSave method, close VBA, close Excel and tell it to save the file. Open it again, go to Workbook, and I consistently get "Error in loading DLL". Hitting ctrl/s from the VBA window gives me the "Errors detected..." message, and cancelling that message box gives "Document not saved".

There doesn't seem to be any way for me to add any Workbook_BeforeSave code on this pc anymore. What's gone wrong?

UPDATE: I have since found that I had a subroutine Workbook_BeforeSave remaining in Module Module1. I've removed it on another pc and saved it. However, copying that file to the Excel 2007 machine, it loads, but saving it twice still causes the "Errors detected" error.

Answer 1

Further update: Since there seemed to be internal damage to the file that Excel 2007 could not deal with, I have since performed the following steps: I made a backup copy of the spreadsheet just to be safe. Then I went to each of the forms in the spreadsheet and exported them and removed them. I did the same for the only module, Module1, except that I removed the erroneous Workbook_BeforeSave code before the export. Then I saved the spreadsheet as a macro-free .XLSX file, hopefully removing all traces of the corruption. I then opened that file and saved it as a new macro-enabled .XLSM file. Then I imported the forms and modules that I had saved, and voila... no more issues.

Apparently no one would ever be able to figure out what went wrong in Excel 2007, so I gave up and recreated the file successfully. Since the corruption seems to be ignored in Excel 2010, I am satisfied that future versions will not have this problem. I'm submitting this response with the hope that it might help another user recover their file should they hit the same issue.