Excel - why won't you go away??

ron_darrell

Hi guys - chances are this should be in the programming section but it is an ASP question so I guess here's as good a place as any. I have an web-app that needs a CSV file for importing data. The customer sending us the file can only generate XLS files (don't ask why - it's long and silly and makes you wonder does anyone know what a computer is for) so I've been trying to write a script to convert the file and read it into my DB (the code for grabbing data from the CSV file is already written and works fine with the other files being sent to us from other customers). My problem is this. The code I've written converts the file no problem but it doesn't release the file (error message: "this file is locked for editing by user") so I can't access it to read in the data. Worse still it leaves an Excel process running on the Server eating up resources. I can't for the life of me see where the code has gone wrong but if anyone has any suggestions they'd be more than welcome. And yes before anyone asks I have googled the subject. None of the data I've found has been illuminating. Anyways here's the code:

Dim appExcel, strFileName, strCSVFileName
strFileName = Server.MapPath("manifest.xls")
strCSVFileName = Server.MapPath("manifest.csv")

appExcel = Server.CreateObject("Excel.Application")
appExcel.Visible = False
appExcel.Workbooks.Open(strFileName)
appExcel.ActiveWorkbook.SaveAs strCSVFileName, 23
appExcel.Application.DisplayAlerts = True
appExcel.ActiveWorkbook.Close "savechanges:=true"
appExcel.Close "savechanges:=true"
appExcel.Application.Quit
Set appExcel = Nothing

Any help much appreciated. Feel like banging my head against the desk at this stage

-RD

Eoin

You can use an Excel spreadsheet as a datasource using the standard ADODB Connection that you would use for a database, would that be of any use?

ron_darrell

Ya I know that way of doing it. I can I guess write a page just for this file but at this stage with so many hours gone into trying to solve/understand the problem I'd like to see if there's any way of solving it the way I've been trying before I give up and do the other way. Silly I know.

Eoin

ron_darrell wrote: »

Ya I know that way of doing it. I can I guess write a page just for this file but at this stage with so many hours gone into trying to solve/understand the problem I'd like to see if there's any way of solving it the way I've been trying before I give up and do the other way. Silly I know.

But do you already not have a page for the conversion just for this file?

ron_darrell

Yes but after the conversion it calls the standard import routine that reads in from a CSV file. I get what you're saying. It's stubbornness more than anything plus it should work so it bugs me that it doesn't. Does that make any sense?

Seth Brundle

Just out of curiosity, why have the savechanges part when closing excel (given that the workbook is already closed?

appExcel.Close "savechanges:=true"

ron_darrell

Don't know really. appExcel.Close may not really work as appExcel.quit is (apparently) the way to close Excel (though on other articles I read it was appExcel.Close). That code comes closest to doing what I want but still leaves multiple excel processes running on the webserver. Do you think taking out the parameter will make any difference?