TOOLS

Three solutions to try when you cannot save an Excel file

The following message appeared, and I could not save my Excel file, which I had been able to save without any problems.

Error Message:

Errors were detected while saving [path\Filename]. 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.

I clicked the [Continue] button and tried to save the file under a different name, but I got the same message and could not save it. After trying various ways, I found some of ways to save the file. Here are three solutions to try when you cannot save an Excel file.

1

Method 1: Move the sheets to a new book

By moving the original sheets to a new book, I could save the file. For an Excel book (*.xlsx), do Step 1. If the sheet has data validation, also do Step 2. For a macro-enabled book (*.xlsm), also do Step 3.

Step1. Copy the sheets to a new book

① Right-click on a sheet, and then click on [Select All Sheets]. ※Note

② Right-click on the grouped sheets, and then click on [Move or Copy].

③ Select [(new book)] for To book, check [Create a Copy], and then click the [OK] button.

④ A copy of the selected sheets will be created in the new book.

⑤ Click on [File]-[Save As] to save the file.

Note: Hidden sheets

In the above Step1-① [Select All Sheets], the hidden sheets will not be selected. Please follow the steps below to display the sheets.

① Right-click on a sheet and select [Unhide] from the list.

② Select the sheet you want to display from the [Unhide] dialog box, and click the [OK] button.

If the book is protected

If the book is protected, you will not be able to move or copy sheets to a new book. Please follow the steps below to unprotect the book.

① Click on [Review]-[Protect Workbook].

② Enter a password in the [Unprotect Workbook] dialog box and click the [OK] button.

③ The book will be unprotected.

To show sheet tabs

If the sheet tabs are not displayed, follow the steps below to show them.

① Click [File], and then click [Options] at the bottom left of the screen.

② Click [Advanced] in the [Excel Options] dialog box.

③ Check [Show sheet tabs] and click the [OK] button.

Step2. Data Validation

When you copy or move the sheets to a new book, the data validation will be cleared. If the sheets have data validations, you need to set them on the new book.

Step3. Excel Macro-Enabled Workbook

For an Excel Macro-Enabled Workbook, export the modules of the original book and import them into the new book. The steps are as follows.

Export the modules from the original book

① Open the original book.

② Press [Alt]+[F11] key to start VBE (Visual Basic Editor).

③ Select a module to export in the project explorer.

④ Click on [File]-[Export File].

⑤ Select a folder to save the file, enter a file name, and click the [Save] button.

Do steps ③ through ⑤ for all modules. Close the original book after the work is completed.

Import the modules into the new book

⑥ Open the new book.

⑦ Press [Alt]+[F11] key to start VBE (Visual Basic Editor).

⑧ Click on [File]-[Import File].

⑨ Select the relevant folder, and select the module file to import, and click the [Open] button.

⑩ The module will be imported in the project explorer.

Do steps ⑧ through ⑨ for all modules.

Set Available References on the new book

⑪ Click on [Tools]-[References].

⑫ Check the library files that can be referenced in the same way as the original book, and click the [OK] button.

Enable Macro on the new book

⑬ Click on [File]-[Info], and then click [Edit Links to Files].

⑭ Select the original file and click the [Change Source] button.

⑮ Select the new book and click the [OK] button.

⑯ Click the [Close] button on the [Edit Links] dialog box.

⑰ Save the new book and close it.

2

Method 2: Open the file on an another PC or OS

I tried to see if I could save the file on another PC, Window7 or Mac PC. When I opened the file on another Windows 10 PC and tried to save it, I was able to save the file without any problem. When I tried it on a Windows 7 and Mac PC, I was also able to save the file without any error.

I saved the file on another Windows 10 PC, Window7 or Mac PC, and then opened the file on the computer where the error occurred and see if I could save the file, and it worked out without any problem.

3

Method 3: Open VBE (Visual Basic Editor)

In an Excel file that contains macros, I tried the following.
Open the Excel file and start the VBE (Visual Basic Editor). After opening the [References – VBAProject] dialog box and changing the settings for the library file and see if I could save the file. No error occurred and it worked fine. The steps are as follows.

① Open the file.

② Press [Alt]+[F11] key to start VBE (Visual Basic Editor).

③ Click on [+] button of VBAProject.

④ Enter a password to open the VBAProject and click the [OK] button.

⑤ Click on [Tools]-[References].

⑥ Click any of the reference to place a check, and then click the [OK] button.

⑦ Close VBE.
To close VBE, click the close button in the upper right corner of the screen, or click on [File]-[Close and Return to Microsoft Excel].

⑧ Got back to the Excel window and tried to save the file, and it saved successfully.

Finally, start VBE, open the [References – VBAProject] dialog box, and uncheck the library file that you checked in step⑥. After that, save the Excel file.

4

Useful when an error occurs

An error may suddenly occur in an Excel file that has been working fine until now. For example, the following automation errors may occur and stop the operation.

Error Message:

Run-time error ‘-2147319767 (80028029)’:
Automation error
Invalid forward reference, or reference to uncompiled type.

The error occurs in the following code.
ThisWorkbook.Sheets(“Sheet1”).Select

I tried to see if the above three solutions would work even when such an error occurred, and found them to be effective.

5

Microsoft’s reference page

Microsoft Troubleshooting has articles about not being able to save a workbook. Please refer to the links below as well.

Troubleshoot why Excel does not save changes

https://docs.microsoft.com/en-us/office/troubleshoot/excel/issue-when-save-excel-workbooks?tabs=third-party

Repairing a corrupted workbook

https://support.microsoft.com/en-us/office/repairing-a-corrupted-workbook-7abfc44d-e9bf-4896-8899-bd10ef4d61ab?ui=en-US&rs=en-US&ad=US

You cannot save a workbook that contains an image that was copied from another workbook in Excel 2010

https://support.microsoft.com/ja-jp/help/2597034/you-cannot-save-a-workbook-that-contains-an-image-that-was-copied-from

Email Marketing & Communications with WoEmail toolPrev

Kakeibo allows you to set up expense items that fit your lifeNext

ARCHIVES

  1. TOOLS

    How to fix “######” in cells…
  2. MS WORD

    How to format a date to uppercase month …
  3. MS WORD

    [ Mail Merge in Microsoft Word ] Print o…
  4. TOOLS

    How to find out if you have a 32-bit or …
  5. TOOLS

    Display monthly and annual income and ex…
PAGE TOP