Is ION Enterprise Reporter compatible with Excel 2007? Will it successfully generate reports?

Issue
User would like to use Excel 2007 with ION Enterprise

Product
ION Enterprise 6.0

Enviornment
Excel 2007

Resolution
*Warning: Take backups of any files prior to modifying them. Ensure the backups are in a location that will not be overwritten (i.e. the Desktop). *

Reporter can successfully generate reports in Excel 2007. There are some potential issues that can be worked around.

1. If there is no default printer installed on the computer, Reporter will encounter an error when it tries to open Excel 2007:

• • *ERROR* Error 1004 (Unable to set the Zoom property of the PageSetup class) has occurred in Microsoft Office Excel

    Reporter can not successfully generate a report when this happens the user must hit Cancel.

    Workaround: install a default printer on the computer, even if it is a dummy driver for a printer that doesn't really exist.

    This is not necessary for Reporter in ION Enterprise 5.6 - it has been changed to avoid the problem.
    

2. In the Power Quality report, the hyperlinks to individual Incidents & Waveforms do not work.

• • Clicking on them has no effect.

    There are some VBA features that have been deprecated in Excel 2007. Clicking on a hyperlink in a Power Quality report calls some code that doesn't work in Excel 2007.

    Workaround: edit the macro that has the bad code.

    Caution: Recklessly editing the Resource.xls file can potentially break the Power Quality report. Always make backup copies of files before editing them.

i. In the \ION Enterprise\system\bin folder, there is a spreadsheet called "Resources.xls". Open it and enable macros.
ii. Open the Excel VB Macro Editor. Double-click on PRIVATEWaveform (PRIVATE|Waveform) to edit its macros.
iii. In macro TransformWaveCellToRange, there are 2 lines near the end that refer to Me.ChartObjects.Locked (lines 118 & 120). Comment them out with a single apostrophe so it looks like this:

• • (...)

    d.PutInClipboard

    ' Me.ChartObjects.Locked = True ' avoid bad paste (excel bug)

    Me.Cells(3, PointCol).PasteSpecial

    ' Me.ChartObjects.Locked = False

    Set TransformWaveCellToRange = Range(Me.Cells(3, PointCol)... End Function

iv. Save and close Resources.xls.

• • When you run a Power Quality report in Reporter, it copies the waveform handling functions from Resources.xls. After making this change, any future Power Quality reports will have the offending lines of code commented out, and the hyperlinks will work.

    To fix any old reports with broken hyperlinks, there are 2 options:

(a) Re-generate the desired PQ report thru Reporter, after making this change.
(b) Edit the macro in the report itself as per the instructions above.

3. On some systems, the Power Quality report may fail to run, with Excel 2007 giving an error.

• • "Run-time error '-2147467259 (8004005)': Invalid Parameter".

    In the PQ report, there is another call in one of the macros to an Excel property that has been deprecated in Excel 2007 (Axis.CategoryType). This is what causes the error.

    Workaround: edit the macro that has the bad code.

    Caution: Recklessly editing the macros can potentially break the Power Quality report. Always make backup copies of files before editing them.

i. Open the Power Quality report in Excel. Enable macros.
ii. Open the Excel VB Macro Editor. Double-click on the 'pqViolation' Class Module to open it in the editor.
iii. In Sub AddChart, there is a line that refers to Axis.CategoryType (line 377). Comment out this line with a single apostrophe so it looks like this:

(...)
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
vGraphXLabel '"Duration of Disturbance in Seconds"
' .Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue).AxisTitle.Font.Size = 9

iv. Save and close Power_Quality.xls.

• • You will need to do this with any other PQ reports you have, as well as the Power_Quality.xls template in the IONEnterprise\system\reports folder.

More Information

The lines of code to Lock & Unlock the current ChartObjects was apparently made to address a bug in Excel 2000 that has since been fixed.

The reference to Axis.CategoryType may not cause problems on all Excel 2007 installations.