Tag Archives: excelwriter

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Install Error 1608: Unable to Create InstallDrive Instance. Return code -2147024891

Problem

When attempting to run the installer for OfficeWriter 4.x or below, InstallShield returns the error:

1608: Unable to Create InstallDriver Instance. Return code -2147024891.

This error has been known to occur for some users when installing or uninstalling OfficeWriter. According to InstallShield, “Error 1608 is a very system specific issue and is often caused by limited system privileges, incorrect DCOM settings, or another malfunction on the computer.”

Solution

In most cases, the instructions in the following InstallShield article resolve this problem. For many of our customers, this error has been resolved by simply following the instructions regarding DCOM Settings (section A).

http://consumer.installshield.com/kb.asp?id=Q108440 (note: installshield’s website can be very slow sometimes.)

If after trying all of the suggestions in the InstallSheild article, you are unable to install or uninstall OfficeWriter using the automatic installer, you will need to perform a manual installation and/or uninstallation.

Instructions for uninstalling and installing OfficeWriter can be found in our documentation. Although the instructions are labeled for ExcelWriter, the same steps apply to installing WordWriter.

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.

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.