PC

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 the ways to save the file. Here are three solutions to try when you cannot save the Excel file.




Language: English Japanese

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

1 Right-click on a sheet, then click on Select All Sheets. ※Note

2 Right-click on the grouped sheets, then click on Move or Copy.

3 Select [ (new book) ] for To book, check to Create a Copy, then click OK.

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

5 Click on the File tab and select Save As to save the file.

Note: Hidden sheets

In the above Step1 – 1, 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 OK.

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.

1 Click on the Review tab and select Protect Workbook.

2 Enter the password in the Unprotect Workbook dialog box and click OK.

3 The book will be unprotected.

To show sheet tabs

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

1 Click the File tab, then click Options at the bottom left of the screen.

2 Click Advanced in the Excel Options dialog box.

3 Check Show sheet tabs and click OK.

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

1 Open the original book.

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

3 Select a module to export in the project explorer.

4 Click on the File tab and select Export File.

5 Select the folder to save the file, enter a file name and click Save.

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

Import the modules into the new book

6 Open the new book.

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

8 Click on the File tab and select Import File.

9 Select the relevant folder, select the module file to import and click Open.

10 The module will be imported into the project explorer.

Do steps ⑧ through ⑨ for all modules.

Set Available References on the new book

11 Click on the Tools tab and select References.

12 Check the library files that can be referenced in the same way as the original book and click OK.

Enable Macro on the new book

13 Click on the File tab, Info, and Edit Links to Files.

14 Select the original file and click Change Source.

15 Select the new book and click OK.

16 Click Close on the Edit Links dialog box.

17 Save the new book and close it.

Method 2: Open the file on 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 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.

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.

1 Open the file.

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

3 Click on [ + ] button of VBAProject.

4 Enter the password to open the VBAProject and click OK.

5 Click on the Tools tab and select References.

6 Click any of the references to place a check, then click OK.

7 Close VBE.
To close VBE, click the close button in the upper right corner of the screen, or click on the File tab, then click Close and Return to Microsoft Excel.

8 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.

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.

Microsoft’s reference page

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

Troubleshoot why Excel does not save changes

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

Repairing a corrupted workbook

https://support.microsoft.com/ja-jp/office/%E7%A0%B4%E6%90%8D%E3%81%97%E3%81%9F%E3%83%96%E3%83%83%E3%82%AF%E3%82%92%E4%BF%AE%E5%BE%A9%E3%81%99%E3%82%8B-7abfc44d-e9bf-4896-8899-bd10ef4d61ab

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/2660122

What you select expense items is the point!

You can find the list of expense items for your household budget. It shows you samples for singles, families, and those who want to manage their money in detail and rough.

Here is for more information about it.

Recent post
  1. How to highlight today’s date in Excel

  2. How to highlight cells or rows that match a condition

  3. Three solutions to try when you cannot save an Excel file

  4. How to format date to uppercase month in the mail merge document

To display the list of articles, please create at least 4 articles in the custom post Blog.

TOP