All posts by Alison

Full time OfficeWriter Product Owner, part-time pivot table enthusiast.
ExcelWriter, Knowledge Base, OfficeWriter

Trendline Label doesn’t show Equation/R-squared value

Problem

It is possible, in Excel, to add a label to a trendline of a chart that shows the Equation or the R-squared value of the trendline like in the image below:

Trendlines can also be created as objects in ExcelApplication through the Series of a Chart. The Trendline object has the properties to ShowEquation and ShowRSquaredValue.

Setting these properties to true does not seem to have any effect and the label is not shown. However, if you open, with ExcelApplication, an existing workbook which already has a Trendline label these properties will work properly. From that point on you can programmatically change the label as you like using the properties.

Solution

This issue has been resolved for charts in the OOXML file format (XLSX, XLSM) as part of implementing OOXML support for ExcelApplication in OfficeWriter v8.0.

This issue persists for the BIFF (XLS) file format. It can be worked around in the following manner:

1. Create a new chart in Excel and add a trendline.

2. Add the Equation or the R-Square: Select the trendline then right-click the trendline or choose Format>Selected Trendline from the menu.

3. Now simply open the workbook with ExcelApplication.Open, retrieve the trendline and program it any way you like (Including changing the trendline regression type or value or show/hide the Equation or R-Squared value):

Trendline trend = oSeries.Trendlines[0]; //now this will work trend.ShowEquation = true;

ExcelWriter, Knowledge Base, OfficeWriter

Error: Cannot open PivotTable source file ‘[filename[x].xls]SourceData’

Problem

When opening an Excel file containing a pivot table that was streamed to an IE client, Excel will throw the following error message:

Cannot open PivotTable source file ‘[filename[1].xls]SourceData’

This is caused by IE caching the streamed file with a temporary name containing brackets (e.g. filename[1].xlsx). Since Excel does not allow pivot table data sources to contain brackets, when the cached file is opened, the pivot tables try to refresh using the temporary file name.

Solution

There are several ways to work around this:

  • Use a non-IE browser, such as Firefox or Chrome
  • Use IE 9 or later – this appears to have been resolved in the newer versions of IE
  • Save the file to disk before opening it to avoid opening the file with a temporary name
  • Stream the file inline instead of an attachment. This will open the file in IE’s Excel plugin instead of downloading it. If you are using IE 7 or later, you will need to configure the registry on the client to use this option.
  • Add Excel macros to the report to dynamically rename the PivotTable to exclude the bracket and then refresh the PivotTable. An example of these macros is available in this blog post.

Note: This is not an issue with the OfficeWriter product. This behavior occurs anytime someone attempts to download and open an Excel workbook with a PivotTable directly from certain versions of IE.

We have made these solutions available online because our customers have found the information helpful. However, these solutions are not covered under OfficeWriter product support. If you experience issues implementing any of these solutions, we encourage you to reach out to the appropriate vendor (i.e. Mozilla for Firefox, Google for Chrome, and Microsoft for IE 9 (or later) or to configure the in-line plugin).

In particular, if you encounter difficulties with the macro solution, feel free to leave a reply on the blog post so that the author of the post can address your concerns.

Knowledge Base, OfficeWriter, WordWriter

Error: WordWriter.dll was loaded by the DLLRegisterServer entry point was not found

Problem

When attempting to register WordWriter.dll with regsvr32, the following error is thrown:

C:\Program Files\SoftArtisans\WordWriter\WordWriter.dll was loaded, but the DLLRegisterServer entry point was not found. DLLRegisterServer may not be exported, or a corrupt version of C:\Program Files\SoftArtisans\WordWriter\WordWriter.dll may be in memory. Consider using PView to detect and remove it.”

Solution

Regsvr32 can be used to register COM objects, but not .NET ones. Because WordWriter is a pure .NET assembly, it does not need to be registered. To be used in a .NET application, WordWriter.dll can be placed in either the Global Assembly Cache (GAC) or in the application’s bin directory.

WordWriter can be used from standard ASP by registering its COM-callable wrapper, WordWriterCOM.dll. The COM wrapper is registered with regsvr32. For more information on using WordWriter in ASP, see the WordWriter documentation on Using WordWriter in ASP.

ExcelWriter, Knowledge Base, OfficeWriter

FitToPagesTall and FitToPagesWide settings do not take effect

Problem

FitToPagesTall and FitToPagesWide settings do not take effect unless Zoom is explicitly set to false.

Solution

FitToPagesTall, FitToPagesWide and Zoom are properties of the PageSetup object of the ExcelWriter Application object.

The Page Setup dialog in Microsoft Excel allows you to select one of the following two options:

  1. Adjusting the worksheet so that it fits to the dimension of “x” number of pages width-wise and/or length-wise.
  2. Adjusting the worksheet so that it prints to a certain percentage of the original size, ranging between 10 and 400 percent.

The equivalent properties in ExcelWriter are:

The Zoom property has a default value of “True” and “100” percent. This means that even if the Zoom is not explicitly set in your code, these values will be assumed. Any values provided for FitToPagesTall or FitToPagesWide will be ignored. If you want to use these properties, you must explicitly change the value of Zoom to “False”:

worksheet.PageSetup.Zoom = false; worksheet.PageSetup.FitToPagesTall = 2; worksheet.PageSetup.FitToPagesWide = 2;

Knowledge Base, OfficeWriter

Install Error: System appears to have an unrecognized version of J#

Problem

The following error is returned while attempting to install OfficeWriter 3.9 or below:

The System appears to have .NET 2.0 and an unrecognized version of J#. Please install J# 2.0 if you wish to use ExcelWriter .NET or WordWriter.

Starting with version 4, OfficeWriter has been written completely in C# and no longer requires the J# runtime. However, customers installing version 3.x of OfficeWriter do need the J# runtime. The version of J# should match the installed version of the .NET framework.

Solution

Install the J# redistributable that matches the .NET framework that you are using in your application.

Note: Microsoft has not released a J# redistributable that matches .NET 4.0 framework. To use OfficeWriter with .NET 4.0, you will need to upgrade to OfficeWriter 4 or later.

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Files generated by OfficeWriter do not open in the browser

Problem

When OpenInBrowser in the Save method is set to true, the Excel spreadsheet or Word document does not open in the Internet Explorer Browser window as expected. Instead, the spreadsheet opens in Excel or the document opens in Word.

Solution

Using a browser other than Internet Explorer

The plugin that is used to display Office files in the browser is only available in Internet Explorer, so other browsers cannot display Excel or Word files within the browser. However, you can set whether you want to always open the files in Excel or Word, or save them to disk by default.

Firefox:

When a user is sent an Excel or Word file, a dialog box prompting to Open or Save the file pops up. The user can choose an option and then check the “Do this automatically for files like this from now on” box to have it remember this choice.

Chrome:

Chrome defaults to saving the file to disk. If you want to open a file type automatically in Excel or Word, download a file of that type. Once the file is downloaded, right-click the file in the download bar at the bottom of the browser. Select “Always open files of this type”.

Using Internet Explorer

It is likely that the “Browse in Same Window” is disabled for the file types (XLS, XLSX, XLSM, DOC, DOCX, DOCM).

Previously, this option was available under Folder Options > File Types > Select a file type > Check Browse in the same Window.

Starting in Windows Vista, this option is no longer exposed and the registry needs to be modified to enable certain file types to open in the browser. This Microsoft Knowledge Base article explains how to enable the behavior by modifying the registry: A new window opens when you try to view a 2007 Microsoft Office program document in Windows Internet Explorer 7 or Internet Explorer 8.

Excel, ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Excel or Word file generated by OfficeWriter opens in protected view (yellow)

Problem

A report generated using ExcelWriter or WordWriter opens and there is a yellow warning banner saying that the file has been opened in Protected View.

Solution

Starting in Office 2010, Microsoft introduced a security feature that scans files before opening them in Excel or Word. If there is something wrong with the file format definition, it will open in Protected Mode with a red warning banner.

One of the common scenarios for opening a file in Protected View is opening the file from an unsafe location, such as the Temporary Internet Files folder. This means that if the file is streamed to an end-user, it will open in protected view.

For more information about Protected View settings in Office 2010, please refer to this Microsoft TechNet article on how to configure Protected View settings in Office 2010.

SQL Server

Unexpected Issues with the New Office 2007 File Format MIME types

One of the differences between the new OOXML file formats (e.g. .xlsx, .xlsm, .docx, .docm) that were released with Office 2007 and the older binary file formats (eg. .xls, .doc) is that each OOXML file extension has a unique Multipurpose Internet Mail Extension (MIME) type. This is a departure from the MIME types for the binary file formats, where the same MIME type could apply to several file extensions.

For example, the binary “application/vnd.ms-excel” MIME type applies to the .xls, .xlt, and .xla file extensions, but OOXML .xlsx and .xltx have separate MIME types: “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet” and “application/vnd.openxmlformats-officedocument.spreadsheetml.template,” respectively.

An unexpected issue

The change in MIME types can cause unexpected issues to occur. I recently helped a customer who was experiencing strange behavior in his upload application. In his application, he was using FileUp to upload files to a SQL server database, in addition to some information about the files. When the customer tried to upload an XLS file, the upload completed successfully, but if he tried to upload an XLSX file, he started getting error messages. Continue reading Unexpected Issues with the New Office 2007 File Format MIME types →

Excel

Macro Security Settings in Excel 2007 and 2010

What is the problem?

A customer was exporting Excel reports by OfficeWriter to the XLSM format and was alarmed when he opened his output and saw a security warning:

Some active content may have been disabled:

 

 

Obviously, anyone who hadn’t experienced the Excel Trust Center Security Warning before could easily have been alarmed by this.

What is going on?

The security prompts are caused by the settings in Excel’s Trust Center, which was introduced in Excel 2007. Basically, when a workbook is opened in Excel 2007 or 2010, it is automatically run through the Trust Center feature for security reasons. Excel’s behavior for handling workbooks with macros will change depending on the settings selected in the Trust Center.

Some notes about Trust Center security warning messages
  • Some active content may have been disabled indicates that there are multiple types of active content that have been disabled, not just macros. If the workbook contains just macros, the security warning will read Macros have been disabled.
  • In Excel 2010, the security messages are the same, but the coloring makes the warning more obvious. For more information about the difference in the security warning between Excel 2007 and Excel 2010, check out this article.

To get to the Trust Center: Office button > Excel Options > Trust Center > Trust Center Settings. And on my left, Macro settings:

 

For example, say the ‘Disable all macros with notification’ option is selected. This is the default macro security level for Excel 2007/2010 and this setting will prompt the user to enable macros anytime the workbook is opened. The end-user can change the security setting to ‘Enable all macros’, and then macros will run without the prompting.

For more general information about Macro security settings, please refer to this Microsoft article: http://office.microsoft.com/en-us/excel-help/change-macro-security-settings-in-excel-HP010096919.aspx

I am developing a workbook with macros. How can I avoid having the Trust Center prompt my end-users?

A note about macro security settings
Enabling macros to run without prompts could be potentially dangerous if a workbook contains malicious macros. The security prompt is designed to prevent macros from running unless the user decides they are safe to run. Each of the following suggestions will allow macros to run without prompting, which may harm your computer if the macros are malicious

Since the Trust Center’s settings are determined on a per-user basis, depending on which security settings the user has selected, the behavior the end-user experiences may vary. Here are some ideas for avoiding the Trust Center security prompt:

Digitally sign the macros (recommended)

One of the macro settings is to ‘Disable all macros except digitally signed macros’. This means that if the macros are signed by a Trusted Publisher, Excel will run the macros without any security prompts. All unsigned macros will still prompt the user to enable them, but they will not be prompted for workbooks that contained macros signed by the Trusted Publisher.

This Microsoft article explains how to digitally sign a macro project: http://office.microsoft.com/en-us/excel-help/digitally-sign-a-macro-project-HA001231781.aspx

After you have signed the project, the end-user will need to add you as a Trusted Publisher. This is explained below.

The first time the user opens a workbook with signed macros, they will be given the following options:

  1. Help protect me from unknown content (which is to leave the macro disabled)
  2. Enable this content (enable for one-time use)
  3. Trust all documents from this publisher (enable all macros from this publisher).

Instruct the end-user to select Trust all documents from this publisher, which will add the publisher who signed the macros, you, as a Trusted Publisher. All subsequent workbooks with macros signed by you will not display security prompts when opened on the end-user’s machine.

For more information about Trusted Publishers, please refer to this Microsoft article: http://office.microsoft.com/en-us/excel-help/add-remove-or-view-a-trusted-publisher-HA010034138.aspx?CTT=5&origin=HA010167227

Use a trusted location (not recommended in some cases)

If a workbook is opened from a trusted location, it does not go through the Trust Center. This means that regardless of the Trust Center settings, Excel will run the macros in the workbook without any security prompts. Trusted locations should always be secure and trustworthy.

If your end users are opening the workbooks directly from the browser cache (e.g. the workbook is streamed to the client from a web application), DO NOT tell them add their browser cache as a trusted location. The browser cache is an easy target for hackers who can force the browser to download potentially comprimised workbooks. This is not to say that trusted locations are never recommended. For instance, your end-users could specifically designate another location as trusted, and copy any files to that location before opening them, which is the secure way to use trusted locations to avoid the security prompts.

Here is a Microsoft article that discusses trusted locations: http://office.microsoft.com/en-us/excel-help/create-remove-or-change-a-trusted-location-for-your-files-HA010031999.aspx?CTT=5&origin=HA010167227

Enable all macros (not recommended)

This option runs all macros without prompting. This is NEVER recommended because it potentially exposes the end-user to malicious macros, even if your macros are safe.

Macro Settings in Excel 2003

If you are using Excel 2003, the macro settings will be different. Excel Hints has a good overview of those settings here: http://excelhints.com/2010/08/06/adjust-the-security-level-in-excel/.

OfficeWriter

Installation Error 1327

What’s the problem?

I was working with a customer who was faced with the following error message during her installation of OfficeWriter: Error 1327: Invalid Drive: H:

When she looked in ‘My Computer’, there was no H drive on hard disk or mapped to a network location. She was baffled as to why the installation was trying to point to an non-existent drive.

What’s going on?

Error 1327 occurs during any basic installation when there is an incorrect value in the Data field in some of the Microsoft Windows registry keys. This issue is addressed by Adobe in this article and by Robert LaThanh in this post.

How to resolve the issue:
  1. Open regedit and navigate to each of the following registry keys:
    HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders
    HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders
  2. Check to see if any of data values point to a drive that is not mapped to in your computer (either on hard disk or a network location). In particular, look to see if any of the data values point to the drive that the 1327 error mentioned.
    In the case of the customer, one of the data values in HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders was set to H:\Favorites.
  3. If there are any incorrectly mapped data values, modify the values.
    The customer changed the value from H:\Favorites to %USERPROFILE%\Favorites and that resolved the Error 1327 issue.

Here is a sample of what the particular corrected registry key should look like:

 

WARNING: When modifying the registry, make sure you back it up!

Since the solution involved modifying registry values, it was important to make a back up of the registry, so I sent her the Windows 7 instructions for backing up the registry. Microsoft also has articles for backing up Windows XP and Windows Vista.