Excel gives an error opening the .xlsm file edited by openpyxl

Issue #406 resolved
Andrius Senulis created an issue

Hi all,

I have a .xlsm file template (containing macros) which I load (with keep_vba=True) and populate with openpyxl (v2.1.4), then I save it as a new file. When opening the populated file in MS Excel 2013, it throws an error saying:

"We found a problem with some content in 'filename.xlsm'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes." (Options: Yes, No)

After repairing the file, Excel shows a dialog with title "Repairs to 'filename.xlsm'" and the following content: Info: "Excel was able to open the file by repairing or removing the unreadable content." Text field: "Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded." There is also a link to the log file (XML), but no extra info is given there.

However, it does not seem that the error breaks anything in the file - all the macros, formatting are not affected. The only problem is that the Excel needs to repair something in the file saved by openpyxl.

Could you please help solving this issue?

Regards, Andrius

Comments (13)

  1. CharlieC

    The error message from Excel is worse than useless unfortunately. You're best comparing the files with the OpenXML Productivity Tool.

    The problem is probably down to something else in the file not being preserved, or not being preserved quite right. This could be printer settings or something else. Without a file it's impossible to say. Excel files can contain lots of stuff, such as macros, that are not part of the specification.

  2. Andrius Senulis reporter

    Dear Charlie,

    Thank you, I will try to find out what's the problem with the tool you mentioned.

  3. Andrius Senulis reporter

    The file I use is very small and the macros are pretty simple.

    The generated file cannot be opened with the OpenXML Productivity Tool: "Cannot open the file: Required Types tag not found. Line 1, position 2."

    Anyway, I've found a problem. In [Content_Types].xml there are lines describing some kind of external link after definition of each sheet (in my case I have two sheets so this line is repeated twice in the file): <Override PartName="/xl/externalLinks/externalLink1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml"/>

    The content of that file: <s:externalLink xmlns:s="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><s:externalBook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1"><s:definedNames/></s:externalBook></s:externalLink>

    After removing the two lines (also externalLinks folder) referencing this file, the XLSM file opens without error.

  4. CharlieC

    This means that there are external links defined in the initial file but something is going wrong trying to preserve them. Support for links to other files was added in recently and is somewhat experimental (largely because the specification is incomplete and vague) and only tested with links to other workbooks. I've submitted an issue about this to ECMA and am still waiting for a response. I'd love to be able to investigate this with your file if possible.

  5. Andrius Senulis reporter

    You were right! It was apparently the problem in the my template - there were some obsolete references remaining in the workbook.xml file from the development. The error did not appear after I removed them.

    Opening the template did not cause errors, but something went wrong when openpyxl tried to preserve them. Well, it was a good discovery - now I can clean the template from trash.

    Thanks for putting me on the right track!

  6. CharlieC

    Do you want to close the ticket?

    It would still be interesting to know why the links caused problems after passing through openpyxl. Are the definedNames of the worksheets the same?

  7. Andrius Senulis reporter

    Yes the ticket can be closed. Thank you for the discussion!

    There is only one definedName in the workbook.xml file, none in the sheet1.xml and sheet2.xml.

  8. serife86

    Hi all,

    I've the similiar problem with xlsm file. I can open it with excel 2016 on Win 7 but i can't open on windows 10. I'd already checked region settings between 7 and 10. They're same. When i choose "to recover as much as we can?" the records are repaired with this message "Repaired Records: Format from /xl/styles.xml part (Styles)"

    the log is "<?xml version="1.0" encoding="UTF-8" standalone="true"?> -<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <logFileName>error020560_01.xml</logFileName> <summary>Errors were detected in file 'C:\Users\serife.yilmaz\Desktop\34 Hafta nakit planlama.xlsm'</summary> -<repairedRecords> <repairedRecord>Repaired Records: Format from /xl/styles.xml part (Styles)</repairedRecord> </repairedRecords> </recoveryLog>"

    the data format is not right in the cells after repair. For example it has to be "8.905.696" but it is "8905695.914"

    how i can fix it?

    thank you

  9. Log in to comment