Closing Excel on server

Discussion in 'ASP .Net' started by sweeney_jones@hotmail.com, Sep 5, 2005.

  1. Guest

    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
    , Sep 5, 2005
    #1
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mark

    closing excel

    Mark, Apr 13, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    1,993
  2. Winshent
    Replies:
    0
    Views:
    1,948
    Winshent
    Nov 3, 2004
  3. =?Utf-8?B?Vk1J?=

    Closing popup window when closing parent window?

    =?Utf-8?B?Vk1J?=, Feb 14, 2007, in forum: ASP .Net
    Replies:
    3
    Views:
    649
    Thomas Hansen
    Feb 15, 2007
  4. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,495
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  5. thomas
    Replies:
    0
    Views:
    225
    thomas
    Oct 23, 2003
Loading...

Share This Page