Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

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

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

Rate This
  • Comments 40

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

Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post
  • 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.

  • Are you sure you created: C:\Windows\SysWOW64\config\systemprofile\Desktop ?

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

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

  • Hi

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

    Thanks!

  • Hi Marlene,

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

    --DebS

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

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

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

  • Many Thanks... It solves my problem!!!

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

  • Hey Eric!

    Seriously? Tell me where are you located. I can go anywhere for a beer :P

    ~D

  • Thank you, it worked for me as well.

  • 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!

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

Page 1 of 3 (40 items) 123