Category Archives: Knowledge Base

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, WordWriter

Designer Install Error: This software requires Excel 97 and/or Word 97 (or higher)

Problem

The installer for the OfficeWriter Designer v8.0 and below throws the following error:

This software requires Excel 97 and/or Word 97 (or higher), with Visual Basic for Applications (VBA) and MSQuery installed. This system does not appear to have Excel or Word installed. Please install Office before continuing.

Solution

Office 2010 is installed

The OfficeWriter Designer v8.0 and below is not supported in Office 2010. Please install v8.1.1 or higher for Office 2010.

No Office is installed

The OfficeWriter Designer is an Office Add-In and requires a version of Office to be installed to use.

Knowledge Base, OfficeWriter, WordWriter

WordWriter generated document opens in protected mode in Word 2010

Problem

When a report rendered with WordWriter is opened in Word 2010, the document opens in “Protected Mode” (red warning banner). This might also happen in Excel 2010.

Solution

In Office 2010, Microsoft introduced a security feature which can cause files to be opened in Protected Mode with a red warning banner that locks the file for editing. The Protected Mode is activated when Office detects something in the file format that is not consistent with the security definition.

Unfortunately, since we do not have access to the definitions that the security feature uses to check files, we cannot predetermine what will cause the Protected Mode to activate. We have already addressed a number of items that can cause files to open in Protected Mode, but we are unable to know all the possible causes.

If you are using the latest version of the product and you encounter a file that opens in Protected Mode contact us.

ExcelWriter, Knowledge Base, OfficeWriter

ExcelWriter Error: This file does not appear to be a valid Excel 97 or later binary file

Problem

When trying to open an XLSX or XLSM file with ExcelApplication, you get the following error message:

This file does not appear to be a valid Excel 97 or later binary file. Please try opening in Excel 97 later by saving as “Microsoft Excel Workbook (*.xls)”

Solution

Support for the Office 2007/2010 file formats (XLSX, XLSM) was introduced for ExcelApplication in ExcelWriter 8. Earlier versions with throw the above exception when attempting to open an XLSX file with ExcelApplication.

Sign in to download product updates. Make sure to follow the instructions for installing a new version of the product.

ExcelWriter, Knowledge Base, OfficeWriter

Shapes and PivotCharts stretch when placed next to a PivotTable

Problem

If a pivotchart or drawing shape is placed next to a pivottable, it stretched over many rows in the output file. Also, if a pivotchart or drawing shape is placed beneath a pivottable, it gets pushed down the worksheet.

Solution

The issue of shape behavior was addressed in OfficeWriter 4.6.0. Download the latest version of OfficeWriter.

ExcelWriter, Knowledge Base, OfficeWriter

ExcelWriter leaves empty space for a chart title

Problem

When using ExcelApplication to create or modify charts, there appears to be an empty space for the chart title even though one wasn’t specified or the chart title is set to an empty string.

Solution

ExcelWriter will leave space for a chart title if any of the Chart.Title properties are set; this includes setting Chart.Title.Text to an empty string. This is because ExcelWriter assumes that if Chart.Title properties are being set, then there will be a chart title.

This mimics Excel’s behavior, as chart title properties cannot be accessed or modified unless there is a non-empty chart title.

To avoid having extra space for the title, only set Chart.Title properties when Chart.Title.Text is set to a non-empty value (i.e. you intend for the chart to have a title).

ExcelWriter, Knowledge Base, OfficeWriter

Exception: ‘[character]’, hexidecimal value 0x[value], is an invalid character

Problem

When running an Excel report rendered by ExcelWriter’s ExcelTemplate object, the report will throw the following eception if the data set contains certain special characters:

Unhandled Exception: SoftArtisans.OfficeWriter.ExcelWriter.SAException: ‘[character], hexadecimal value 0x[value], is an invalid character. at SoftArtisans.OfficeWriter.ExcelWriter.ExcelTemplate.Process()

This exception occurs if:

  • Data is imported into an Excel file using ExcelWriter
  • Files are .XLSX (Office 2007) or .XLSM (Office 2007 with macros enabled)
  • Data set contains a character that is not valid in XML

Note: Support for OOXML files was introduced in OfficeWriter 4.0, so this issue only occurs in OfficeWriter 4.0-4.6.0 (ExcleWriter 7.0 – 7.6.0).

Solution

Option 1: Upgrade to OfficeWriter 4.6.1 or later (recommended)

This issue was addressed in OfficeWriter 4.6.1 (ExcelWriter 7.6.1). As of version 7.6.1, ExelWriter correctly preserves characters that are not valid in XML, so they will appear in output.

Product updates for OfficeWriter can be downloaded at www.officewriter.com/product-updates.

Note: Make sure to uninstall the old version of OfficeWriter before installing a new version.

If you are experiencing this issue with OfficeWriter for SQL Server Reporting Services and need to upgrade the server-side installation of OfficeWriter, we generally recommend upgrading the client-side installation of the OfficeWriter Designer as well.

Option 2: Scrub data of invalid XML characters

There are certain ranges of characters that are not valid XML. The XML specification has defined which ranges of characters are supported in XML files. These files cannot be written into XML files, which includes the OOXML file formats. Excel handles the special characters by encoding them before they are written to file.

Since ExcelWriter 7.0-7.6.0 does not handle these characters properly, the other solution is to remove any characters that are not valid XML, if the characters don’t need to be preserved.

ExcelWriter, Knowledge Base, OfficeWriter

ExcelWriter Error: Items in the array exceed 256 characters

Problem

When you specify List data validation in Excel with a sequence of comma-separated values, Excel prevents you from entering more than 255 characters, including commas. Analogously, when you create List data validation in ExcelWriter with an array of values, the CreateDataValidation method throws the exception: Items in the array exceed 256 characters, if the combined length of all of the elements in the array is longer than 255 characters. The limit is actually 255 rather than 256 as stated in the exception message. In practice, the actual limit would be lower if there are multiple elements in the array, as ExcelWriter has to insert tokens to demarcate individual values.

Solution

In order to prevent the exception, you can specify the list as a formula pointing to a range containing the desired values rather than as an array. This approach parallels Excel’s second way to create List data validation. If it’s desirable to hide the validation values, you can place the range on a non-visible area of the worksheet, or on a separate hidden worksheet.

 // Create validation object based on desired cell range 
DataValidation dv = workbook.CreateDataValidation(DataValidation.ValidationType.List, "=Sheet1!A1:A4"); 

// Alternatively, use an existing named range 
DataValidation dv2 = workbook.CreateDataValidation(DataValidation.ValidationType.List, "=ValidationRange"); 

// Set cell B1 to use validation 
worksheet.Cells["B1"].DataValidation = dv;

For more information about creating data validations, please refer to our documentation

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Data not imported when using a recordset for a data source

Problem

When using a recordset as a datasource with the ExcelWriter template object, the cells are not populated with data. This typically happens when the recordset is at EOF due to manipulation in the script. When the template object’s .Process() method is called, the data binding occurs.

Solution

Before calling .Process(), you should move to the first record in the recordset with this command:

 oRS.MoveFirst()

That way, the recordset will be passed properly to the object.

Knowledge Base, OfficeWriter, WordWriter

New line characters are not displayed in WordTemplate output

Problem

The data source for a merge field contains text with new line characters (e.g. Chr(13) & Chr(10), “/n”), but the output is not being displayed on multiple lines in OOXML documents (.DOCX, DOCM) in WordWriter 4.0.0-4.1.0.

Solution

This issue was resolved in WordWriter 4.5.0. WordWriter software updates are available for download at www.officewriter.com/product-updates.