The blog has moved!
If you are interested in reading new posts, the new URL to bookmark is http://blog.valeriogheri.com/
Anyone who ever tried working with Office automation on Windows Server 2008 64bit knows that it is a nightmare to make it work as expected.
For a web application I’m currently working on, I need to access an Excel 2010 file, write some data in it, run a VBA macro and read the results out of it.
To make this workflow successfull, there are 3 problems to cope with:
1) create an Excel application
var excelApp = new Microsoft.Office.Interop.Excel.Application();
2) open and save files
3) run a VBA macro stored into the Excel file
Now, problems 1 and 2 have been successfully addressed in the past, see for example http://www.bloing.net/2011/01/how-to-make-iis7-play-nice-with-office-interop/
for a complete overview.
But I couldn’t find anything about problem number 3! My application was simply stuck when trying to run the macro and the only solution was to kill the Excel application to take back control of it. After two days spent troubleshooting, changing every possible configuration options, I’ve finally found the solution. Open IIS Manager, select the Application Pool used for the application you want to deploy and then click on “Advanced Settings”. Once the window opens, set the option “Load User Profile” to true, like shown in the image below.
That’s it, this was enough to make my application work as expected, being able to run the Excel VBA macro!
I hope this helps.