MySQL Bugs: #73467: Warning about macro-free workbooks when closing & saving an Excel workbook

Bug #73467	Warning about macro-free workbooks when closing & saving an Excel workbook
Submitted:	4 Aug 2014 23:10	Modified:	22 Oct 2015 21:49
Reporter:	Javier Treviño	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S3 (Non-critical)
Version:	1.2.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A pop-up warning message stating some features can't be saved in a macro-free workbook appears when closing a Workbook that has not been saved.
The features that can't be saved are functions stored in defined names that are added by MySQL for Excel to handle some internationalization issues.

How to repeat:
1. Open MS Excel.
2. Select the Data tab from the ribbon
3. Click the MySQL for Excel Icon and open the panel.
4. Open a connection, select an schema and click over any table.
5. Click on Edit session.
6. Close Excel, a message asking if you want to save the workbook will be displayed, select Yes.
7. A new pop-up message stating some features can't be saved in a macro-free workbook will appear. This should not appear since no formulas were explicitly added in the first 6 steps.

Suggested fix:
The defined names that are added by MySQL for Excel to handle some internationalization issues should be removed before saving the workbook, and added again during the session of MySQL for Excel.

Posted by developer:
 
Added code to remove the internationalization formulas used by MySQL for Excel stored in Workbook Names before the Workbook is saved, and they are added back again after the save operation.

Fixed as of the upcoming MySQL for Excel 1.3.2 release, and here's the changelog entry:

A pop-up warning message stated that some features could not be saved in a
macro-free workbook would appear when closing a Workbook that was not
saved. 

Internationalization formulas used by MySQL for Excel that are stored in Workbook Names are now removed before the Workbook is saved, and these formulas are added again after the save operation.

Thank you for the bug report.

I have just installed version 1.3.3 and get the same message when trying to save an xlsx file.

Reopening the bug since the problem still happens in Excel 2010 and 2013.
Possibly in Excel 2007 as well.

Posted by developer:
 
Reopening the bug since the problem still happens in Excel 2010 and 2013.
Possibly in Excel 2007 as well.

The warning continues when I use MySQL for Excel 1.3.4 and Excel 2013 to save .xlsx file. Any idea what might cause this and fix?
Thanks.
Kelvin

Yes - happens in Excel 2007 and MySQL for Excel 1.3.4

Had to disable MySQL for Excel Com Add-In (Excel options) to stop the prompt...

Still occurring with Excel 2010, MySQL for Excel version 1.3 (not sure about minor).  I thought it was because of something I added to another addin. Disabling that addin, I am able to reproduce this error every time with a new workbook and no content. On the first save, the error does not occur. Only upon opening the workbook and saving again does the error popup.

A side effect is that autosave no longer works, and more complicated spreadsheets crash more frequently!

Related to this bug is the following error when using 'Refresh All' on spreadsheets with tables withinn them (once for each table):

"An error ocurred trying to refresh data in Excel table <table>.  Do you want to continue loading the remaining Excel tables?"

Exception from HRESULT:0x800A03EC.

That exception occurs on a new spreadsheet in a table with only one cell of data (as well as all others). 

Disabling MySQL for Excel fixes both of these problems.

Made a test with MySQL for Excel setting the Autosave to every minute, verified the Autosave is working correctly.

So if autosave is breaking in any way, a new bug needs to be created with specific steps as to how to reproduce that behavior.

Posted by developer:
 
Stopped using some hidden defined names to store formulas to transform date values into a the date format used by MySQL. These defined names were being detected by Excel as macros or user functions that could not be saved in macro-free workbooks, thus generating an annoying warning every time a Workbook was saved, if the add-in was active.

Posted by developer:
 
Fixed as of the upcoming MySQL for Excel 1.3.5 release, and here's the changelog entry:

Stopped using hidden defined names to store formulas that transform date
values into a date format used by MySQL. These defined names were being
detected by Excel as macros or user functions that could not be saved in
macro-free workbooks, thus generating bogus warnings every time a Workbook
was saved when the Excel plugin was active.

Thank you for the bug report.