Category Archives: OfficeWriter

ASP.NET, OfficeWriter, SharePoint

Solving the ‘double hop’ issue using Secure Store


[Image via Fabian Williams]

Last week I was working on some ASP.NET web forms that generated internal reports against MS CRM using ExcelWriter and I wanted to port the application to one of our SharePoint instances. Though it seemed simple at first, I ran into a few issues. One of the issues happened to be authentication related. It was a typical ‘double hop’ problem where this SharePoint instance was using integrated Windows NTLM authentication and my code was trying to access the CRM SQL Server database. By nature, NTLM is unable to pass the credentials to the database thus producing access errors. (You can find more information on the NTLM issue and using Kerberos as a solution here.)

Since we don’t have Kerberos configured on this environment, our best solution was Secure Store. This service allows a user to authenticate with domain credentials and then use an account established in Secure Store to access the database. In our case, this was the read-only CRM account. This also enables easy to use and convenient access control using AD groups. Continue reading Solving the ‘double hop’ issue using Secure Store →

OfficeWriter

Andrew Brust on OfficeWriter: “Enhanced Integration for Microsoft Business Intelligence”

[Click here to read the full review!]

To many of you, the name Andrew Brust might ring a bell. After all, this is a man who’s been working with, shaping and talking about Microsoft technologies for twenty years. Maybe you’ve seen him speak or read his columns on Visual Studio Magazine and Redmond Developer News. Maybe you’ve heard of his new Microsoft consulting firm, Blue Badge Insights. Maybe, like me, you follow his personal blog. I started reading Brust Blog in the beginning of the year, and it quickly became my de facto source for objective analysis of Microsoft developments. A few comments led to an email exchange that, a few months later, lead to what has got to be one of the most cogent and comprehensive summaries of an advanced technological product out there. Continue reading Andrew Brust on OfficeWriter: “Enhanced Integration for Microsoft Business Intelligence” →

Excel, OfficeWriter, SharePoint

How to Create a Renewal Forecast Report with CRM, ExcelWriter and SharePoint

After finishing off our ASP.NET, SQL, and ExcelWriter training, the Technical Services Interns were given a project to create a proof of concept involving the dynamic generation of internal reports. We would use our new SQL knowledge to pull data from a customer relations database (Microsoft CRM), our ExcelWriter knowledge to create a rich Excel report from that data, and our ASP.NET skills to create a Web Form whereby users could constrain the reports they received. The reports would then be ported into SharePoint webparts. The reports were split into three categories:

  • Renewal Forecast
  • Case Distribution
  • Customer Activity

This post addresses the first of these reports. The Renewal Forecast report gives a company’s Sales and Support departments insight into customer contracts coming up for renewal.

Creating the Renewal Forecast Report

The purpose of this report is to provide a detailed overview of customers’ support contracts that are nearing expiration so employees can better manage the renewal process. The front-end in SharePoint is customizable so the report is generated completely dynamically and populated using queries from CRM. It is all ASP.NET code and uses a very sharp looking jQuery datepicker. Of course, the report generation is handled nicely using an ExcelWriter template. Not a current ExcelWriter user? No problem: you can download a free evaluation and follow along! Continue reading How to Create a Renewal Forecast Report with CRM, ExcelWriter and SharePoint →

Knowledge Base, WordWriter

How to get a list of bookmarks from a Word document

Problem

You want to get a list of all the bookmarks in a document using WordWriter.

Solution

WordTemplate

The WordTemplate.Boomarks will return the names of all the bookmarks in a WordWriter template. This can be useful for binding data to the template with SetRepeatBlock, which requires the name of the bookmark for the repeatblock.

WordApplication

Use the GetElements method to retrieve all the elements of a particular type. In this case, the Element.Type is “Bookmark”.

For example, to retreive an array of all the bookmarks in a document:

Element[] eArray = document.GetElements(Element.Type.Bookmark);
//Get a handle on the first bookmark in the document
Bookmark bookmrk = (Bookmark)eArray[0];
If you need to retrieve a particular bookmark, use Document.GetBookmark(string), which returns the Bookmark object with the specified name.

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Unexpected behavior of the CONTINUE modifier in version 7.1 and below

Problem

Using the CONTINUE modifier in a template to generate a report may show an unexpected behavior. Breaking down the different scenarios, we find that the behavior changes with data source type and amount of data available.

As described in our documentation, the Continue modifier enables data from a single data source to span multiple worksheets using the ExcelTemplate object. Without the modifier, if the data source for the ExcelTemplate uses a forward-only cursor, such as with a DataReader, the second worksheet with the same data marker will automatically start from the next row of data. If the data source is scrollable, however, the second worksheet with the same data marker will rewind to the first row of data. The Continue modifier tells the ExcelTemplate object to start from the next row even if the data source is scrollable. More about data markers and modifiers here.

In version 7.1 and below, the above description is accurate if there is enough data to reach the last data marker with a continue modifier before the data source has exhausted. However, if the data source gets exhausted prior to that point, the ExcelTemplate object will try to rewind the data source which may result in an exception thrown (for forward-only data sources) or duplication of data (for scrollable data sources).

Here are the two specific cases:

  1. When using a forward-only data source, the ExcelTemplate object will throw a runtime exception on the Process method: “Exhausted Data Markers” because it has encountered a new data marker but there is no more data available.
  2. When using a scrollable data source, the ExcelTemplate object will rewind the data source and will result in duplicated data on both worksheets.

Solution

Option 1: Upgrade to ExcelWriter 7.5 or later (recommended)

This issue was fixed in version 7.5 by applying more reasonable behaviors to the edge cases. With the new behavior, if the data has exhausted before reaching all data markers with a CONTINUE modifier, ExcelTemplate will not attempt to rewind the data source and will simply remove the extra data markers (similar to the behavior of the “optional” modifier).

Here is a table comparing the old and new behaviors in all scenarios:

No modifiers (CONTINUE) (OPTIONAL) (CONTINUE,OPTIONAL)
Old New Old New Old New Old New
Forward-only data source that still has data Continue Continue Continue Continue Continue Continue Continue Continue
Forward-only data source with exhausted data Exception Exception Exception Empty Exception Empty Exception Empty
Scrollable data source with that still has data Rewind Rewind Continue Continue Rewind Rewind Continue Continue
Scrollable data source with exhausted data Rewind Rewind Rewind Empty Rewind Rewind Rewind Empty

Option 2: Divide the data

If the template file cannot be modified at runtime, divide the data according to the number of sheets containing data markers with the CONTINUE modifier. This can be done by adjusting the MaxRows setting so there will be data available to the last of these data markers.

Option 3: Use ExcelApplication

Alternatively, if we have the ExcelApplication object available (included in OfficeWriter Enterprise Edition) we can modify the template to contain the correct number of sheets in the workbook. This way we can keep MaxRows a constant. It is possible to pass a template document between the application object and the template object in memory.

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Error: General license key exception: No valid license key found

Problem

When trying to create an instance of the ExcelApplication or ExcelTemplate objects, the following error is returned:

General license key exception: No valid license key found.

This can happen if you are trying to use an OfficeWriter dll that is not the same version as the license key in the registry.

A common scenario where this occurs, is upgrading to a new version of OfficeWriter. When the old version of OfficeWriter is uninstalled and a new version is installed, the dlls in your projects are not automatically updated. This means that your projects may have references to the old dlls (say v4), but the license key in the registry is for the new version (v8).

Update April 4, 2013: If you are running a 32-bit application with OfficeWriter on a 64-bit operating system, you may encounter this error message, even though the license key is in the registry and matches the version of OfficeWriter being used. If you experience this issue, please contact us.

Solution

There are 2 options:

  1. Update the references in your projects to point to the new OfficeWriter dlls. (Dlls can be found under Program Files\SoftArtisans\OfficeWriter\bin).
  2. Add the license key for the old version with the License Key Manager to run both versions of OfficeWriter. For more information about running different versions of OfficeWriter side-by-side, refer to this post.

To add a license key with the License Key Manager:

  1. Locate LicenseManager.exe either from Start > Programs > SoftArtisans > OfficeWriter or from Program Files\SoftArtisans\OfficeWriter.
  2. If you are installing on Windows 7 or Windows Server 2008, right click LicenseManager.exe and select ‘Run as Administrator’. Otherwise, double-click to run the installer.
  3. Click the Add/Update Key button to add the license key.
  4. After the license key is added, you should see license keys for the old and new version registered.

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, WordWriter

Install Error: Unable to save file: C:\Windows\Download installations{guid}\OfficeWriter 4.x.msi

Problem

You recieve an error message when running the OfficeWriter installer on Windows 2008 R2 or Windows 7:

Unable to save file: C:\Windows\Download Installations{guid}\OfficeWriter 4.1.0.msi. The system cannot find the path specified.

This error message may vary slightly depending on your system.

Solution

The installer must be run as an administrator. However, being logged in as an administrator does not cause the installer to be run with administrator privileges. You must explictly run it as an administrator.

To run the installer as an administrator, right click the installer and select “Run as Administrator.”

You can also run the installer as the administrator through the Windows Power Shell:

  1. Run Windows Power Shell as an administrator
  2. Within Windows Power Shell, navigate to the directory containing the OfficeWriter installer.
  3. Use the command .\OfficeWriter-Installer-4.1.0.exe. Note that the name of the installer file used in the command will vary depending on the version of OfficeWriter you are installing.

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Install Error 1316 – A network error occurred when attempting to read from the file

Problem

When running the OfficeWriter installer to upgrade to OfficeWriter 4.x or below from an earlier version of OfficeWriter the following error may occur:

Error 1316 – A network error occurred when attempting to read from the file C:\WINNT\Downloaded Installations{EBF98E9A-3A6C-4B10-AB6B-3D9A746A5C35}\SoftArtisans OfficeWriter [version number].msi

Solution

It is not possible to install OfficeWriter 4.x or earlier over a previous version with the automatic installer. The previous version must be uninstalled first. This is a known issue with InstallShield, the component used in the OfficeWriter installer.

Note: This only applies to installers for OfficeWriter 4.x and below. Starting in OfficeWriter 8.0, the installer no longer uses InstallShield so the issue no longer applies. However, you should uninstall the previous version of OfficeWriter before installing OfficeWriter 8.x to ensure clean upgrades.

Instructions for removing older versions of OfficeWriter can be found in our documentation:

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Install Error: 1603 Fatal error during installation

Problem

Installing OfficeWriter 3.9.1 may fail on Windows Vista or Windows Server 2008. The installer appears to proceed normally, but eventually rolls back and displays the following error message:

Error: -1603 Fatal error during installation. Consult Windows Installer Help (Msi.chm) or MSDN for more information.

The installation failure is due to a problem in InstallShield, a software tool used to build the OfficeWriter installer. The problem occurs in an InstallShield method that interfaces with IIS 7, which comes with Windows Vista and Windows Server 2008. OfficeWriter installer for versions 4.6.0 and earlier attempt to interface with IIS to create a new website for sample applications during installation. Because of the InstallShield problem on Windows Vista and Windows Server 2008, the installer fails and subsequently rolls back.

Solution

Option 1: Upgrade to OfficeWriter 3.9.2 or later

Upgrade to a newer version of OfficeWriter. Subsequent to the 3.9.1 release, the OfficeWriter installer is built with a patched version of InstallShield which fixes the problem described above. We recommend that, whenever possible, customers upgrade to the newest release of OfficeWriter to take advantage of the latest bug fixes and new features.

Option 2: Install OfficeWriter 3.9.1 without the samples

Install OfficeWriter 3.9.1 without the samples. Because the error occurs when the installer attempts to create a new website for the samples, not installing the samples would allow the installer to succeed. On the Setup Wizard dialog box which allows selection of the features to install, deselect the “OfficeWriter Samples” option.