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:
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?
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.
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).
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.
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.
