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 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.

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

4 Replies to “Generating BizTalk binding files with Excel automation in a TFS Build”

  1. Thank you very much!!! Adding that folder helped me get past Workbook.Open!

    I was scratching my head for so many hours!

Leave a Reply to Jin Cancel reply

Your email address will not be published. Required fields are marked *

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.