[thelist] [ASP] Excel COM object reference

[Jerry Scannell]

Jacques,

There are 2 process flows relative to Excel interfacing from a web page.
One centers on opening and modifying an existing .xls and the other is in
creating a new one.  here's code for both:

Creating a new .xls:
Dim xls
Set xls = CreateObject("Excel.Application")

with xls
   .Application.Visible = False           ' This is so you can't see it.
make it True if you want to see it
   .Workbooks.Add                             ' Adds a blank sheet set

   .Sheets ( "Sheet1" ).Select
   .ActiveSheet.Cells(1,1).Value = "Now isnt this fun"     ' Write the text
   .SaveAs "filename.xls"                                  ' Save the the
excelsheet
   .Application.Quit                                           ' This is
something you forgot in your example ;)
End With

Set xls = Nothing                                        ' Release the
object variable.


Updating an existing .xls:
Const xlReadWrite = 3

Dim xls
Set xls = CreateObject("Excel.Application")

with xls
   .Application.Visible = False           ' This is so you can't see it.
make it True if you want to see it
   .Workbooks.Open "filename.xls", xlReadWrite, False   '  If you don't do
this, then you can't save it.

   .Sheets ( "DataStuff" ).Select       ' You can name the individual sheets
to something other than "Sheet1", "Sheet2", etc.
   .Range  ( "A1:N26"   ).Select      ' Change as needed to select all the
old data
   .Selection.EntireRow.Delete       ' Delete previously entered data if
desired

   .ActiveSheet.Cells(1,1).Value = "Now isnt this fun"     ' Write the new
text
   .Save
   .ActiveWorkBook.PrintOut 1          ' This will print the sheet to the
printer if you want.
   .Application.Quit                                           ' This is
something you forgot in your example ;)
End With

Set xls = Nothing                                        ' Release the
object variable.


Jerry
----- Original Message -----
From: "Jacques Capesius" <jacques_capesius at cnt.com>
To: "'Evolt List'" <thelist at lists.evolt.org>
Sent: Thursday, November 07, 2002 9:22 AM
Subject: [thelist] [ASP] Excel COM object reference


> I have the following line of ASP code that creates an excel spreadsheet
and
> saves it to the harddrive.
>
> Dim objSpreadsheet
> Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")
> objSpreadsheet.Cells(1,1).Value = "hoser"
> objSpreadsheet.ActiveSheet.Export("c:\inetpub\jacquesdev\hoser.xls")
> Set objSpreadsheet = Nothing
>
> The code runs just fine, but when I go to open up the spreadsheet, I get a
> message along the lines of "this spreadsheet has been locked for editing
by
> 'another user'" and I can only open a read-only file of this spreadsheet.
> When I try to delete this spreadsheet, I get a sharing violation.
> Furthermore, in the task manager, there is an excel.exe process open that
> wasn't open before, and when I try to kill the process, I get an access
> denied. I try to stop and start the WWW Publishing Service, thinking that
> might free the file up, but still, it won't let me delete this pesky
little
> critter. The only way I have been able to get rid of the file is by
> rebooting the machine.
>
> I assume there's some excel COM object setting that changes the excel
sheet
> from 'locked for editing' to 'go ahead, delete it. see if I care', but try
> as I may, I can't seem to find any information on it. So... I'm turning to
> you wizards. :-)
>
> Is there some way to programmatically change the porperties of the
> spreadsheet I'm exporting to the drive so that I can delete it without
> having to do a hard reboot on the system?
>
> thanks!
>
> -jacques :)
> --
> For unsubscribe and other options, including
> the Tip Harvester and archive of thelist go to:
> http://lists.evolt.org Workers of the Web, evolt !
>