Modify content of Excel file outside Excel and put the changed embedded file back

I want to create a macro to change the 'drawing' (actually a text box) in an Excel file. The first step is to make sure that a modified XLSX file can be opened by Excel. And here's where my problem starts.

When I change the extension from XLSX to ZIP and unzip (macOS util or The Unarchiver) or just open the ZIP file (BetterZIP), I can extract:

zip://Users/hl/Desktop/Book1 copy.xlsx.zip!/xl/drawings/drawing1.xml

I can change the XML file in BBEdit but when I rezip (or replace the XML in BetterZip), Excel can no longer open the XLSX file (after having restored the extension).

Does someone know a way to recreate the XLSX file?

Example.zip (7.9 KB)

I believe (but I have not tested) all you need to do is zip the parts of the Excel XML documents, and rename as a “.xlsx” file.

Having said that, have you considered using either AppleScript or VBA to make the changes to the Excel file? That might be easier and more reliable.

1 Like

You can use BBEdit to open XLSX files and modify the XML files they contain directly; no need to change the extension, unzip, rezip, or extract. With a quick test file that contains a text box 'drawing', you can see that you can find the drawing1.xml in the same location directly in BBEdit here:

Once you open the XLSX file in BBEdit and edit its contents like this, you can just save your work and the changes will show up the next time you open the file in Excel. I haven't done this with drawing objects before, but I have done this to modify other Excel files, and can verify that it works fine.

2 Likes

That's great news!

First I tried this (thanks @JMichaelTX for reminding me of VBA):

But as it turned out, the text boxes walked like a text box, quacked like a text box ... but were some something different. Perhaps MS Word objects? Anyway, this surprising use of BBEdit gives hope.

One strange thing: my View > Wrap command is greyed out. How did you manage to have your text wrapped?

Next step for me will be: Save the 'drawing' as an XML file. Modify (translate) it and put it back in BBEdit. If that works too, I'll try to build (get) an AppleScript to save all files drawing*.xml as separate XML files. And one that puts everything back after modification. But first let me do this initial test (later today).

Thanks the both of you for your answers!

1 Like

I actually didn't have to do anything, because I have text soft-wrapped by default:

But you can also turn it on per-document even if that menu is grayed out by clicking the gear button at the top left of a document:

02 PM

Or by pressing ,:

5

Both ways will bring up an individual document's text options.

Hey @ALYB,

This doesn’t become active until you’ve clicked into the document in the edit panel of the file viewer window.

-Chris

The easiest way to determine what is and how to deal with it is to record a macro in Excel where you manually make the changes in the Excel UI that you want.
The view/edit the VBA macro.

That’s very cool!

@ALYB,
Using that approach to develop an automation, one would then need to script BBEdit to make the changes and save the file. I haven’t done much scripting of BBEdit, but my understanding is that it is very scriptable. See:
Scripting and Automation - BBEditExtras

Although the BBEdit approach is very cool, my personal approach would be to first try a VBA Macro. I’ve written many of these, and they are quite powerful. If need be, you can call a VBA macro from an AppleScript. OR, maybe just write the entire “macro” using AppleScript.

1 Like

While a small change didn’t corrupt the file, changing all drawings in a test file or even removing the content, corrupted the XLSX file. So, I’ll probably have to tackle this via a Word macro.

I assume you meant a “Excel” macro.
As mentioned above, you can start by recording an Excel VBA macro, going through the steps manually. Then edit the macro as needed.

Let me know if you need help.