ToolBox -

Symptom

After upgrading to Office Connector version 2.01.0041 the following message is displayed when opening a workbook that was saved using a prior version of Office Connector:

This workbook was saved using a prior version of Office Connector.  This workbook needs to be optimized to run faster under the new version.  If you do not optimize this workbook, it may take longer to refresh than it did before.

Do you want Office Connector to optimize your workbook now?

<Yes> <No>

Upon clicking Yes, another message is displayed stating “This workbook has been successfully optimized.  You must save the workbook in order to keep the optimizations”.  Following this, Office Connector functions within the workbook return a value of zero instead of the expected values.

Resolution

Option 1 – Disable the performance optimization features for the workbook

This option will cause will allow the workbook to function normally but without the benefit of the performance enhancements.  In some cases this action may result in somewhat slower performance as compared to previous versions of Office Connector.

Excel 2000-2003

1.       Click the File menu and then click Properties

2.       Click the Custom tab

3.       In the Name box, type:  UseFilters

4.       In the Value box, type:  1

5.       Click Add

6.       In the Name box, type:  UseAllColumns

7.       In the Value box, type:  1

8.       Click Add

9.       Click OK

10.   Save the workbook

11.   Close and re-open the workbook and confirm that no prompt appears asking if you want to optimize the workbook and then confirm that the formula values return the expected results.

Excel 2007

1.       Click the Office Button in the upper left corner

2.       Click Prepare and then Properties

3.       In the bar across the top (below the ribbon), click the drop-down arrow next to Document Properties and then click Advanced Properties

4.       Click the Custom tab

5.       In the Name box, type:  UseFilters

6.       In the Value box, type:  1

7.       Click Add

8.   In the Name box, type:  UseAllColumns

9.       In the Value box, type:  1

10.   Click Add

11.   Click OK

12.   Save the workbook

13.   Close and re-open the workbook and confirm that no prompt appears asking if you want to optimize the workbook and then confirm that the formula values return the expected results.

Option 2 – Complete the optimization by unloading and then reloading the Office Connector add-in

1.       Close the workbook without saving.

2.       Close and restart Excel.

3.       Open the workbook in question.

4.       When prompted to optimize your workbook, click No.

5.       Unload and reload the Office Connector add-in.  Click here for step-by-step instructions.

6.       Click Refresh on the Office Connector toolbar.

7.       When prompted to optimize your workbook, click Yes.

8.       If formula values now return the expected values, save the workbook.  If the problem persists close the workbook without saving and use Option 1 listed above to disable the performance optimization feature.

9.       Close and reopen the workbook to verify that you are not prompted to optimize the workbook and that the formulas show the expected values.