Running Excel under SQL Agent task

I have a long time running small VB6 application as SQL Agent task, which creates an Excel spreadsheet, saves it on server and sends to mail recipient. Recently my customer has upgraded both Windows Server and SQL Server to 2008 R2, and application stopped working. I found that WorkSheet.SaveAs method keep failing and producing Error 1004 (Application-defined or object-defined error), which looks like Excel has no permission to access file system. I checked user account, used to run this step, he has enough rights to destination folder, and when I run this app under that user - it works ok. Only under SQL Agent it fails! It took me 3-4 hours to google this problem and found this one - The SSIS and Excel Story Continues! I run "MMC comexp.msc", go to the properties of Microsoft Excel Application, under Identity, changed it to The Interactive User from The Launching User (which is set by default). After making this change and restart SQL Agent my application starts working. But only for a while…

I found that sometimes it works, sometimes produces same error, and sometimes can't even launch Excel. Fortunately, in the same blog I found another post on this issue Error ‘Microsoft Office Excel cannot access the file’ while accessing Microsoft Office 11.0 Object Library from SSIS, which helped me to fix this issue. You only have to create one folder:

For Windows 2008 Server x64: C:\Windows\SysWOW64\config\systemprofile\Desktop

For Windows 2008 Server x86: C:\Windows\System32\config\systemprofile\Desktop

So, looks like a configuration problem of Server 2008 R2 and Office 11, people talking about it since last year, Microsoft really could fix it for that time…