Excel Object Problems... 1

[mwolf00]

While working with a page that uses an excel object yesterday, it suddenly started to hang. The code is

	' create the Excel object
	Set excelApp = Server.CreateObject("Excel.Application")
	
	' open the spreadsheet file
	excelApp.Workbooks.Open Server.MapPath("../uploads/lrt/abstracts/" & lseAbs)

' some processing occurs...

	Set excelSheet = Nothing
	excelApp.workBooks.Close
	Set excelApp = Nothing

The first line I had an error on was

excelApp.workBooks.Close

which caused the page to hang. Now, when I try to open the page at all

Set excelApp = Server.CreateObject("Excel.Application")

hangs for a bit and then fails "server.createObject failed".

A side note, the excel file that I was trying to open when it hung for the first time is now locked open by IUSER so I cannot even delete it no matter how I try.

Any insite would be appreciated.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[Sheco]

Take a look at the task list on the server and see if you perhaps have an instance of excel running without any visable interface.

 

[tsuji]

owc used server-side has limitations and ms recognized and documented in a few of kb well-read articles, such as this.

http://support.microsoft.com/kb/317316

I can safely suppose you know well their existence.

But, looking at the except of script presented, I see a major problem in it. [tt]excelApp.workBooks.Close[/tt] immediately followed by [tt]Set excelApp = Nothing[/tt] is not enough to free up resource. excelApp must be quitted before release, else it would be staying in the memory orphaned. Hence the clean up should at least follow this pattern.
[tt]
Set excelSheet = Nothing
excelApp.workBooks.Close
[red]excelApp.quit[/red]
Set excelApp = Nothing
[/tt]

 

[mwolf00]

Thanks to both of you. I am still looking into solutions. Unfortunately, I do not have direct access to the server where this is happening. I have asked that they look into Sheco's suggestion. Unfortunately, I cannot try tsuji's suggestion until I can create the component again which may not happen at all. Is there another way to parse excel files on the server side without using Office Web Components?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[mwolf00]

I cannot remove/rename two excel files in the directory. They are listed as being in use by someone else. I'm pretty sure that they are where the problem started. Any ideas on how to get rid of these two files? I tried to force it using the FSO but no matter what, I can't seem to delete them.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[tsuji]

Try this? Go to the site server as admin. Run the administrative script (.vbs) to terminate all instances of excel see if this cures the problem and that you can start anew.
[tt]
dim cprocess, oprocess
set cprocess=getobject("winmgmts:root\cimv2").instancesof("win32_process where name='excel.exe'")
for each oprocess in cprocess
oprocess.terminate
next
set cprocess=nothing
[/tt]

 

[bboffin]

Addressing the original problem it's generally not a good idea to use the standard Excel.Application object in a web site as the interfaces can hang (as you found out) while trying to pop up message boxes which is why OWC was introduced.

However OWC is very limited and may not do what you want in which case you should look for a third party component that has been designed for web usage such as Aspose.Excel which will almost certainly handle it but it's quite expensive.


Bob Boffin

 

[mwolf00]

Thanks for all of the help. Sure enough, there were 30 instances of excel running on the server. I have added the "excelApp.quit" to my code so we will see if that clears up the problem.

Bob, there's no chance that they will spring for the third party application. It is tough enough to get them to update FREE drivers (Oracle comes to mind) on their servers. Anything that is out-of-their-ordinary is out-of-the-question. I'm not sure that the IT department here understands anything beyond HTML...


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[mwolf00]

The problem happened again. This time one the second file I opened (the one I suspected of problems). When I open this particular file, it seems to stay open. I changed it to "Read Only" and opened it with excel. Apparently the file was created on an older version of excel. Perhaps that is what is causing the problem?

Other than the FSO, is there a way force the file to be opened as "Read Only"? Will that even help?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[tsuji]

Check out excel workbooks.open method. The 2nd parameter is for updatelinks, and the 3rd is boolean true for readonly... Use the built-in object viewer for detail?

 

[mwolf00]

tsuji,

Thank you again for all of the help. I've changed the code to

excelApp.Workbooks.Open Server.MapPath("../uploads/lrt/abstracts/" & lseAbs),false, true

and it seems to be working. I cannot find more information on using the component in ASP - everything seems to be written for VB. Am I doing it correctly? It seems like I should leave updatelinks as False - any reason not to? I only want to read the files.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[tsuji]

>It seems like I should leave updatelinks as False - any reason not to?

It all depends [1] how the dispersed/distributed the data sources are, if any, such as a consolidated account data are consolidated from different individual accounts which may be located in external, independent workbooks; [2] how often those external sources changed; [3] how updated would you anticipate the clients need the data be; [4] how easiness you feel the resouces be available; [5] how speedy the response need be etc... It is scripter's call.

Note, however, that the 2nd parameter updatelinks takes on values like this.
[tt] update links' type:
0 = none (effectively like false or vbfalse=0)
1 = external only
2 = remote only
3 = all[/tt]
You can foresee if your sheets has remote links, on the server, that may need some serious testing.

[II]>I cannot find more information on using the component in ASP

These two articles contain extensive links for further reading.

http://support.microsoft.com/kb/317316/

http://support.microsoft.com/kb/257757/