Formatting lost in the VB Macro code.

Issue #717 resolved
Hugh Foster
created an issue

As part of testing issue #705, I discovered that the macro is preserved but has a slightly different behavior. The currency formatting in the pivot table that is generated by the macro is no longer preserved.

This worked in openpyxl 2.2.1 but was broken in 2.3.5 and is also not working with the change made in #705.

The test files attached to #705 can be used to reproduce the issue:

using Python 3.4 and openpyxl 2.2.1

  1. execute xl_text.py (make sure the xlsm is in the same directory).
  2. open test_result.xlsm
  3. Enable the macro to run.
  4. Notice that column D (Sum of Balance) in the pivot table has the currency formatting.

Repeat the steps 1-3 using openpyxl 2.3.5 or greater. Note that column D (Sum of Balance) longer has the currency formatting.

Comments (7)

  1. CharlieC

    Please provide some more specific information about what particularly does not work. The file itself contains nothing formatted for currencies.

  2. Hugh Foster reporter

    The macro does the styling with the following code:

            Columns("D:D").Select
            Selection.Style = "Currency"
            Columns("D:D").EntireColumn.AutoFit
            Cells(4, 1).Select
    

    The behavior of the sheet has changed from 2.2.1 to 2.3.5+. I can no longer format any column with currency (manually or otherwise). I have to specify US style currency. The macro needs to be changed from above to:

        Selection.NumberFormat = _
            "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* ""-""??_);_(@_)"
    
  3. CharlieC

    The problem seems to be related as to how Excel internally uses the "built-in" named styles. openpyxl 2.4 now provides for full support for them so that you can now do things like ws['A1'].style = "Currency" compatibility with macro behaviour is anti-goal (we've now idea how it works) but we might be able to tweak the implementation.

  4. Log in to comment