Connecting to Excel

kruse

unread,

Mar 6, 2006, 4:26:16 AM3/6/06

to

This code has worked before. But now it has stopped working:

<CFSCRIPT>
/* Open Excel in the background */
objExcel.Visible = false;
/* Disable alerts such as: 'Save this document?' */
objExcel.DisplayAlerts = false;
/* Define the workbooks object */
objWorkBook = objExcel.Workbooks;
/* Open the Excel file */
objOpenedBook = objWorkBook.Open("#Attributes.FilePath#");
/* Create a collection of all existing worksheets */
objSheets = objOpenedBook.Sheets;
/* The number of sheets in the file */
numOfSheets = objSheets.Count;
</CFSCRIPT>

I get this error:

An exception occurred when executing a Com method.
The cause of this exception was that: AutomationException: 0x80010105 - The
server threw an exception..

The error occurred in D:\Applications\LDev\Tag\ReadXLS.cfm: line 85
Called from D:\Applications\LDev\Assessment.cfm: line 84
Called from D:\Applications\LDev\Assessment.cfm: line 73
Called from D:\Applications\LDev\Assessment.cfm: line 51
Called from D:\Applications\LDev\Assessment.cfm: line 1
Called from C:\CFusionMX7\CustomTags\Layout.cfm: line 351
Called from C:\CFusionMX7\CustomTags\Layout.cfm: line 1
Called from D:\Applications\LDev\Index.cfm: line 99
Called from D:\Applications\LDev\Tag\ReadXLS.cfm: line 85
Called from D:\Applications\LDev\Assessment.cfm: line 84
Called from D:\Applications\LDev\Assessment.cfm: line 73
Called from D:\Applications\LDev\Assessment.cfm: line 51
Called from D:\Applications\LDev\Assessment.cfm: line 1
Called from C:\CFusionMX7\CustomTags\Layout.cfm: line 351
Called from C:\CFusionMX7\CustomTags\Layout.cfm: line 1
Called from D:\Applications\LDev\Index.cfm: line 99

83 : objExcel.Visible = false;
84 : /* Disable alerts such as: 'Save this document?' */
85 : objExcel.DisplayAlerts = false;
86 : /* Define the workbooks object */
87 : objWorkBook = objExcel.Workbooks;

Any one who knows what can be done to make it work??

Iceborer

unread,

Mar 6, 2006, 11:17:05 AM3/6/06

to

Not sure that this is your answer, but there is an MS KB article that talks about this error http://support.microsoft.com/kb/912448#kb2 ...

ksmith

unread,

Mar 6, 2006, 2:10:40 PM3/6/06

to

Hi Kruse,
Some additional info would be helpful. What version of CF and what version of
excel is on the machine? Does it work on a freshly started CF server or always
fail? If you run taskmanager, do you see the excel application under
processes? Do you see several?

kruse

unread,

Mar 7, 2006, 1:42:02 AM3/7/06

to

Originally posted by: Iceborer

Not sure that this is your answer, but there is an MS KB article that talks
about this error http://support.microsoft.com/kb/912448#kb2 ...

Yes I saw this article and installed office 2003 sp2. But it still doesn?t
work.

This sentence bothers me:
We do not currently recommend or support Automation of Microsoft Office
programs from any unattended, non-interactive client program or component. For
more information, click the following article number to view the article in the
Microsoft Knowledge Base:

kruse

unread,

Mar 7, 2006, 2:44:57 AM3/7/06

to

Originally posted by: ksmith

Hi Ken

We are using Coldfusion MX 7 (7,0,0,91690) on a windows 2003 server sp1.
The Excel version is 2003 sp2 (11.6560.6568).

When I run the code I see one Excel process that finishes when the code fails.
And it fails with this:

An exception occurred when executing a Com method.
The cause of this exception was that: AutomationException: 0x80010105 - The
server threw an exception..

The error occurred in D:\Applications\LDev\Tag\ReadXLS.cfm: line 85

Called from D:\Applications\LDev\Tag\PopUpUploadExcel.cfm: line 115

Called from D:\Applications\LDev\Tag\ReadXLS.cfm: line 85

Called from D:\Applications\LDev\Tag\PopUpUploadExcel.cfm: line 115

83 : objExcel.Visible = false;
84 : /* Disable alerts such as: 'Save this document?' */
85 : objExcel.DisplayAlerts = false;
86 : /* Define the workbooks object */
87 : objWorkBook = objExcel.Workbooks;

This mean that the connection to the Excel com object does success. But I
don?t understand why it fails on line 85 and not on line 83 which is the first
command it executes on the object.

I have tried to restart the server and then it worked!! I hope it will keep
working.

kruse

unread,

Mar 7, 2006, 3:30:29 AM3/7/06

to

I have tried to locate the error.

The com connection works fine

I have tried to comment some of the lines out when I execute commands on the
object.
The result is shown beneath.

<CFSCRIPT>
objExcel.Visible = false;
/*objExcel.DisplayAlerts = false; */
objWorkBook = objExcel.Workbooks;
/* objOpenedBook = objWorkBook.Open("#Attributes.FilePath#");*/
/* objSheets = objOpenedBook.Sheets;*/
/* numOfSheets = objSheets.Count;*/
</CFSCRIPT>

I think it is strange that the Visible and Workbooks does work. But the rest
does throw an error if they are used?

kruse

unread,

Mar 7, 2006, 3:50:12 AM3/7/06

to

I have made some more tests.
<CFSET test = objExcel.Visible > WORKS
<CFSET test = objExcel.DisplayAlerts> DOES NOT WORK.
throws the well known error 0x80010105.

This is verry strange. When I do the test locally on my dev env it works.

ksmith

unread,

Mar 7, 2006, 9:56:59 AM3/7/06

to

Hi,
Well I find a bunch of things here. First, I am able to make your original
code work here. I am using cfmx7.01 and msoffice2003 sp2 also. The first
thing I find goes along with microsoft's warning about not using a clientless
account. Everything works if I have started cfmx from the commandline or I
setup the CFMX appserver service to run as a domain account. Without the
domain account it fails and I see instances of DW20.exe running. DW20 is
microsoft's error and reporting module for office. So, I guess you should try
running CFMX under a named account per the following
http://www.macromedia.com/go/tn_17279

Next, I would start using some of the new features of CFMX to make your COM
calls a little more robust. In CFMX it is possible to use java stubs to make
your calls to the msoffice programs. This can speed things up and even
sometimes cure specific method call failures. In your case, all that is needed
to do this is to replace the programID you are using in the cfobject calls from
"Excel.Application" to "Excel.Application.10". You can read about this at
http://livedocs.macromedia.com/coldfusion/7/htmldocs/00001582.htm#1148538.
Just realize for msoffice the stubs and xml file are already created for you
and ready to use.

The next enhancement is the releasecomobject function. CFMX has this function
and it will release excel.application from memory. When I use this function
call at the end of your page, I never see more than 1 instance of excel.
Without I see 2 or more.

I also see on the cfobject/create code working, never the connect. That is
probably a good thing. I would suspect that cfobject/connect could put you in
the situation where you temporarilly connect to a COM object that is about to
shutdown to be garbage collected. The developer's guide does mention putting
COM objects into the application scope as a way to make things more efficient.
You could test it. The procedure is discussed in the same chapter of the
Developer's Guide that I refered you to already.

Finally, you might consider completely moving away from COM. COM is somewhat
inefficient when called from java, as CFMX does. It also requires that you
have msoffice on the CFMX server and puts you at its mercy as changes are made
(see first paragraph). Apache has a project http://jakarta.apache.org/poi/
which replaces the calls to COM object with calls to Java objects. Often the
exact same syntax for you COM object method calls will work with POI. You
simply do a cfobject/java instead of cfobject/COM. You will probably find all
methods and properties that expect a visible window will go away with POI. So,
things like "objExcel.visble=false" and displayAlerts should not be called.

HTH

ksmith

unread,

Mar 7, 2006, 10:33:53 AM3/7/06

to

It turns out the POI jar file is already installed with cfmx7. So, here is a
quick working example doing the NumberOfSheets in your test. Run that attached
code after you point it to an excel file on your system. You should still
download POI from Apache to get the docs and API guide.

<CFSCRIPT>
fs =
createobject("java","org.apache.poi.poifs.filesystem.POIFSFileSystem");
fsSTR=createobject("java","java.io.FileInputStream");
fs.init(fsSTR.init("C:\Documents and Settings\ksmith\My
Documents\current.xls"));
//POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream("workbook.xls"));
wb = createobject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook");
wb.Init(fs);
</CFSCRIPT>

<H2>number sheets: <cfoutput>#wb.getNumberOfSheets()#</cfoutput></H2>

kruse

unread,

Mar 8, 2006, 2:22:57 AM3/8/06

to

Thanks alot Ken.

I was starting the CF service with a domain user.
When I tried Excel.Application.10 instead of Excel.Application it worked just
fine.

Yesterday I started to look at the customtag cfx_excel which does the job
great through POI.
But I think that I will give my own version through POI i try. I think that It
will be faster than the COM version.

Thank you for your great help.

kruse

unread,

Mar 8, 2006, 8:32:44 AM3/8/06

to

H Ken,

Is there a list or description of all the jar files (if any other than POI)
installed with cfmx7.
It would be nice to know what other jar files that could be used without any
work in cfmx7.

By the way it works just fine and mutch faster than the COM method.

Best regards,
Morten