Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

An Excel XLSX file is a zip archive containing several XML files. I tried to extract all the XML files, and edit xl\connections.xml using an XML editor. That's because I have to change 20+ connections to point to a different server.

When I open the edited archive in Excel, it refuses the changes and repairs the file.

Is there a way to edit the XML files inside an XML archive?

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted
+50

How are you going about doing the editing? I would suggest not fully extracting the file, but instead, just opening the archive in 7-zip. Then, find the file you want to edit, choose "edit", then save it and 7-zip should automatically replace it in the archive. In the past when I've done this, Excel has then opened it up without any trouble, but when actually extracting then recompressing it, I've had issues.

share|improve this answer
2  
Excel 'zip' file contents are not really compressed (deflated), they're just stored instead. –  whitequark Jun 14 '10 at 15:22
    
Ah, that would explain why recompressing as a zip using windows's built-in zipper causes problems. I'm curious now if using 7-zip and specifying the compression level as "store" would work then. –  nhinkle Jun 14 '10 at 20:13
    
@whitequark: that is not correct - the contents are indeed deflated! Excel also reads 'zip' files where the contents are only stored, but if you save the same file again in Excel it gets compressed anyway. @nhinkle: zipping with 7-zip always worked for me regardless if I chose "store" or "normal-deflate" as the compression level. –  Turismo Jun 15 '10 at 10:34
    
@Turismo: maybe I missed it with odf or such. Compressing those XML's is, really, a good idea. The Windows zipper is probably brain-damaged. –  whitequark Jun 15 '10 at 10:56
add comment

You definitely should be able to edit the XML in the archive without problems. I have done that several times with the xl\comments1.xml file and Excel opened it without any problems.

I used 7-zip and Notepad++. Perhaps your editor changes the encoding of the XML or your zipper creates a file that is not exactly compatible with Excel.

Perhaps there is a dependency on another file in the archive that has to be changed also.
You could try changing the connection within Excel and checking which files in the archive changed after saving.

share|improve this answer
add comment

If you are interested in updating the Excel XML through code, then there are existing open source projects which can help you with this, such as ExcelPackage.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.