Error ‘Microsoft Office Excel cannot access the file’ while accessing Microsoft Office 11.0 Object Library from SSIS


Folks, yet another stumble with SSIS and Excel. This time I am using Microsoft Office 11.0 Object Library. The code runs fine on Windows Server 2003.

Below is the code sample.

=======================================================

using Microsoft.Office.Interop.Excel;

using Excel = Microsoft.Office.Interop.Excel;

using System.Runtime.InteropServices;

Excel.Application m_ExcelApplication = new Excel.Application();

m_ExcelApplication.Visible = false;

m_ExcelApplication.UserControl = false;

m_ExcelApplication.Application.ScreenUpdating = false;

m_ExcelApplication.DisplayAlerts = false;

string strFileName = @"d:\testing\testing.xls";

Excel.Workbook m_ExcelWorkBook = m_ExcelApplication.Workbooks.Open(strFileName, //FileName

2, //UpdateLinks

bReadOnly, //ReadOnly

Type.Missing, //Format

Type.Missing, //Password

Type.Missing, //WriteResPassword

true, //IgnoreReadOnlyRecommended

Type.Missing, //Origin

Type.Missing, //Delimiter

false, //Editable

Type.Missing, //Notify

Type.Missing, //Converter

Type.Missing, //AddToMru

Type.Missing, //Local

Type.Missing //CurruptLoad

);

=======================================================

When executing the Open function. I received the following exception:

Microsoft Office Excel cannot access the file ‘d:\testing\testing.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.

However, when I removed the directory information and just give it the file name: ‘testing.xls’ – I put testing.xls in to the same directory as the Windows Service- everything works fine, bizarre isn’t it.

Research shows that automation is an issue with Windows Server 2008, specifically the 64-bit version. The 32bit version works, as well as windows 2003 32/64bit. I’m unsure at this point as to whether it affects only SERVICES or APPLICATION automation as well.

I dug in further and figured out the solution which is pretty crazy and actually motivated me to blog this for thousands of you who may run into this hair-pulling problem with SSIS and Excel.

SOLUTION:

For Windows 2008 Server x64: Create the following directory:

C:\Windows\SysWOW64\config\systemprofile\Desktop

For Windows 2008 Server x86: Create the following directory:

C:\Windows\System32\config\systemprofile\Desktop

Thats It!! Voila!! You are all set to goโ€ฆ..

Author : Debarchan(MSFT) SQL Developer Engineer, Microsoft

Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead , Microsoft

Comments (87)

  1. Mark says:

    Uh…no…doesn't work for me.  I am on Win 2008 R2 64 bit but running on a Virtual Server don't know if that has anything to do with it or not.

  2. Debarchan says:

    Are you sure you created: C:WindowsSysWOW64configsystemprofileDesktop ?

  3. Alice says:

    The solution solved my issue with Excel.  Thanks so much!

  4. Terry says:

    YES!!!! this solves my problem. thank you so much

  5. Marlene says:

    Hi

    Now mine at least runs… but runs and runs as if hanging.  Can you tell why?

    Thanks!

    1. Deepika K says:

      Hi, how did you resolve your issue ? I’m facing thew same problem.

  6. DebarchanS says:

    Hi Marlene,

    Is the hang scenario from a SQL Server job? Can you run the package successfully from command prompt or BIDS?

    –DebS

  7. Mike says:

    Thanks a million, this fixed it for me.  Luckily I still have some hair left.

  8. Brandon says:

    This solution worked for us!  What an obscure problem/solution!

  9. Ankit says:

    It worked for me !! Thanks a Ton. I have no clue how its related but it does work

  10. meet2aftab@gmail.com says:

    Many Thanks… It solves my problem!!!

  11. Eric Fisher says:

    Dude!  You have saved me hundreds of hours of thinking it was my code or permissions…  I owe you a beer!

  12. Debarchan says:

    Hey Eric!

    Seriously? Tell me where are you located. I can go anywhere for a beer ๐Ÿ˜›

    ~D

  13. Gliese 581 g says:

    Thank you, it worked for me as well.

  14. James Field says:

    Thank you — I was pulling my hair out trying to tie a piece of excel automation into a Team City Build project (don't ask, its a long story).

    Anyhow, creating the directories has fixed a problem I was just about ready to give up on!

  15. Lisa says:

    This worked for me. As a previous commenter said, so obscure! Thank you.

  16. Jim Burfield says:

    Worked for me too – in my case for Windows 7 32-bit / Excel 2010.

    I just created C:WindowsSystem32configsystemprofileDesktop (I was only missing the Desktop folder), and that was the solution for nearly a full day's worth of hair pulling.

    Thanks so much!

  17. FG says:

    Thanks a ton for this solution! This is one for the textbooks. I had a powershell script that ran fine when executed manually but didn't work when run under task scheduler. I installed a new scheduler (nncron) and nothing changed. But the logging now gave a clue as to why the job was failing. The open call to Excel was failing. Made your change and the darn thing started working correctly. Wow. I hate Windows more than ever now, but thank the gods of everything holy there are folks like you around to battle against the evil empire in Redmond.

  18. Andrey says:

    your post helped me second time!

    i had this issue about year ago and successfully forgot solution…

    thanks!

  19. Amar says:

    Wow — I tried the above solution and it worked like charm ๐Ÿ™‚ We were fighting on this issue for almost 4 days and tried many stuff .

  20. Anand kumar singh says:

    i have created the folder but it didn't work for me

  21. Jeshur says:

    It worked for me! Thanks a ton!

    But now I am facing the same issue for Windows Server 2012 and the said fix ain't fixing it! Any other solution for WS 2012?

  22. j says:

    Voila nothing. I was able to correct it on a development machine using dcomcnfg.exe and dcomcnfg.exe /32 because of the inconsistancy of com apps and 32 office on a 64 bit machine. BUT I duplicated all the settings on another cloned machine and NOTHING – I'm MS certified and started my days in COM but this is terrible. Why is Excel still a COM object and not part of C#,Framework and safe….code.

  23. Arno Schoof, InsightCS says:

    Brilliant, I had the same problem and would never have found this solution myself…!  It works for me in Windows 2008 server reading Excel files through a Windows server.

  24. sunny says:

    Worked for me…nice post. Thanks for this.

  25. Vikas says:

    It works for me…really woth writing this one but needs more tagging here.

  26. Gomti says:

    This isn't a workable solution, what if I have to write to file on a network?

  27. M akins says:

    Wow. Thanks a lot! Saved me prob hours of garbage research.

  28. tej says:

    Now mine at least runs… but runs and runs as if hanging.  the job which is calling the conversion block is in running stateeven after convesion completes and put a lock on xlxs file.

    can you help why?

  29. jagjit singh says:

    This Solution solved my issue with Excel. Thank you

  30. Vish says:

    Hey, Thank you so much!!!1

    Solved my problem

  31. :D ebra says:

    It worked for SQL Server 2012 also!!  Unbelievable… I should have made bets on this one!  ๐Ÿ˜€

  32. David says:

    You are a legend!!!!!! This fix worked perfectly!

    And special thanks to James Field for mentioning TeamCity so google brought me here

  33. Chetan says:

    Hi,

    I have exactly the same issue, but the above fix doesn't help for me. ๐Ÿ™ I am still getting the same error even after creating the "Desktop" folder.

  34. Rick says:

    Thank you so much for the solution.  Aneurysm adverted.

  35. Faheem says:

    i am doing the same it is working in iis 7 but not in iis 8 ?? can anyone guide me …

  36. faheem says:

    how to deal with this issue in windows server 2012 ?? reply me ASAP

  37. ravi_maduri_mca@hotmail.com says:

    The solution solved my issue with Excel.  Thanks so much!

  38. Norbert says:

    It worked for me, thanks a lot, but… Can anyone explain me, why this is working?

  39. Pulkit K says:

    Awesome! This worked for me too! My problem was: I was able unable to open a workbook via a vbs script scheduled in the Task Scheduler to run even when the user is logged out. I've been trying to resolve this for a week now. Just created the Desktop folder and it worked flawlessly! Thanks! ๐Ÿ˜€

    Is it too late to ask how did you ever come across this solution?

  40. Suraj says:

    Thanks for posting this.. was breaking my head over why it works from cmd but not when run as a service..  ๐Ÿ™‚

  41. Prathap says:

    Create the folders as well as provide write access to the App pool account that is running web application.  After performing this,I was able to run on windows 7 machine but application is hanging on windows Server 2008 R2 and Office 2007 Excel

  42. Ole Hem says:

    Works also on Server 2012 through an Dynamics AX Aos service.

    Thanks!

  43. JC says:

    It seems to be a hidden bug.

    You saved my life! Thank you so much.

  44. jimmy says:

    hi, what about windows server 2012 !!

  45. radhakrishna says:

    yes it got resolved by creaing Desktop folder as mentioned above path. but after 2 days once again same error occured. please suggest

  46. Naveen says:

    It worked on Windows 7 64-bit too!

    The solution is really crazy!!!!!!!!!!!

  47. Sharada says:

    This solved my issue. Thanks so much!!! After wasting sometime on it, i found this useful post.

  48. Martas says:

    Hey! It works! It saved mlds of hours for me. Once you're in Czech, just give me a call for a beer. I pay! ๐Ÿ™‚

    THX once again.

    …..damn illogical obstructions…

  49. Nikolay Galibov says:

    This solution is also suitable for IIS 7.5?

  50. Brijesh Kothari says:

    Thanks a lot to you. You have helped me solve my issue.

  51. Sujoy says:

    Such a funny solution!Thanks a lot Deb!

  52. KJMClark says:

    A few other notes: Remember that the user account the service is running under will need to have appropriate permissions on those desktop folders.  It doesn't do any good to just create the folders if the account that will try to access them has insufficient permissions there.  If you can't even get your COM interop to start, you may need to go into dcomcnfg to give launch permission to that account as well.  Remember to run dcomcnfg as administrator, expand "Console Root"/"Component Services"/"Computers"/"My Computer".  Right-click "My Computer" and find the "COM Security" tab.  On there, choose "Edit Default" under "Launch and activation permissions".  Make sure your account is listed there, and give it at least "Launch Local" permission.  This new security model is such a pain.

  53. Umar says:

    Life saver.

    Thanks man…

  54. Edgar Santos says:

    Works!!! Don't forget to Add Permissions to the Folder Desktop!

  55. Murali says:

    You looks like god to me today !!!..

    I have the problem with MS Word. i have done cross-check App with Excel.

    Solution solved both Problems !!!

  56. Yuri says:

    Thank you very mutch. ๐Ÿ™‚

  57. Tyler says:

    This made my DAY!

  58. sinil says:

    u saved my life~~ thanks

  59. Ale says:

    Thanks so much for the solution – so glad to find your post.

  60. Nikhil says:

    This works perfectly fine in 2012 as well!! Thanks for posting.

  61. Michael says:

    Thanks for this … spent hours trying to figure it out … owe you a beer

  62. Anonymous says:

    This also worked for me…Very, very weird.  Weirdest solution for a problem I have ever seen.  

    Would be nice if you also posted the reason you discovered and why it works…but the solution is great!

  63. Anonymous says:

    wow!!! cant believe something so simple was causing me a headache. Thanks for the investigating. This has worked for me…

  64. Anonymous says:

    Thank YOU so much

    just awesome

    please i need some explanation how and why this thing works, could someone help with it?

  65. Anonymous says:

    It saved me few hours… Thanks!!!

    In my case, it is a normal executable file, put into windows task scheduler. The executable run OK if it run from windows explorer, but while executing from task scheduler, it logs the bizarre error message. I tried this solution and it worked.

    Thanks again.

  66. Reino Boonstra says:

    Thanks a lot! I had the same issue on Windows Server 2012 running Excel from a compiled Matlab module. This explanation solved it!

  67. Lison says:

    You made my day, it worked like a charme. I've been searching for a solution since couple of days… Thank you so much !!!

  68. ramy says:

    i have office 2016 and it doesn't work

  69. R Washington says:

    Thanks! That did the trick, very strange.

  70. You are a genius. This worked perfectly for Windows 2012 and Office 2016. says:

    I had a .NET ASPX page that had this error and creating this directory and granting access fixed it.  Thanks.

    Chris – coi@cirwin.com

    1. Ashutosh says:

      But it doesn’t work for Win2012 + Office2010, does it?

  71. Serge says:

    Worked for me with SQL Server 2014 and Windows 8.1 Pro. Thanks a lot, wise wizard!

  72. Dylan Schaffer says:

    This worked! WHY?! Thanks!

  73. Big Dave says:

    This is the best solution EVER , Drop into the snug in Skerries and there’s 5 pints waiting for you !!!

  74. Chad says:

    SQL Server 2012 64 bit.
    Thanks.

  75. chris says:

    โ€˜Microsoft Office Excel cannot access the file .
    I am also facing this kind of problem. give me a solution

  76. kevin says:

    i spent 4 days working on this thing. I found this blog and thought no way would creating this directory make any sense. so I tried it…………….worked like a charm. just saved me a lot more time looking at this issue

  77. Ashutosh says:

    I am using a Windows Server 2012 R2, similar issue occurs but the above solution doesn’t fix it. Could you please help me fix it??

  78. Jody says:

    This was such a headache – Thank you for posting this solution!

  79. Gurvinder says:

    I am using windows 2012R2 server and facing the same issue. Its a 64 based system. I tried what you have posted but still facing the same issue. Can you please tell any other alternative ?

  80. ManPluz says:

    I am also using a Windows Server 2012 R2, similar issue occurs but the above solution doesnโ€™t fix it. Please help….

  81. ManPluz says:

    Hey Got the solution
    1. Run->dcomcnfg->Go to Component Services -> Computers->My Computer->DCOM Config->Microsoft Excel Application->Right click->Identity Tab-> Select โ€˜The interactive userโ€™-> OK
    (On 64 bit system with 32 bit Office try this: ->Start-> Run->mmc -32-> File->Add Remove Snap-in->Component Services >Add > OK > Console Root > Component Services > Computers >My Computer >DCOM Config >Microsoft Excel Application)

    2. Go to IIS Manager-> Select the application pool(here local system)->Local System->Right Click Advanced Settings->Change identity Property to Local System

    3. Restart IIS

  82. sailen says:

    Thanks, this solved our issue. Almost going to pull my hair ๐Ÿ˜‰

  83. Sergio says:

    Thank you very much can not believe that this is the problem ๐Ÿ™‚

  84. Sean Pat says:

    This worked for me.