Excel automation:  Open method of Workbooks class failed

Milk_Man_Dan
Milk_Man_Dan used Ask the Experts™
on
Hello all,

I've created an ASP.Net page that generates a report based on a source Excel spreadsheet.  The page works perfectly on my local server (Server 2003).  However upon deploying it to another server (Server 2008), the page crashes when I attempt to open the source spreadsheet.  I receive the following error message:

Open method of Workbooks class failed

Things I have tried / noticed (I'm relatively new at this, so bear with me):

1.  I am able to use a macro in Excel on the new server to open the source spreadsheet with no problems.
2.  I have tried using both a fully qualified path to the source spreadsheet, as well as a Server.MapPath approach.  Both run into the same problem.
3.  Attempting to open an invalid file name produces a different error, so it is recognizing the file (I'm not sure what this tells us though)
4.  I have granted "Everyone" full access to the folders in question.  I understand the inherent security risk here; I'm just trying to get the application to work first.
5.  I've granted Everyone access to Excel with dcomcnfg.exe
6.  I have verified that the exact same version of Excel is running on both servers.  (Excel 2003, (11.5612.5606).

For the time being, I used Excel on the new server to create a blank spreadsheet named test.xls, and I am attempting to simply open that file, which can be seen in my code below.

Does anyone have any thoughts on what could be the problem?  Thank you in advance for your help and patience with a neophyte such as myself.
Dim oExcelSource As Object					
oExcelSource = CreateObject("Excel.Application")
 
oExcelSource.Workbooks.Open("C:\test.xls")  ' <-- The page crashes here
 
...
...

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dumb question, but is the file test.xls placed where it's supposed to be on the 2008 server?

Author

Commented:
Yes it is.  
why dont you try to early bind instead of late bind and see if it works?

to early bind, you first need to reference Microsoft.Office.Interop.Excel

Then, use the Imports statement to import namespace Microsoft.Office.Interop.

Finally, you can use early bind in your code like:


Imports Microsoft.Office.Interop
 
...
 
Dim oExcelSource As New Excel.Application
oExcelSource.Workbooks.Open("C:\test.xls")  

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

It would be good design to even add a couple of error preventing lines, on the likes of:

Imports Microsoft.Office.Interop
Imports System.File.IO
...
 
Dim oExcelSource As New Excel.Application

If File.Exists("C:\test.xls") Then
     oExcelSource.Workbooks.Open("C:\test.xls")  
End If

Author

Commented:
Thanks for your help with this newyuppie.

Sadly, this isn't a Visual Studio project, but rather is being developed in Dreamweaver.  I mention this because I don't know for sure if I'm doing this part correctly.  I attempted to reference Microsoft.Office.Interop.Excel like you suggested, as well as importing the Microsoft.Office.Interop Namespace.  My code is shown below; please let me know if I'm doing this incorrectly.

At any rate, the page was successfully able to create the oExcelSource Excel Application.  However, when attempting to open the spreadsheet, I'm given a new error message:

Exception from HRESULT: 0x800A03EC

I Google'd this for a bit and found that this error can sometimes be resolved by granting "Trust access to Visual Basic Project" under Macro Security in Excel.  I granted this trust access, as well as set the security level to low just in case this could be playing a role.  Unfortunately, the error still persists.

Do you have any thoughts?  Thank you in advance for your help.
<%@ Assembly name="Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
 
<%@ Import Namespace="Microsoft.Office.Interop" %>
 
...
 
Dim oExcelSource As New Excel.Application
 
oExcelSource.Workbooks.Open("C:\test.xls") ' <-- The page crashes here
 
...

Open in new window

I found this question in EE,

http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21267120.html

Basically, try the following
<%@ Assembly name="Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
 
<%@ Import Namespace="Microsoft.Office.Interop" %>
 
...
 
Dim oExcelSource As New Excel.Application
 
oExcelSource.Workbooks.Open("C:\test.xls", null, null, null, null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null)
...

Open in new window

Or:
<%@ Assembly name="Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
 
<%@ Import Namespace="Microsoft.Office.Interop" %>
 
...
 
Dim oExcelSource As New Excel.Application
 
oExcelSource.Workbooks.Open("C:\test.xls", System.Reflection.Missing.Value , System.Reflection.Missing.Value , System.Reflection.Missing.Value , System.Reflection.Missing.Value ,
                        System.Reflection.Missing.Value ,
                        System.Reflection.Missing.Value ,
                        System.Reflection.Missing.Value ,
                        System.Reflection.Missing.Value ,
                        System.Reflection.Missing.Value ,
                        System.Reflection.Missing.Value ,
                        System.Reflection.Missing.Value ,
                        System.Reflection.Missing.Value ,
                        System.Reflection.Missing.Value ,
                        System.Reflection.Missing.Value )
...

Open in new window

Commented:
> I have granted "Everyone" full access to the folders in question

IUSR might not be included in "everyone". So set permissions for IUSR (you do not want permissions set for "everyone" on a server anyway.


Furthermore, using Excel.Application in a webbased solution is not recommended and asking for trouble. The simple way to create an Excel Report from a web application is to have a HTML-table and send it to the client with contenttype = "application/vnd.ms-excel". That will work for single-worksheet Excel Workbooks. If you need a Workbook with multiple worksheets, then you can use Office-XML: generate the XML on the server and send it with the Excel contenttype (as mentioned). It is only a lot of work to understand the format of Office-XML.

Author

Commented:
Thank you for your continued help with this.

I tried the code below, and it still resulted in Exception from HRESULT: 0x800A03EC (Using null threw a compilation error).  Just for kicks, I tried opening the file this way with the early bind method, and it also gave me the original "Open method of Workbooks class failed" error message.

I have confirmed that IUSR now also has full access to the files / folders I'm attempting to open.  

Thoughts?  Thank you again for your help, I greatly appreciate it.
<%@ Assembly name="Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
 
<%@ Import Namespace="Microsoft.Office.Interop" %>
 
...
 
Dim oExcelSource As New Excel.Application
 
oExcelSource.Workbooks.Open("C:\test.xls", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value)
 
...

Open in new window

Please try the solutions posted in this link:

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.office.developer.automation&tid=ea7ead60-6a52-4a52-a2ec-094657405ce3&cat=en_US_edd5c8cb-2460-4373-a729-fd2e9a1cc9f1&lang=en&cr=US&sloc=en-us&m=1&p=1

There are 2 solutions provided, please test each one and come back with news, I'll keep looking in the meantime.
Here is another workaround posted, apparently it is an issue with Excel automation and Server 2008 64 bit.

http://bytes.com/topic/c-sharp/answers/819740-c-service-excel-application-workbooks-open-fails-when-called-service
Please try the links in order.

I have a third link, where it basically does what we did earlier (missing.value 13 times), but has a final comma, and a "true". Also, they use Type.Missing instead of System.Reflection.Missing.Value. So the code would look like this:

<%@ Assembly name="Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
 
<%@ Import Namespace="Microsoft.Office.Interop" %>
 
...
 
Dim oExcelSource As New Excel.Application
 
oExcelSource.Workbooks.Open("C:\test.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, true)
 

Open in new window

Author

Commented:
You are a life saver!  Following the following steps resolved my problem:

1. Login to the server as a administrator.
2. Go to "Start" -> "Run" and enter "taskmgr"
3. Go to the process tab in task manager and check "Show Processes from all
users"
4. If there are any "Excel.exe" entries on the list, right click on the
entry and select "End Process"
5. Close task manager.
6. Go to "Start" -> "Run" and enter "services.msc"
7. Stop the service automating Excel if it is running.
8. Go to "Start" -> "Run" and enter "dcomcnfg"
9. This will bring up the component services window, expand out "Console
Root" -> "Computers" -> "DCOM Config"
10. Find "Microsoft Excel Application" in the list of components.
11. Right click on the entry and select "Properties"
12. Go to the "Identity" tab on the properties dialog.
13. Select "The interactive user."
14. Click the "OK" button.
15. Switch to the services console
16. Start the service automating Excel
17. Test you application again.

Thank you so much again for all of your help.  
I'm glad that worked for you! Thanks for the grade, see you around!
NY

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial