Excel Multiple Sheets Problem

[jennuhw]

I have created workbooks like this before, but I have never had the following problem. Here is all of my code:

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

objxlapp.visible = true

' open the spreadsheet file
set books = objXlApp.Workbooks.add() 'Open ("\\esp7\OppSite$\template.xls")

' get the worksheet
Set objXlSheet = books.Worksheets("Sheet1")

' read/write from the worksheet
objxlsheet.range("B6").value = "test"

books.Close True
objxlapp.quit
Set objXlApp = Nothing

I want to create a new Excel document. The first problem is that the line where it assigns a value to B6 locks up the code. If I take that line out, the page loads fine.

Secondly, before, I have always saved the file then opened it. Shouldn't I be able to see this file without saving it somewhere?

Thanks in advance!

 

[jennuhw]

I have even tried to open an existing workbook and add a value to the sheet, but it still locks up. Any one have any ideas? Thanks.

 

[jennuhw]

Nevermind on this. I have it copying an existing workbook and editing it. That works fine.

 

[theniteowl]

jennuhw, you are using Server.CreateObject to start the instance of the application which tells it to use Excel on the server side but then you are trying to make it visible.
You cannot open it server side and make it visible to you client-side.
You can create the file server side then deliver it to the client, but not the active object on the server.

If you use CreateObject on the server and for any reason the object fails to close out which your code above was doing when I tested it here, it leaves the object open on the server but running under the context of the IIS account.
You end up with multiple instances of an Excel object running and even from the server through the Task Manager you will not be able to shut them down as they are running under a different security ID than you are logged into the server with. Stopping and restarting IIS does not resolve it and the only solution to clean it up I have found is to restart the server.

If you need to create the excel file on the server-side you should use Server.CreateObject, manipulate the file programmatically and then close it out, then present the file to the client through the browser. If you need the window to come up where you can see it then drop Server from CreateObject and it will use the Excel instance on your own machine via ActiveX. You can set the window visible when the object is created locally as it is running under your own logon, not the web server.

I suspect that you only want the file to open locally so you can see immediately what it is doing while testing?
If so, switch to CreateObject for now and when you are done testing put Server.CreateObject back in and remove the line making the file visible.


Paranoid? ME?? WHO WANTS TO KNOW????

 

[jennuhw]

I have been watching for instances of Excel on the server and was getting a ton of them.
I did end up creating the file on the server, and opening it with a VB script like I had done in the past. I wasn't sure if I could have just done workbooks.add and had the user do what they want with the file. I will now have to create a script to clear up the files after X amount of days. Thanks.