Closing Excel on server


S

sweeney_jones

Guys,

I am using this code to write data into an Excel file from an ASP.NET
page. Excel is launched on the server, data is dumped in from arrays,
then the file is saved to the required location. (Thanks to Steve Orr
for most of the code)

This all works fine, but I am having problems with 2 things. Firstly, I
wanted to highlight the first sheet before I save and close, this
doesn't seem to be working. Secondly, Excel is not closing on the
server. In task manager on the server I can see the Excel.exe pocess
still running under the usersname.

Could anybody help with this please ? I have posted my code below.

Thanks
Bob

Dim oExcel As New Excel.Application
Dim oBooks As Excel.Workbooks, oBook As Excel.Workbook
Dim oSheets As Excel.Sheets, oSheet As Excel.Worksheet
Dim oCells As Excel.Range
Dim sFile As String, sTemplate As String
sFile = Server.MapPath(Request.ApplicationPath) & "RESULTS\" &
CType(Qid, String) & " - " & CType(Session("ReportName"), String) & "
RESULTS.xls"
'sTemplate = Server.MapPath(Request.ApplicationPath) & _
' "\HPTResults.xlt"

Dim rFile As String 'Results File
rFile = "\\bls2mbr25\RESULTS\" & CType(Qid, String) & " - " &
CType(Session("ReportName"), String) & " RESULTS.xls"

oExcel.Visible = False : oExcel.DisplayAlerts = False

'Start a new workbook
oBooks = oExcel.Workbooks
oBooks.Open(Server.MapPath(Request.ApplicationPath) &
"\HPTResults.xls") 'Load template
oBook = oBooks.Item(1)
oSheets = oBook.Worksheets
oSheet = CType(oSheets.Item("rawdata"), Excel.Worksheet)

'Do all the data export here.
'Export Team Answers
Dim x As Integer
Dim y As Integer
Dim range As String
For x = 0 To Answers.GetUpperBound(0)
y = x + 2
range = "A" & y & ":D" & y
oSheet.Range(range).Value = Answers(x)
Next

'Export TeamLeader Answers
For x = 0 To Leader.GetUpperBound(0)
y = x + 2
range = "E" & y & ":G" & y
oSheet.Range(range).Value = Leader(x)
Next

'Activate sheet, NOT WORKING
With oBook.Worksheets("Team Average Checklist")
.Activate()
oExcel.Goto(Reference:=.Range("A1"), scroll:=True)
End With

'oBook.Sheets("Team Average Checklist").activate()

oSheet.SaveAs(sFile) 'Save file
oBook.Close()

'Quit Excel and thoroughly deallocate everything
'NOT WORKING, its leaving excel process open on server.
oExcel.Quit()

ReleaseComObject(oSheet)
ReleaseComObject(oSheets) : ReleaseComObject(oBook)
ReleaseComObject(oBooks) : ReleaseComObject(oExcel)
oExcel = Nothing : oBooks = Nothing : oBook = Nothing
oSheets = Nothing : oSheet = Nothing
System.GC.Collect()

HyperLink1.Visible = True
HyperLink1.NavigateUrl = rFile

End Sub
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top