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

Published Jan 15 2019 02:54 PM 777 Views
First published on MSDN on Dec 19, 2012

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

%3CLINGO-SUB%20id%3D%22lingo-sub-317477%22%20slang%3D%22en-US%22%3EError%20%E2%80%98Microsoft%20Office%20Excel%20cannot%20access%20the%20file%E2%80%99%20while%20accessing%20Microsoft%20Office%2011.0%20Object%20Library%20from%20SSIS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-317477%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Dec%2019%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EFolks%2C%20yet%20another%20stumble%20with%20SSIS%20and%20Excel.%20This%20time%20I%20am%20using%20Microsoft%20Office%2011.0%20Object%20Library.%20The%20code%20runs%20fine%20on%20Windows%20Server%202003.%3C%2FP%3E%0A%20%20%3CP%3EBelow%20is%20the%20code%20sample.%3C%2FP%3E%0A%20%20%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%0A%20%20%3CP%3Eusing%20Microsoft.Office.Interop.Excel%3B%3C%2FP%3E%0A%20%20%3CP%3Eusing%20Excel%20%3D%20Microsoft.Office.Interop.Excel%3B%3C%2FP%3E%0A%20%20%3CP%3Eusing%20System.Runtime.InteropServices%3B%3C%2FP%3E%0A%20%20%3CP%3EExcel.Application%20m_ExcelApplication%20%3D%20new%20Excel.Application()%3B%3C%2FP%3E%0A%20%20%3CP%3Em_ExcelApplication.Visible%20%3D%20false%3B%3C%2FP%3E%0A%20%20%3CP%3Em_ExcelApplication.UserControl%20%3D%20false%3B%3C%2FP%3E%0A%20%20%3CP%3Em_ExcelApplication.Application.ScreenUpdating%20%3D%20false%3B%3C%2FP%3E%0A%20%20%3CP%3Em_ExcelApplication.DisplayAlerts%20%3D%20false%3B%3C%2FP%3E%0A%20%20%3CP%3Estring%20strFileName%20%3D%20%40%22d%3A%5Ctesting%5Ctesting.xls%22%3B%3C%2FP%3E%0A%20%20%3CP%3EExcel.Workbook%20m_ExcelWorkBook%20%3D%20m_ExcelApplication.Workbooks.Open(strFileName%2C%20%2F%2FFileName%3C%2FP%3E%0A%20%20%3CP%3E2%2C%20%2F%2FUpdateLinks%3C%2FP%3E%0A%20%20%3CP%3EbReadOnly%2C%20%2F%2FReadOnly%3C%2FP%3E%0A%20%20%3CP%3EType.Missing%2C%20%2F%2FFormat%3C%2FP%3E%0A%20%20%3CP%3EType.Missing%2C%20%2F%2FPassword%3C%2FP%3E%0A%20%20%3CP%3EType.Missing%2C%20%2F%2FWriteResPassword%3C%2FP%3E%0A%20%20%3CP%3Etrue%2C%20%2F%2FIgnoreReadOnlyRecommended%3C%2FP%3E%0A%20%20%3CP%3EType.Missing%2C%20%2F%2FOrigin%3C%2FP%3E%0A%20%20%3CP%3EType.Missing%2C%20%2F%2FDelimiter%3C%2FP%3E%0A%20%20%3CP%3Efalse%2C%20%2F%2FEditable%3C%2FP%3E%0A%20%20%3CP%3EType.Missing%2C%20%2F%2FNotify%3C%2FP%3E%0A%20%20%3CP%3EType.Missing%2C%20%2F%2FConverter%3C%2FP%3E%0A%20%20%3CP%3EType.Missing%2C%20%2F%2FAddToMru%3C%2FP%3E%0A%20%20%3CP%3EType.Missing%2C%20%2F%2FLocal%3C%2FP%3E%0A%20%20%3CP%3EType.Missing%20%2F%2FCurruptLoad%3C%2FP%3E%0A%20%20%3CP%3E)%3B%3C%2FP%3E%0A%20%20%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%0A%20%20%3CP%3EWhen%20executing%20the%20Open%20function.%20I%20received%20the%20following%20exception%3A%3C%2FP%3E%0A%20%20%3CP%3E%3CI%3EMicrosoft%20Office%20Excel%20cannot%20access%20the%20file%20'd%3A%5Ctesting%5Ctesting.xls'%20%3C%2FI%3E%20%3CI%3E%20.%20%3C%2FI%3E%3C%2FP%3E%0A%20%20%3CP%3EThere%20are%20several%20possible%20reasons%3A%3C%2FP%3E%0A%20%20%3CP%3E%E2%80%A2%20The%20file%20name%20or%20path%20does%20not%20exist%3C%2FP%3E%0A%20%20%3CP%3E%E2%80%A2%20The%20file%20is%20being%20used%20by%20another%20program.%3C%2FP%3E%0A%20%20%3CP%3E%E2%80%A2%20The%20workbook%20you%20are%20trying%20to%20save%20has%20the%20same%20name%20as%20a%20currently%20open%20workbook.%3C%2FP%3E%0A%20%20%3CP%3EHowever%2C%20when%20I%20removed%20the%20directory%20information%20and%20just%20give%20it%20the%20file%20name%3A%20'testing.xls'%20-%20I%20put%20testing.xls%20in%20to%20the%20same%20directory%20as%20the%20Windows%20Service-%20everything%20works%20fine%2C%20bizarre%20isn't%20it.%3C%2FP%3E%0A%20%20%3CP%3EResearch%20shows%20that%20automation%20is%20an%20issue%20with%20Windows%20Server%202008%2C%20specifically%20the%2064-bit%20version.%20The%2032bit%20version%20works%2C%20as%20well%20as%20windows%202003%2032%2F64bit.%20I'm%20unsure%20at%20this%20point%20as%20to%20whether%20it%20affects%20only%20SERVICES%20or%20APPLICATION%20automation%20as%20well.%3C%2FP%3E%0A%20%20%3CP%3EI%20dug%20in%20further%20and%20figured%20out%20the%20solution%20which%20is%20pretty%20crazy%20and%20actually%20motivated%20me%20to%20blog%20this%20for%20thousands%20of%20you%20who%20may%20run%20into%20this%20hair-pulling%20problem%20with%20SSIS%20and%20Excel.%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20SOLUTION%3A%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3EFor%20Windows%202008%20Server%20x64%3A%20Create%20the%20following%20directory%3A%3C%2FP%3E%0A%20%20%3CP%3EC%3A%5CWindows%5CSysWOW64%5Cconfig%5Csystemprofile%5CDesktop%3C%2FP%3E%0A%20%20%3CP%3EFor%20Windows%202008%20Server%20x86%3A%20Create%20the%20following%20directory%3A%3C%2FP%3E%0A%20%20%3CP%3EC%3A%5CWindows%5CSystem32%5Cconfig%5Csystemprofile%5CDesktop%3C%2FP%3E%0A%20%20%3CP%3EThats%20It!!%20Voila!!%20You%20are%20all%20set%20to%20go%E2%80%A6..%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20Author%20%3A%20Debarchan(MSFT)%20SQL%20Developer%20Engineer%2C%20Microsoft%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20Reviewed%20by%20%3A%20Snehadeep(MSFT)%2C%20SQL%20Developer%20Technical%20Lead%20%2C%20Microsoft%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-317477%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Dec%2019%2C%202012%20Folks%2C%20yet%20another%20stumble%20with%20SSIS%20and%20Excel.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-317477%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EIntegration%20Services(SSIS)%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 02:54 PM
Updated by: