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.

Email, RSS Follow
Email

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.

Email, RSS Follow
Email

Women In Tech

Stories from the WIT Trenches: Jes Shultz Borland

Photo Credit: Jes Schultz Borland

[This is the second in a series of posts exploring the personal stories of real women in technology. Back in April I wrote a bit about my own history and about the problems, systemic and idiosyncratic, plaguing women who chose  a career in most sectors of the tech world. Writing it was surprisingly cathartic, and the response to it was powerful enough to make me want to push it further. Every woman in tech overcame at the very least statistical odds to be here; this blog series aims to find out why, and what they found along the way. This week we have the dynamic and inspiring Jes Schultz Borland, whom many of you know from her incredibly active social presence in the SQL and WIT communities. In March, Jes described her professional journey,with all its dips and twists and catalysts, for SQL University WIT Week, and reading her story was part of what encouraged me to tell my own. If reading this does the same for you, please feel free to email me.]

Hi! My name is Jes Schultz Borland. I live in central WI. I’m a runner, an avid cook, a Jaycee, and a database administrator for a Fortune 500 company. I love what I do. I love keeping SQL servers running with minimal downtime, I love writing scripts to automate or fix processes, I love writing reports, and I love helping developers tune queries. I’m also very involved in the (amazing) SQL community. I love answering people’s questions, I love speaking at user groups and SQL Saturdays, I love being on the board for my user group, and I love learning. I tweet, I blog, and I speak regularly.

1)      Can you take us back to your “eureka!” moment—a particular instance or event that got you interested in technology? Continue reading Stories from the WIT Trenches: Jes Shultz Borland →

Email, RSS Follow
Email

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.
Email, RSS Follow
Email

OfficeWriter

Popular (Pitan Pivot Mage Remix)

I absolutely love Wicked (I’ve seen in on Broadway and in West End), so it’s really no surprise that when I was stuck in traffic last Friday, I decided to start singing “Popular” with pivot table lyrics…

Hear the original song here.

Whenever I see data that’s way out of line,
And let’s face it, it happens all the time!
It makes me want to cringe all up inside.

And when data needs a workover,
I say, “Come on and fork it over,
and leave
that mess by the wayside.”

I’ll guarantee a fix,
with my lovely bag of magic tricks.

Don’t worry, there’s no need to be blue!
Once we are through,
it will be true:
You. Will. Use….

Pivoting.
It’s all about pivoting.

You’ll make all the cool reports,
make your tables short,
see all the aggregate info.
So let’s start, by importing some data rows!

Everyone will think you have all the smarts,
if your reprots are filled with pivot charts.
Once I’ve finished with my magic arts
Your data will be clear
with your pivot tables near…

Because it’s pivoting.
I’ll help you do pivoting.

You may not agree right now
that pivot tables allow
you to make reports so rivetting

There’s nothing to prevent you you
from using awesome pivot-ing

La, la, la-la-la-la.

Pivot tables are the best,

And if you ever need help, call on me!

Just FYI Pitan is totally based on Galinda from Wicked

Email, RSS Follow
Email

Social Media

Social Media for Techies: Why Do I Need It?


[Image via Life Magazine]

Are there two more groan-inducing, not-you-again, put-on-your-earmuffs words than “social media?” If you say yes, then this article might be for you. If you say no, this article still might be for you. Because regardless of whom you are and where you stand, you need to have a social media presence, and this post aims to explain why.

Social media, as I’ve sure you’ve witnessed, is now a viable form of mass communication, and its interactive, abbreviated, real-time nature has influenced our other forms of communicating. I think it’s reasonably safe to say that we tend to think and speak in shorter, more easily digestible forms today than we did when Thomas Payne wrote Common Sense (@tompayne Monarchy is oppressive. We need our own govt w/ representation for all. Who’s ready to fight for it?). We’ve become a nation of skimmers and crux-extractors and sound-biters, and if you can’t produce readily skimmable, summary-box content, well, let’s hope you’re writing for The New Yorker. Continue reading Social Media for Techies: Why Do I Need It? →

Email, RSS Follow
Email

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.

Email, RSS Follow
Email

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.

Email, RSS Follow
Email

Excel, OfficeWriter

Pitan Pivot Mage: The Mystery of the ‘Replace Content’ Message

I was working with a formatted Excel report and I needed to add a pivot table. Easy-peasy, I added my lovely little pivot table with a pivot chart. Since I would be importing the data into the pivot table later, I made sure to place the pivot chart outside of the cells that the pivot table would occupy once it was populated with data (to avoid overlap). I saved my template and went to generate my report.

When I opened the output, I was greeted with a mysterious message from Excel:

 

I was baffled because I knew that nothing should overlap with the pivot table or pivot chart, even with after the data had been imported. Continue reading Pitan Pivot Mage: The Mystery of the ‘Replace Content’ Message →

Email, RSS Follow
Email

Knowledge Base, OfficeWriter, WordWriter

Word Error: Could not start converter mswrd632.wpc

Problem

When you try to save a Word 2007 template as part of a SQL Server Reporting Services RDL you get an error:

Word cannot start the converter mswrd632.wpc

Solution

This error is caused by a Windows security update published December 2009, affecting Windows XP, Windows 2000 and Windows Server 2003. You can resolve the issue by disabling the converter through the registry. Open regedit and delete the registry keys at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Text Converters\Import\MSWord6.wpc. Restart Word and the error should disappear.

You can find more information in this article. It also provies a script that will perform the registry fix for you. The additional steps mentioned in the update to that article have not been tested, as unregistering the converter has solved the problem in every instance in which we’ve encountered it.

Note that this issue only affects Word 2007 SSRS templates. Word 2003 templates are not affected. Also, Excel 2007 is not affected by this issue.

Email, RSS Follow
Email

Blogged