ASP and excel help needed

[bastienk]

Hi All,

I have a web app that calls an excel spreadsheet for manipulation. During testing I can apparently open th excel app and there my program hangs. I noticed that I have excel processes not terminated on the server.

I would like to see if there is a way that I can loop thru the opened apps and close them, like a for each loop but am unsure as to logic. This is what I have so far...

Set objXlApp = GetObject(, "Excel.Application") 
If Err.Number <> 429 Then 
   set objXlApp = nothing
End If

Further to my app, is there an easy way to read the data in the excel sheet into an array. My plan was to conver the excel file to a csv file and read that in, but it hangs on the file SaveAs "myfile.csv"...

 Set objXlApp = Server.CreateObject("Excel.Application")
'open the spreadsheet file
  response.write "opening file<br>"
  set objXlWb = objXlApp.Workbooks.Open(Path & HR)
  objXlWb.ActiveWorkbook.SaveAs "hier.csv"
  objXlApp.Application.Quit

Help is, as always, much appreciated

TIA


Bastien

Cat, the other other white meat

 

[dilettante]

This comes up so often that somebody ought to add it to the FAQs over in the ASP Forum:

KB 257757 Considerations for Server-Side Automation of Office

Basically, it itsn't supported. Using Office programs server-side is (1.) at your own risk, and (2.) most likely violates your license agreement.

As a result, not much effort has gone into "solutions" for the problems you might encounter.

But if you just need to extract data out of Excel worksheets in ASP you might consider using ADO with Jet and the Excel driver. There are still caveats though:

KB 195951 HOWTO: Query and Update Excel Data Using ADO From ASP

The basic problem here is there is no concurrency control for access to an Excel workbook via ADO.


The only thing I can suggest you try that is simple is to be sure your ASP logic exits the Excel application (.Quit or .Exit? I don't recall right now, dig into the object model in Excel via the VBA IDE). Once this is done you should also be setting the Excel object reference variable to Nothing.

But basically, Excel and ASP don't mix. Word and Powerpoint have the same sorts of troubles.


-- "See, there's a warning right on the label. 'Invisible Pedestrian: Not For Blind Kids'."