Category Archives: ExcelWriter

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, WordWriter

Reporting Services Error: Report must be created with OfficeWriter Designer version 3.8.1.85 or later

Problem

When a report is exported from SSRS 2008 after upgrading to OfficeWriter 4.x, the following error message appears:

OfficeWriter report was deployed to Microsoft SQL Server 2008 must be created with OfficeWriter Designer version 3.8.1.85 or later. This report was created with version [version number] and must be run on SQL Server 2005 or earlier.

Solution

There are two known causes for this message, depending on the [verison number] of the Designer:

Designer version 3.8.x – 3.9.x

Full support for SQL Server Reporting Services 2008 was officially introduced in OfficeWriter 4.x. Exporting reports that were designed with an earlier version of the OfficeWriter Designer to SSRS 2008 will result in the error message above.

This message indicates that the report needs to be upgraded with a later version of the Designer.

To upgrade a report:

  1. Follow the instructions for removing the old version of the Designer and installing the new version of the Designer.
  2. Open the report in the new version of the Designer.
  3. Save or publish the report from the OfficeWriter Designer toolbar.

This will make several changes to the RDL to make it compatible with SSRS 2008.

Designer version 4.x

This is a known issue that occurs when the following conditions are met:

  • The report was created in Visual Studio 2008 for SQL Server 2008
  • OfficeWriter Designer version is 4.5.0
  • OfficeWriter v4.1.0 is deployed on the report server

The solution is to upgrade the OfficeWriter on the server from v4.1.0 to v4.5.0 or later.

NOTE: We generally recommend that the version of OfficeWriter on the server matches the version of the OfficeWriter Designer.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, WordWriter

OfficeWriter Designer Warning: Feature is only available in Enterprise Edition

Problem

OfficeWriter Enterprise Edition is installed on the server, but the warning message: WARNING! This feature is only available in Enterprise Edition of OfficeWriter pops up when using certain features of the Designer.

Solution

This warning message displays when using any features that are only supported with Enterprise Edition of OfficeWriter, such as Reporting Services formulas. This is because the OfficeWriter Designer does not have the capability to detect what version of OfficeWriter is installed in Reporting Services.

The common scenario is for OfficeWriter for Reporting Services to be installed on a server, with the OfficeWriter Designer distributed to end-users, who design reports and upload them to the server. The Designer has no means of determining if the OfficeWriter on the server is Enterprise or Standard Edition, but end-users should be aware that if the OfficeWriter on the server is not Enterprise Edition and they use Enterprise-only features, their report may not render properly.

The OfficeWriter Designer can be used on the same machine that has OfficeWriter for Reporting Services installed, such as on a development machine, but the Designer does not require OfficeWriter to be installed. This is why that alert displays regardless of what version of OfficeWriter is installed.

To avoid seeing the warning message when using features that are only available in OfficeWriter Enterprise Edition, un-check ‘Show this warning when Enterprise Edition feature is used.’

If only Standard Edition is installed on the server, to avoid the warning message and hide Enterprise Edition-only features, click ‘Hide EE Features’ button at the bottom of the pop-up window.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services

OfficeWriter Designer prompts to publish during preview

Problem

The OfficeWriter Designer keeps prompting to publish the report when attempting to view the report from Excel. This happens even when the report is retrieved from the server and no changes are made to the report before trying to preview.

Solution

How View in the OfficeWriter Designer works

When the View button is clicked, the OfficeWriter Designer does several things:

  1. If the report has not been published, prompt the user to save and publish the report.
  2. If a previously published report has been changed in Excel, prompt the user to save and re-publish
  3. Make a request to Reporting Services to export/render the published report using the OfficeWriter renderer (OfficeWriter for Excel)
  4. Download a copy of the rendered report and open in Excel

In order for the OfficeWriter Designer to view a report, the most updated version of the report must be published to the server.

Designer still prompts when no changes have been made to the report

Certain Excel features, such as macros and pivottables, can be set to automatically execute or update when the workbook is opened. This is considered to be a change to the file, even though there are no visible changes to the report.

The OfficeWriter Designer uses the same logic as Excel when determining if a change has been made to the file. This means that any time Excel would prompt a user to save changes made to a file, the OfficeWriter Designer will prompt to publish the changes to the server.

For example, if a report contains a pivottable that is set to update the data when the workbook opens, Excel will interpret this a change to the file, even though it appears as though nothing has changed. In this case, the Designer will ask the user to publish the report before the View function can be run.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, SharePoint, SSRS, WordWriter

Error installing OfficeWriter for Reporting Services 2012 in SharePoint integrated mode

Problem

When installing OfficeWriter for SQL Server Reporting Services 2012 in SharePoint mode (SharePoint 2010), the OfficeWriter installer aborts with message:

SoftArtisans OfficeWriter Setup Wizard ended prematurely because of an error. Your system has not been modified. To install this program at a later time, run Setup Wizard again. Click the Finish button to exit the Setup Wizard.

Solution

Starting in SQL Server 2012, the SharePoint integration for Reporting Services was re-architectured: SSRS 2012 is configured as a shared service against SharePoint 2010. Configuration information is stored in a database and can be managed through SharePoint Central Administration or with Powershell.

Due to this change in architecture, the OfficeWriter installer is not currently equipped to run against SSRS 2012 in SharePoint integration mode. We will be adding support for this configuration to the OfficeWriter installer in a future release.

Update 4/11/2013: Instructions for manually installing OfficeWriter in this configuration are now available in our documentation: Manual Installation for SSRS 2012 with SharePoint 2010. The fix to the installer will be available in a soon to be released version of OfficeWriter.

Note:

This does not happen outside of SSRS 2012 with SharePoint 2010. OfficeWriter has full compatibility with SQL Server Reporting Services 2012 in native mode starting in v8.2. OfficeWriter also works with other combinations of SSRS and SharePoint.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, WordWriter

Designer Install Error: No Office 2007 or 2010 found

Problem

Installing the OfficeWriter Designer 8.1.0 and higher throws the error:

No Office 2007 or 2010 found. Office 2007 or later must be installed to use this product. If you are using an earlier version of Office please download the installer version 8.0 or earlier.

Solution

Office 2010 is installed

This error may occur if you have the 64-bit version of Office 2010 installed.

The OfficeWriter Designer does not currently support the 64-bit version of Office 2010. There are no immediate plans to support the 64-bit version of Office 2010 because we have not seen much demand from customers and Microsoft recommends using the 32-bit version in most cases:

IMPORTANT The 32-bit version of Office 2010 is the recommended option for most people because it prevents potential compatibility issues with other 32-bit applications, specifically third-party add-ins that are available only for 32-bit operating systems. — Choose the 32-bit or 64-bit version of Microsoft Office.

The OfficeWriter Designer is supported on 64-bit operating systems, but requires a 32-bit version of Office. If no 32-bit version of Office 2007 or 2010 is detected on the machine, the installer will throw the error above.

Office 2003 is installed

Starting in OfficeWriter 8.1, the OfficeWriter Designer is only supported in Office 2007 and 2010. If you need to use the Designer in an earlier version of Office, please download OfficeWriter Designer 8.0 or earlier.

No Office is installed

The OfficeWriter Designer is an Office add-in and requires a 32-bit version of Office to be installed.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, WordWriter

Designer Error: Could not load an object because it is not available on this machine

Problem

After installing the OfficeWriter Designer for Office 2007 or 2010, Excel or Word throws the following error three times when opened:

Could not load an object because it is not available on this machine.

Following the instructions in Designer throws compile error after installing Microsoft August 2012 updates to re-register MSCOMCTL.OCX do not resolve the issue.

Solution

In August 2012, Microsoft released Security Bulletin MS12-060 which targeted a security vulnerability in the Microsoft Common Controls SP6 (MSCOMCTL.OCX). As part of the security update, a new version of the library was distributed to all Office 2010 SP1, Office 2007 SP2 and Office 2007 SP3 installations.

The OfficeWriter Designer requires the new version of MSCOMCTL.OCX. If the older version is present, the above error will occur.

To resolve this issue:

This will require admin privileges and we recommend that you consult your IT staff before applying these updates.

1. Install the appropriate service pack to Office 2007 or 2010:

If the latest service pack is not applied, the new version of MSCOMCTL.OCX will not be installed on the machine, even if the security update is run.

To verify that the installation completed successfully, follow the instructions in this How to tell which Office service pack is installed blog post.

2. Download and install the version of the update for your version of Office:

3. If you experience a compilation error when you open Office, try following the instructions in Designer throws compile error after installing Microsoft August 2012 updates to re-register MSCOMCTL.OCX.

Additional Help – Installing Service Packs

Before you can install the service pack, you may need to clear some disk space. Microsoft has an article on Strategies for freeing disk space, which suggests that you Delete files using the Disk Cleanup tool. If you are working on a server OS, such as Windows Server 2008, the Disk Cleanup tool may not be installed. Instructions for how to install the Disk Cleanup tool are outlined in this MSDN forum post.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, WordWriter

Designer Compile Error: Cannot run the macro ‘OnLoad’

Problem

After installing Microsoft’s August 2012 security updates on a machine with the OfficeWriter Designer installed, you may encounter the following:

Office 2003, 2007, or 2010 throws an error when opening: Compile error: Automation error Unspecified Error followed by Cannot run the macro ‘OnLoad.’ The macro may not be available in this workbook or all macros may be disabled.

Details

One of the Microsoft August 2012 security updates targeted a vulnerability in Windows Common Controls: MS12-060 – Vulnerability in Windows Common Controls.

On the detail page (2720573), it mentions that there is a known issue with all versions of Office when installing this update:

“Windows Common Control-based embedded Active-X controls may fail to load within pre-existing office documents, within third-party applications, and when you insert new controls in developer mode.”

This affects the OfficeWriter Designer as well.

Update

Alternate error message:

Compile error: Automation error Unspecified error

This may also affect customers who switch between Word 2007 and Word 2010. When you switch back to Word 2007, this error may be re-introduced and you will need to follow the solution steps again.

Solution

To resolve this issue, MSCOMCTL.OCX must be re-registered on the machine:

1. Run the command prompt as Administrator

2. Register MSCOMCTL.OCX:

  • For 64-bit operating systems, type the following: Regsvr32 “C:\Windows\SysWOW64\MSCOMCTL.OCX”
  • For 32-bit operating systems, type the following: Regsvr32 “C:\Windows\System32\MSCOMCTL.OCX”

3. A computer restart may be necessary

For more information about resolving this issue, please refer to the following Microsoft KB articles for Office 2010, Office 2007, or Office 2003.

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Error: BC3002: Type ‘WordTemplate’ is not defined

Problem

When working with a dynamically compiled aspx page (meaning that it does not have a code behind aspx.cs or aspx.vb page), the following error message may occur:

Compiler Error Message: BC30002: Type ‘WordTemplate’ is not defined

The error is thrown on the line that instantiates the WordTemplate object.

Solution

Note: Although the error message lists WordWriter, this applies to ExcelWriter as well.

Most customers have precompiled web applications or web sites, which means that the site is precompiled from Visual Studio with references to any necessary DLLs, including SoftArtisans.OfficeWriter.ExcelWriter.dll or SoftArtisans.OfficeWriter.WordWriter.dll. For more information on how to add OfficeWriter to a precompiled .NET application, see Adding OfficeWriter to your .NET Application.

However, if working with dynamically compiled pages, the references aren’t added ahead of time. The sample principles for precompiled applications apply here as well:

  1. Make sure to import or include the SoftArtisans.OfficeWriter.WordWriter (or SoftArtisans.OfficeWriter.ExcelWriter) namespace.
  2. Make sure to import the System.Web namespace because the ExcelWriter and WordWriter output options have a dependency on System.Web.
  3. Make sure that SoftArtisans.OfficeWriter.WordWriter.dll or SoftArtisans.OfficeWriter.ExcelWriter.dll is in the bin directory of the application. The alternative is to install the DLL into the GAC.

ExcelWriter, Knowledge Base, OfficeWriter

PivotTables are not displaying correctly

Problem

After creating or modifying a PivotTable with ExcelWriter 8.4 or later, the PivotTable has not changed or is empty.

Solution

ExcelWriter does not have the ability to render PivotTables. Changes made to a PivotTable using ExcelApplication will not take effect until the PivotTable is refreshed. There are several ways to refresh the PivotTable:

  1. If you are editing an existing PivotTable, in the original file, go to PivotTable Options > Data > Check off “Refresh data when opening the file.” This will ensure that Excel refreshes the PivotTable as soon as the output is opened.
  2. If you are creating a new PivotTable from scratch, make sure to set the PivotTable.PivotTableSettings.RefreshOnOpen to true. This will force the PivotTable to refresh automatically when the output is opened.
  3. If you don’t want to have Excel refresh the PivotTable when the workbook opens, you can refresh the PivotTable manually or with a macro. Right-click the PivotTable and select Refresh.

We encourage users to keep “Refresh on Open” enabled when working with PivotTables in ExcelWriter to make sure that modified PivotTables load correctly in Excel.

ExcelWriter, Knowledge Base, OfficeWriter

ExcelWriter Error: Resulting sheet would have more rows than are permitted by the Excel format

Problem

The Excel .xls binary format has a limit of 65536 rows per worksheet. If you are importing data with ExcelWriter’s ExcelTemplate object (or our SSRS renderer, which uses the ExcelTemplate object) it is possible to get an error that Excel’s row limit was exceeded even though you know you have imported much fewer than 65K rows.

This problem is usually caused by cells or formulas that already exist in the template file. When ExcelWriter populates a spreadsheet with the ExcelTemplate object, it inserts a new row for every row of data and pushes all existing rows down. If the total number of pushed down rows plus inserted rows exceeds Excel’s limit, an exception will be thrown. Then, after the data has been imported, ExcelWriter goes through the entire workbook and updates any formulas that reference the data marker cells, stretching the formulas to include all the newly inserted rows. If the cell range in a formula is stretched beyond Excel’s row limit, again an exception will be thrown.

Solution

Cells

Sometimes people take a previously populated report and turn it into a template. They may have cleared the cell values, but the template may still contain many rows of cells that are blank or contain only formatting. These rows will be pushed down when the data is imported.

The solution is to clean up the template file by actually deleting all unnecessary rows and columns rather than just clearing cell values. Also, it’s generally a good idea to set background formatting using row or column headers rather than by selecting large areas of cells.

Formulas

Formulas can be found in many parts of a workbook, including cells, charts, conditional formatting, and named ranges. Make sure that all formulas reference the minimum number of rows necessary and bear in mind that ExcelWriter will stretch certain formulas when new rows are inserted.

Hidden named ranges

It is important to know that Excel sometimes creates hidden named ranges behind the scenes for internal purposes. You won’t be able to find them through the Excel UI, but if they reference data marker cells, ExcelWriter will update them. Common cases where Excel created hidden named ranges are when the user applies an AutoFilter or AdvancedFilter to a range of cells. This occurs whether the filter is applied from the Excel UI or from a VBA macro. For this reason, check your macros carefully to make sure they are not applying any filters to unecessarily large cell ranges that include data markers. If you have any unnecessarily large hidden named ranges in your macros, you can modify them by changing the VBA code and running the macro again. If you are having trouble modifying or removing hidden named ranges, you can write some VBA code to do it. The names always end in “`_FilterDatabase”

Tip: If you have Excel 2007 or above, here is a quick way to see if you have any hidden named ranges in an .xls file without writing any VBA:

  1. In Excel, save a copy of the template file as .xlsx or .xlsm
  2. Rename to .zip and unzip the file
  3. In the subdirectory “xl”, open the file workbook..xml
  4. Look in the Section
  5. Look for defined names that end in “_FilterDatabase”. Here is an example of something you might see: <definedName name="xlnm.FilterDatabase” localSheetId=”3″ hidden=”1″> Data!$A$1:$BQ$65000
  6. If you temporarily change hidden to 0, it will show up in Excel under the named item manager.

Note: We don’t recommend permanently modifying or removing these hidden named ranges in the XML directly as that could have unexpected results.