Generating BizTalk binding files with Excel automation in a TFS Build

This is yet another of those things I really need to blog about so that I don’t ever forget this personally

Background

So the background is as follows:

• A large BizTalk 2010 implementation at a large private Swedish Enterprise customer.
• Visual Studio 2010 and TFS 2008
• Developers have already been working more or less 1 year before I arrive at the scene.
• My mission is to automate the whole build/release process.
• The devs have written an Excel macro that takes values from the Excel WorkSheet and together with a template-file for BizTalk bindings it generates all the different binding-files for the specified environments in the Excel Worksheet. I’ve seen a lot’s of different ways to do this before and this is yet another.
• The Excel macro is called from a vbs-file.
• The vbs-file is called from a powershell script, yes I see a lot of improvement here.

The challenge

So I started my journey today with the hopes of just hooking up the existing scripts for generating the BizTalk binding-files with a TFS build. But for some reason no files were generated but I could see that Excel started and so forth.

After I while I decided to leave the Excel macro thingee and rewrite the complete Excel macro in C#, and I did and it worked just fine.

I hooked up my little console app with my build and got the following message:

System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file ‘c:\temp\test.xls’. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

Finally! some error I could search for.

The solution

I found the solution here in Excel 2007 automation on top of a Windows Server 2008 x64 and it stipulates that you should add the following folder structure to the server where you’re running Excel automation:

• Windows 2008 Server x64
  Please create this folder: C:\Windows\SysWOW64\config\systemprofile\Desktop
• Windows 2008 Server x86
  Please create this folder: C:\Windows\System32\config\systemprofile\Desktop

It almost felt silly after some 4 hours of rewriting an Excel macro to C# to just add a the Desktop folder under C:\Windows\SysWOW64\config\systemprofile and the build just worked perfectly!

Warning read this official Microsoft kb about support for this here in Considerations for server-side Automation of Office.

Lessons learned

• VBA code can be very messy
• You should start to think about automated build processes from day one in a dev project.
• I never thought adding a folder could solve this

Cheers!

Hugo