Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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
Anonymous
December 28, 2012
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.Anonymous
December 28, 2012
Are you sure you created: C:WindowsSysWOW64configsystemprofileDesktop ?Anonymous
March 05, 2013
The solution solved my issue with Excel. Thanks so much!Anonymous
April 03, 2013
YES!!!! this solves my problem. thank you so muchAnonymous
April 07, 2013
Hi Now mine at least runs... but runs and runs as if hanging. Can you tell why? Thanks!- Anonymous
April 21, 2016
Hi, how did you resolve your issue ? I'm facing thew same problem.
- Anonymous
Anonymous
April 08, 2013
Hi Marlene, Is the hang scenario from a SQL Server job? Can you run the package successfully from command prompt or BIDS? --DebSAnonymous
May 01, 2013
Thanks a million, this fixed it for me. Luckily I still have some hair left.Anonymous
May 23, 2013
This solution worked for us! What an obscure problem/solution!Anonymous
June 04, 2013
It worked for me !! Thanks a Ton. I have no clue how its related but it does workAnonymous
June 05, 2013
Many Thanks... It solves my problem!!!Anonymous
June 10, 2013
Dude! You have saved me hundreds of hours of thinking it was my code or permissions... I owe you a beer!Anonymous
June 10, 2013
Hey Eric! Seriously? Tell me where are you located. I can go anywhere for a beer :P ~DAnonymous
August 21, 2013
Thank you, it worked for me as well.Anonymous
September 08, 2013
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!Anonymous
November 22, 2013
This worked for me. As a previous commenter said, so obscure! Thank you.Anonymous
December 16, 2013
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!Anonymous
December 18, 2013
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.Anonymous
December 30, 2013
your post helped me second time! i had this issue about year ago and successfully forgot solution... thanks!Anonymous
January 08, 2014
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 .Anonymous
February 03, 2014
i have created the folder but it didn't work for meAnonymous
February 25, 2014
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?Anonymous
March 06, 2014
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.Anonymous
March 13, 2014
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.Anonymous
March 20, 2014
Worked for me...nice post. Thanks for this.Anonymous
April 01, 2014
It works for me...really woth writing this one but needs more tagging here.Anonymous
April 14, 2014
This isn't a workable solution, what if I have to write to file on a network?Anonymous
April 18, 2014
Wow. Thanks a lot! Saved me prob hours of garbage research.Anonymous
April 22, 2014
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?Anonymous
April 29, 2014
This Solution solved my issue with Excel. Thank youAnonymous
April 29, 2014
Hey, Thank you so much!!!1 Solved my problemAnonymous
May 06, 2014
It worked for SQL Server 2012 also!! Unbelievable... I should have made bets on this one! :DAnonymous
June 23, 2014
You are a legend!!!!!! This fix worked perfectly! And special thanks to James Field for mentioning TeamCity so google brought me hereAnonymous
June 23, 2014
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.Anonymous
June 28, 2014
Thank you so much for the solution. Aneurysm adverted.Anonymous
July 10, 2014
i am doing the same it is working in iis 7 but not in iis 8 ?? can anyone guide me ...Anonymous
July 13, 2014
how to deal with this issue in windows server 2012 ?? reply me ASAPAnonymous
August 03, 2014
The solution solved my issue with Excel. Thanks so much!Anonymous
August 03, 2014
It worked for me, thanks a lot, but... Can anyone explain me, why this is working?Anonymous
August 11, 2014
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! :D Is it too late to ask how did you ever come across this solution?Anonymous
August 29, 2014
The comment has been removedAnonymous
September 21, 2014
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 ExcelAnonymous
September 21, 2014
Works also on Server 2012 through an Dynamics AX Aos service. Thanks!Anonymous
September 25, 2014
It seems to be a hidden bug. You saved my life! Thank you so much.Anonymous
October 01, 2014
hi, what about windows server 2012 !!Anonymous
October 20, 2014
yes it got resolved by creaing Desktop folder as mentioned above path. but after 2 days once again same error occured. please suggestAnonymous
November 12, 2014
The comment has been removedAnonymous
December 01, 2014
This solved my issue. Thanks so much!!! After wasting sometime on it, i found this useful post.Anonymous
December 07, 2014
The comment has been removedAnonymous
December 16, 2014
This solution is also suitable for IIS 7.5?Anonymous
December 16, 2014
Thanks a lot to you. You have helped me solve my issue.Anonymous
December 17, 2014
Such a funny solution!Thanks a lot Deb!Anonymous
January 16, 2015
The comment has been removedAnonymous
January 24, 2015
Life saver. Thanks man...Anonymous
January 28, 2015
Works!!! Don't forget to Add Permissions to the Folder Desktop!Anonymous
January 28, 2015
The comment has been removedAnonymous
February 02, 2015
Thank you very mutch. :)Anonymous
February 16, 2015
ThanksAnonymous
March 03, 2015
This made my DAY!Anonymous
March 04, 2015
u saved my life~~ thanksAnonymous
April 16, 2015
Thanks so much for the solution - so glad to find your post.Anonymous
May 07, 2015
This works perfectly fine in 2012 as well!! Thanks for posting.Anonymous
May 25, 2015
Thanks for this ... spent hours trying to figure it out ... owe you a beerAnonymous
May 29, 2015
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!Anonymous
June 16, 2015
The comment has been removedAnonymous
June 23, 2015
Thank YOU so much just awesome please i need some explanation how and why this thing works, could someone help with it?Anonymous
June 25, 2015
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.Anonymous
July 06, 2015
Thanks a lot! I had the same issue on Windows Server 2012 running Excel from a compiled Matlab module. This explanation solved it!Anonymous
August 18, 2015
You made my day, it worked like a charme. I've been searching for a solution since couple of days... Thank you so much !!!Anonymous
October 14, 2015
i have office 2016 and it doesn't workAnonymous
November 07, 2015
Thanks! That did the trick, very strange.Anonymous
December 08, 2015
I had a .NET ASPX page that had this error and creating this directory and granting access fixed it. Thanks. Chris - coi@cirwin.com- Anonymous
June 01, 2016
But it doesn't work for Win2012 + Office2010, does it?
- Anonymous
Anonymous
December 23, 2015
Worked for me with SQL Server 2014 and Windows 8.1 Pro. Thanks a lot, wise wizard!Anonymous
March 11, 2016
The comment has been removedAnonymous
April 11, 2016
This worked! WHY?! Thanks!Anonymous
April 28, 2016
This is the best solution EVER , Drop into the snug in Skerries and there's 5 pints waiting for you !!!Anonymous
May 04, 2016
SQL Server 2012 64 bit. Thanks.Anonymous
May 18, 2016
‘Microsoft Office Excel cannot access the file .I am also facing this kind of problem. give me a solutionAnonymous
May 24, 2016
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 issueAnonymous
June 01, 2016
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??- Anonymous
October 25, 2016
I am having similar issue. In my case i'm getting excel exception and a DOS 67 error when excel tries to access sharepoint rive. The service account used has complete domain access. I can access the sharepoint(target) drive while logged onto the server (source). I am able to create and delete a file. however, when running through the peoplesoft process scheduler i get those errors. The scheduler is using the same domain ID mentioned. we are on Windows VM ware 2012 R2 64-bit, Peopletools 8.54.19 and Excel 2010 64-bitI've created the desktop folder on All the servers. Please help!
- Anonymous
Anonymous
June 22, 2016
This was such a headache - Thank you for posting this solution!Anonymous
June 27, 2016
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 ?Anonymous
June 28, 2016
I am also using a Windows Server 2012 R2, similar issue occurs but the above solution doesn’t fix it. Please help....Anonymous
June 28, 2016
The comment has been removed- Anonymous
October 16, 2016
Hi.... Thanks Sir... It solved my problem :) - Anonymous
March 16, 2017
well done guy... it has solved my problem after trying many solutions. Indeed I have SQL Server 2012 and a 32bit Excel on a 64 bit system. I applied only the step 1 of your solution and it works perfectly. Thanks again.
- Anonymous
Anonymous
July 04, 2016
Thanks, this solved our issue. Almost going to pull my hair ;)Anonymous
July 17, 2016
Thank you very much can not believe that this is the problem :)Anonymous
July 21, 2016
This worked for me.Anonymous
August 19, 2016
Big thanks...this saved $50 000 000 to our company.Anonymous
August 24, 2016
Awesome...works like a charm.Thank you so much. It saved a lot of time :)Anonymous
September 08, 2016
this information is not helpful to usAnonymous
October 13, 2016
Thank you so much. This must have saved me hours of troubleshooting.Anonymous
October 20, 2016
Thank you so much. This solved my Excel automation problem.Anonymous
October 24, 2016
Holy Mackerel - what a ridiculous problem - and how silly this solution seemed - BUT IT WORKED! a Million thanks!Anonymous
October 25, 2016
I am having similar issue. In my case i’m getting excel exception and a DOS 67 error when excel tries to access sharepoint rive. The service account used has complete domain access. I can access the sharepoint(target) drive while logged onto the server (source). I am able to create and delete a file. however, when running through the peoplesoft process scheduler i get those errors. The scheduler is using the same domain ID mentioned.we are on Windows VM ware 2012 R2 64-bit, Peopletools 8.54.19 and Excel 2010 64-bitI’ve created the desktop folder on All the servers.Please help!Anonymous
November 09, 2016
Thanks! Worked for me too :)Anonymous
November 09, 2016
It worked . Brilliant solution. Saved my huge time.Anonymous
November 22, 2016
HOLY... WOW... How the heck does something as simple as that solve this issue?!?!?!?!?! LOLAnonymous
December 05, 2016
THANK YOU!!!!Anonymous
December 16, 2016
Snehadeep its too wierd. Thanks a lot (really was about to pull my hair) :-)Anonymous
December 22, 2016
Amazing! Thanks, this solution solved my problems.Anonymous
January 04, 2017
OMG. Thank you for saving my hair!Anonymous
March 30, 2017
Thank you for this! #Savinghairsince2012Although it has only been four years since this post, I wonder if there's a bug raised for this somewhere.Anonymous
April 18, 2017
I created the Desktop folder as you prescribed, and ran my test. it worked like a charm. Thank you, Thank you. I don't believe I would have thought about this obscure solution.Anonymous
April 25, 2017
And what is the solution if I have Windows 7 Enterprise 64 bit?. I have the same problemAnonymous
May 09, 2017
Thank u so muchAnonymous
May 23, 2017
wow it works by simply creating the desktop foldersAnonymous
May 25, 2017
Worked with Excel Object 15.0 and Windows Server 2012. Thanks!Anonymous
December 14, 2017
This works on Windows 2012 R2 as well. Thanks a million. It would have taken eons to figure this out.Anonymous
December 14, 2017
Does this work for MS Office 2010? THanks!- Anonymous
December 28, 2017
Yes, it would work!
- Anonymous
Anonymous
May 18, 2018
Thanks!! Still a valid, though weird ;-), solution.Anonymous
May 11, 2019
Man, I'll find you and I'll marry you for this post. There's no way I could have imagined this otherwise... Awesome. Thank you very much. You've saved me another 10 hours at least.