Saved .xslm file must be repaired to open; file loaded with keep_vba=true; only some radio button controls saved

[Andy Yee]

I am writing to an .xlsm Excel file containing VBA controls on numerous sheets including radio buttons.  When I load the file with openpyxl in my script,  I am using the keep_vba=true option.  After the  data is written and the file is saved using openpyxl, I get the Repair prompt from Excel when I open the saved file to view.  Excel 'repairs' the file by removing a list of drawings ('Removed part: Drawing Shape').  The radio buttons are preserved on half of the sheets but scrubbed on the others.

Tab (j) lost its radio buttons. Tab (b) did not.  Files attached.

I am using Python 3.6, WIN7, and openpyxl just upgraded to 2.5.5.

Any help would be appreciated - thanks very much.

[Charlie Clark]

Am .08.2018, 22:03 Uhr, schrieb Andy Yee <jpowe...@gmail.com>:

> I am writing to an .xlsm Excel file containing VBA controls on numerous
> sheets including radio buttons. When I load the file with openpyxl in my

> script, I am using the *keep_vba=true* option. After the data is

> written and the file is saved using openpyxl, I get the Repair prompt
> from Excel
> when I open the saved file to view. Excel 'repairs' the file by
> removing a list of drawings ('Removed part: Drawing Shape'). The radio
> buttons are
> preserved on half of the sheets but scrubbed on the others.
>
> Tab (j) lost its radio buttons. Tab (b) did not. Files attached.
>
> I am using Python 3.6, WIN7, and openpyxl just upgraded to 2.5.5.
>
> Any help would be appreciated - thanks very much.

Thanks for the report and the files. Is the script necessary to
demonstrate the error? I'm not sure which file is which and I can open
them both in Excel 2016 for Mac without problems. FWIW the naming
convention I use when working with bugs is bugXXXX-xl.xlsx for a source
file and bugXXXX-25.xlsx for a file that has been processed by openpyxl.

I suspect the difference in appearance may be related to the use of
control properties which are not currently supported by openpyxl. Please
let me know if you'd be interested in sponsoring development of them.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Andy Yee]

Thank you Charlie.  The script was posted in the event it would help to reference.  File naming conventions noted for next time.

1.  The radio button control type is the same on all sheets where they appear.  The file named "...loaded..." is the original source file before it was written to.  Radio buttons are visible on all sheets where they exist including tab (j) Atmospheric Tanks.  The file named "...after saving..." is the file that was written to & saved by openpyxl.  In Excel 2013 I see radio buttons on tab (b) but not tab (j).  Same Excel control type but different behavior.  This is why I don't think it is a case of control types not supported by openpyxl - the radio button are preserved on some sheets (like tab (b) Pneumatic Devices) but not on others (like tab (j) Atmospheric Tanks).

2.  I will see about upgrading to Excel 2016 to check the processed file again.  I will post an update as to whether a change in Excel versions fixes the problem.

[Charlie Clark]

Am .08.2018, 19:54 Uhr, schrieb Andy Yee <jpowe...@gmail.com>:

> Thank you Charlie. The script was posted in the event it would help to
> reference. File naming conventions noted for next time.
>
> 1. The radio button control type is the same on all sheets where they

> appear. The file named "*...loaded..*." is the original source file

> before it was written to. Radio buttons are visible on all sheets where
> they

> exist including tab (j) Atmospheric Tanks. The file named "*...after
> saving..*." is the file that was written to & saved by openpyxl. In

> Excel 2013 I see radio buttons on tab (b) but not tab (j). Same Excel
> control
> type but different behavior. This is why I don't think it is a case of
> control types not supported by openpyxl - the radio button are preserved
> on some sheets (like tab (b) Pneumatic Devices) but not on others (like
> tab
> (j) Atmospheric Tanks).
>
> 2. I will see about upgrading to Excel 2016 to check the processed file
> again. I will post an update as to whether a change in Excel versions
> fixes the problem.

It may fix the error raised by Excel, but it won't fix the missing
controls. The current handling of VBA and its associated items is "best
try" with no guarantee and relies heavily on preserving the structure of
the original archive, which openpyxl does not guarantee. If you compare
(you can try the OOXML Productivity Tool for this but be warned it's very
slow for these files) the file structure of the two files, you'll notice
that some files are preserved, while other's aren't. This can probably be
largely fixed by extending the library to support the control properties
XML.

[J Powell]

Is this productivity tool you are referring to one of the tools available in the Open SML SDK 2.5 for Microsoft Office (OpenXMLSDKToolV25.msi) install?  I would appreciate any additional details on extending the library for control properties. Example of library that might work?  If this is something that would have to be done by the source file author, then I may be out of luck since these are protected EPA Excel workbook files.

Thanks again Charlie.

--
You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/eqArCVHoqsg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openpyxl-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Charlie Clark]

Am .08.2018, 23:20 Uhr, schrieb J Powell <jpowe...@gmail.com>:

> Is this productivity tool you are referring to one of the tools available
> in the Open SML SDK 2.5 for Microsoft Office (OpenXMLSDKToolV25.msi)
> install?

Yes, though do note that it may take a long time to compare the two files:
over ten minutes on my machine.

> I would appreciate any additional details on extending the
> library for control properties. Example of library that might work? If
> this is something that would have to be done by the source file author,
> then I may be out of luck since these are protected EPA Excel workbook
> files.

The source files don't need changing; all the work needs doing in openpyxl.

[J Powell]

OK, I appreciate the reply and the direction as to the right path to go down. Thank you.

--
You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/eqArCVHoqsg/unsubscribe.

To unsubscribe from this group and all its topics, send an email to openpyxl-user...@googlegroups.com.

[Markus Schmid]

Hello All,

how has this issue been solved?   I created an Excel Template that has an acitveX button and uses the Microsoft Excel data validation function (creaes a dropdown based on data from a range)....

...my problem is the same as described above.  When I update this excel file with openpyxl then the button is deleted and the validation function is also deleted... ...interestingly the VBA code remains intact and can be viewed with the Excel VBA-IDE....

Any insights to this would be appreciated

Markus

[Charlie Clark]

On 10 Apr 2024, at 14:13, Markus Schmid wrote:

> Hello All,
>
> how has this issue been solved? I created an Excel Template that has an
> acitveX button and uses the Microsoft Excel data validation function
> (creaes a dropdown based on data from a range)....
>
> ...my problem is the same as described above. When I update this excel
> file with openpyxl then the button is deleted and the validation function
> is also deleted... ...interestingly the VBA code remains intact and can be
> viewed with the Excel VBA-IDE....
>
> Any insights to this would be appreciated

No promises, but you might find a checkout of the 3.2 branch to be more useful: it includes much greater support for things like ActiveX controls.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office

Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226