XP/2003 Deadline Looms

If you are still on Windows XP and Office 2003, if you have not already started your migration, you should start ASAP. The end of support for BOTH is this April. Here is a great link that explains all the reasons you should start today: http://www.microsoft.com/en-us/windows/enterprise/endofsupport.aspx.

I have heard from a number of folks that are “stuck” in XP/2003 land. Namely, because they have a large number of Office based solutions, Excel VBA add-ins, XLL’s, UDF’s, macros and Access 2003 databases that must be migrated and no idea how to begin to remediate them. There is help out there and a number of partners and even service offerings from Microsoft that can help you.

If you have a solution in Office 2003 that you need help to remediate, please contact me. Send me a private tweet or send me an InMail on LinkedIn.

VSTO and COM/OLE…

If you have been doing much work in VSTO and especially around Excel and embeddings, you may have been bit by this bug. Does this error look familiar:

clip_image002

“The program used to create this object is Excel. That program is either not installed on your computer or it is not responding. To edit this object, install Excel or ensure that any dialog boxes in Excel are closed.”

This error can be caused by the following:

  • A .NET3.5 Add-in or a .NET 4.0 Add-in
  • You have attached to the WorkbookOpen and/or WorkbookActivate events.
  • You are trying to edit/open/double-click on an embedded Excel instance inside a Word or PowerPoint document.

There have been several reports of this problem on the Microsoft MSDN site:

There are also a number of KB articles that document the problem and attribute it to specific programs:

The problem is specifically documented here:

Simple Solution

The basic simple answer is to place a Marshal.ComReleaseObject(Wb) at the end (or better, in the Finally block) of your event handlers. This will properly allow Word and excel to handle the OLE communication by not having VSTO hang on to an instance handle of the workbook, therefore causing the error.

And this is not carte blanche to start placing ComReleaseObject() all over your code. I have found VERY VERY few limited cases where using ComReleaseObject() in an add-in necessary. And this is one of them. Hot smile

Orphan Issue

It is not a panacea, either. Confused smile While it resolves the issues of OLE initialization and allows you to edit your Excel embedding in Word (as one example), it does not prevent one other scenario that I like to call “Orphaned Excel.” In this scenario, you edit your embedded Excel instance in another Window (usually via a right-click / Ole Object / Open). If you leave Excel open, return to Word and close the document, Excel should close. But in the VSTO COM/OLE scenario it may not – it will remain open with the embedded workbook still editable. However, it is orphaned an no longer associated with its container. Any edits will be lost.

That is where a solution I created for Excel/Word OLE interaction comes in here:

This add-in is very well commented and explains the following:

  • When a workbook is opened, it looks to see if it is embedded.
  • If it is, it connects to the running instance of Word, and gets a reference to the parent document.
  • A timer in the add-in will then continually check the status of the document
  • If the parent document is still opened, nothing happens. However, if Word is existed or the parent document is closed, the child embedding is forced closed.

IMPORTANT NOTE

However, and this is important note. For everything you do, you are in the sandbox with other kids. Sad smile Your add-in is loading in the same AppDomain as everybody else. If there is another VSTO 3.5/4.0 Add-in loaded in Excel and that add-in is not doing any of the above – well, your still going to have problems.

That is what makes this issue so vexing. Steaming mad You can play by all the rules, but you cannot prevent other kids from throwing sand. This is why I see some customers going to the extreme to manage out (disable) all other COM add-ins when they load their solution. But this does not work for HKEY_LOCAL_MACHINE Loaded add-ins.

There are few options and most involve a lot of code. I say, TEST. If your are in an Enterprise environment, test all your VSTO add-ins together, find code owners and get everyone on the same page. If it is a vendor add-in causing the problem, point them here. Smile

Script to Migrate Normal.dot

When you deploy Office 2010 over an existing install of Office 2003, one thing that will happen is that user settings in Microsoft Word (auto-text, styles, macros,etc.) will not translate forward. This is because as part of the migration process, the Normal.dot for Office 2003 is renamed to Normal11.dot. The following article details this:

The article outlines a set of steps to manually replace the original Normal.dot. However, it does not provide a means to perform this step during setup/deployment or as a script to be deployed as part of a post-install or login.

The following is a JavaScript sample that performs the steps outlined in the KB article above:

shell = new ActiveXObject('WScript.Shell'); 
userPath = shell.ExpandEnvironmentStrings('%UserProfile%');
var path = userPath + "\\AppData\\Roaming\\Microsoft\\Templates\\";
var fs = new ActiveXObject("
Scripting.FileSystemObject");
var newNormal = fs.GetFile(path + "
normal.dotm");
newNormal.Move(path + "
new_normal.dotm");
var oldNormal = fs.GetFile(path + "
normal11.dot");
oldNormal.Move(path + "
normal.dot");

Outlook Calendar Cleaner

I encountered an odd problem while working with a customer that was porting from Lotus Notes to Office 365 (Exchange) and ended up creating a new tool called the Outlook Calendar Cleaner.

The customer had a mixed environment of Mac and Windows and in certain conditions appointment items were disappearing on the Mac Office Outlook calendar, but still appearing in Outlook Web Access (OWA) and if the delegate was in Windows, they would still see the appointment on their view of the owners calendar.

The issue turns out to be that in the conversion a multi-line subject in an appointment item (supported in Lotus, but not in Exchange), has the new line character converted to a start of text (SOT) character or ASCII code 0x02. The problem is that this is an invalid character in XML and the Exchange Web Services implementation on Mac O/S X does not properly parse this character. This causes the process to hang and all appointments which were being imported on that thread are not copied over. Net effect – they appear to be missing in Mac Outlook.

So, I created a tool to correct the problem. The Outlook Calendar Cleaner is a very specific tool targeting this problem. You can to open the users Exchange Account (Office 365 account) in Outlook 2010 for Windows and run the tool in Windows and it will clean any appointments found to have this special character.

I have posted the Outlook Calendar Cleaner tool on Codeplex (along with the source code). Here:

https://outlookcalendarclean.codeplex.com/

Excel File Cleaner

Last year I worked with a major fortune 500 company on an issue they were having with their Excel files increasing in size by major proportions, slow load times, and cell formatting errors. Each of the problems are documented by well known issues in the Microsoft Knowledge Base:

The company had problems with the workarounds listed in the articles. Those tools/options did not work well because they targeted files created in Excel 2003 and did not take into account the extended cell range capabilities in Excel 2007/2010. As such I created a new tool to help combat this issue. They have since deployed this tool throughout their organization to great success.

Just recently, in an effort to increase the adoption of this tool, I placed it on CodePlex (source code and all). The tool uses the OpenXml toolkit to correct files that exhibit issues (file size, slowness and cell format errors). You can access it here:

Excel File Cleaner

Outlook Calendaring Issues with BPOS (Office 365)

The other day I got a Microsoft BPOS/Outlook issue (NOTE: BPOS is now called Office 365). Certain, heavy calendar users were experiencing a lot of issues. Here were the primary items:

  • Lag when switching between Delegated calendars/ inboxes etc.
  • Appointments not updating correctly.
  • Message interface error when switching between calendars
  • New event on calendar, no details shown
  • Duplicate entries on Calendar
  • Unable to determine who’s calendar you are viewing when using “calendar in Mail” view. The name was not listed on the tab.

Most of the problems occur because Outlook is not configured to work over a slower than usual connection. BPOS uses a login client that then connects to the “cloud” servers run by Microsoft. When running Exchange server on premises, you can rely on your 100MB network connection and not worry about bottlenecks occurring for certain heavy network operations. However, when using the advanced calendaring features of Outlook, it can put a strain on your ISP connection (upload/download is usually far less than 100MB). The core of the solution is outlined in this support article:

http://support.microsoft.com/kb/955572.

What you need to do is turn on the option to Cache Others Mail. This is not enabled by default and you should NOT set this for all users, JUST your heavy shared calendar users (like Administrative Assistants):

  1. Exit Outlook 2007.
  2. Start Registry Editor: Click Start, click Run, type regedit in the Open box, and then click OK.
  3. Locate and then click to select the following registry key:  HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Cached Mode, or you can use: HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Outlook\Cached Mode
  4. Right-click CacheOthersMail, and then click Modify.
  5. Type 1 in the Value data box, and then click OK.
  6. On the File menu, click Exit to exit Registry Editor.
  7. Next, disable headers. To do this, follow these steps:
  8. On the File menu, and then click Cached Exchange Mode.
  9. Click to clear the following options:
    • Download Headers and then Full Item
    • Download Headers
    • On Slow Connections Download Only Headers

Additionally, the following article is a set of best practices for managing calendars. Some of the issues, like duplicates or cancellations not being accepted are likely due to appointments being modified, changed or the original e-mail request being moved, etc. The following article is an excellent resource to make sure appointments are handled properly to avoid issues:

http://support.microsoft.com/kb/899704

This article describes the following scenarios in which Calendar items may be removed from the Calendar:

  • Multiple users receive meeting requests for a mailbox owner.
  • You delete a meeting request on one computer after you accept the same meeting request on another computer.
  • You cancel or delete a meeting without sending an update.
  • You frequently change recurring meetings.

This article also describes the following scenarios in which the Calendar items may become out of date:

  • You forward a meeting request.
  • You use Outlook Web Access to accept a meeting.
  • You do not click "Send Update" when you change a meeting that you organize.
  • You do not process a meeting request in the Inbox.

Finally, this article recommends the following best practices for working with meeting information:

  • Convert an existing appointment to a meeting request.
  • Do not forward meeting requests if you are not the meeting organizer.
  • Limit the number of delegates who have access to your Calendar.
  • Schedule end dates on recurring meetings.
  • Turn on Calendar logging for executives and for other frequent users.

Additional issues you may be having can be resolved by installing the latest set of patches called Cumulative Updates for Outlook and Office core (mso). The following website has a link to all of these patches:

Update Center for Microsoft Office, Office Servers, and Related Products

http://technet.microsoft.com/en-us/office/ee748587.aspx