+ Reply to Thread
Results 1 to 5 of 5

CreateObject("Excel.Application")

  1. #1
    Olivia Towery
    Guest

    CreateObject("Excel.Application")

    Each month this object runs in an asp application to create a workbook from
    5 separate tables in a SQL 7.0 db. The app is great and performs
    successfully with the exception if the file is large - maybe 1000 lines per
    spreadsheet - 800KB. The last lines of code are as follows:

    xls.ActiveWorkbook.SaveAs server.MapPath(".") & "\" & fName
    xls.Quit

    Set xls = Nothing ' Release the object variable.
    set rsTbl1 = Nothing

    Response.Redirect ("https://www.foo.org/" & strChap & "/admin/" &
    strRedirect & ".asp?File=" & strfile & "")

    Once it completes the SaveAs command, the app hangs. It never quits or goes
    to the response.redirect. Does anyone no how to fix this issue? Or if I
    should be in another newsgroup?

    Olivia



  2. #2
    Jim Cone
    Guest

    Re: CreateObject("Excel.Application")

    Olivia,

    Are you closing the active workbook?
    Save As does not close both workbooks - only one.
    When you close a workbook or quit Excel, Excel throws up a
    message asking if you want to save any open workbooks.
    It must be responded to.
    To avoid this...Active.Workbook.Close SaveChanges:=False ' or True
    or possibly xls.DisplayAlerts = False

    Also, it is easy to leave orphan references in Excel which can prevent
    Excel from closing.
    Are you setting all object variables to nothing? (child then parent)
    It's usually best to avoid references to ActiveSomething, instead use
    object references to refer to the ActiveSheet, ActiveWorkbook, ActiveCell
    and all others.
    Avoid the use of the "With" construct.
    Finally, thought it appears you are getting away with it, I would not use
    xls as the application object reference.

    Regards,
    Jim Cone
    San Francisco, USA
    http://www.realezsites.com/bus/primitivesoftware


    "Olivia Towery"
    <[email protected]>
    wrote in message
    Each month this object runs in an asp application to create a workbook from
    5 separate tables in a SQL 7.0 db. The app is great and performs
    successfully with the exception if the file is large - maybe 1000 lines per
    spreadsheet - 800KB. The last lines of code are as follows:

    xls.ActiveWorkbook.SaveAs server.MapPath(".") & "\" & fName
    xls.Quit
    Set xls = Nothing ' Release the object variable.
    set rsTbl1 = Nothing

    Response.Redirect ("https://www.foo.org/" & strChap & "/admin/" &
    strRedirect & ".asp?File=" & strfile & "")

    Once it completes the SaveAs command, the app hangs. It never quits or goes
    to the response.redirect. Does anyone no how to fix this issue? Or if I
    should be in another newsgroup?
    Olivia



  3. #3
    Olivia Towery
    Guest

    Re: CreateObject("Excel.Application")

    Yes, I have the xls.DisplayAlerts = False statement.

    The app works great on most workbooks. The problem only occurs when the
    workbook is larger than say 1000 lines per sheet. When it is less than
    that, the app quits and redirects as it should.

    Any other suggestions are greatly appreciated.


    "Jim Cone" <[email protected]> wrote in message
    news:[email protected]...
    > Olivia,
    >
    > Are you closing the active workbook?
    > Save As does not close both workbooks - only one.
    > When you close a workbook or quit Excel, Excel throws up a
    > message asking if you want to save any open workbooks.
    > It must be responded to.
    > To avoid this...Active.Workbook.Close SaveChanges:=False ' or True
    > or possibly xls.DisplayAlerts = False
    >
    > Also, it is easy to leave orphan references in Excel which can prevent
    > Excel from closing.
    > Are you setting all object variables to nothing? (child then parent)
    > It's usually best to avoid references to ActiveSomething, instead use
    > object references to refer to the ActiveSheet, ActiveWorkbook, ActiveCell
    > and all others.
    > Avoid the use of the "With" construct.
    > Finally, thought it appears you are getting away with it, I would not use
    > xls as the application object reference.
    >
    > Regards,
    > Jim Cone
    > San Francisco, USA
    > http://www.realezsites.com/bus/primitivesoftware
    >
    >
    > "Olivia Towery"
    > <[email protected]>
    > wrote in message
    > Each month this object runs in an asp application to create a workbook

    from
    > 5 separate tables in a SQL 7.0 db. The app is great and performs
    > successfully with the exception if the file is large - maybe 1000 lines

    per
    > spreadsheet - 800KB. The last lines of code are as follows:
    >
    > xls.ActiveWorkbook.SaveAs server.MapPath(".") & "\" & fName
    > xls.Quit
    > Set xls = Nothing ' Release the object variable.
    > set rsTbl1 = Nothing
    >
    > Response.Redirect ("https://www.foo.org/" & strChap & "/admin/" &
    > strRedirect & ".asp?File=" & strfile & "")
    >
    > Once it completes the SaveAs command, the app hangs. It never quits or

    goes
    > to the response.redirect. Does anyone no how to fix this issue? Or if I
    > should be in another newsgroup?
    > Olivia
    >
    >




  4. #4
    Jim Cone
    Guest

    Re: CreateObject("Excel.Application")

    Olivia,
    Out of suggestions, however, when the app does work correctly and quits,
    does Task Manager still show any Excel.exe processes running?
    Regards,
    Jim Cone
    San Francisco, USA



    http://www.officeletter.com/blink/specialsort.html
    "Olivia Towery"
    <[email protected]>
    wrote in message
    Yes, I have the xls.DisplayAlerts = False statement.
    The app works great on most workbooks. The problem only occurs when the
    workbook is larger than say 1000 lines per sheet. When it is less than
    that, the app quits and redirects as it should.
    Any other suggestions are greatly appreciated.



    "Jim Cone" <[email protected]>
    wrote in message
    > Olivia,
    >
    > Are you closing the active workbook?
    > Save As does not close both workbooks - only one.
    > When you close a workbook or quit Excel, Excel throws up a
    > message asking if you want to save any open workbooks.
    > It must be responded to.
    > To avoid this...Active.Workbook.Close SaveChanges:=False ' or True
    > or possibly xls.DisplayAlerts = False
    >
    > Also, it is easy to leave orphan references in Excel which can prevent
    > Excel from closing.
    > Are you setting all object variables to nothing? (child then parent)
    > It's usually best to avoid references to ActiveSomething, instead use
    > object references to refer to the ActiveSheet, ActiveWorkbook, ActiveCell
    > and all others.
    > Avoid the use of the "With" construct.
    > Finally, though it appears you are getting away with it, I would not use
    > xls as the application object reference.
    >
    > Regards,
    > Jim Cone
    > San Francisco, USA
    > http://www.realezsites.com/bus/primitivesoftware
    >
    >
    > "Olivia Towery"
    > <[email protected]>
    > wrote in message
    > Each month this object runs in an asp application to create a workbook

    from
    > 5 separate tables in a SQL 7.0 db. The app is great and performs
    > successfully with the exception if the file is large - maybe 1000 lines

    per
    > spreadsheet - 800KB. The last lines of code are as follows:
    >
    > xls.ActiveWorkbook.SaveAs server.MapPath(".") & "\" & fName
    > xls.Quit
    > Set xls = Nothing ' Release the object variable.
    > set rsTbl1 = Nothing
    >
    > Response.Redirect ("https://www.foo.org/" & strChap & "/admin/" &
    > strRedirect & ".asp?File=" & strfile & "")
    >
    > Once it completes the SaveAs command, the app hangs. It never quits or

    goes
    > to the response.redirect. Does anyone no how to fix this issue? Or if I
    > should be in another newsgroup?
    > Olivia
    >
    >




  5. #5
    Olivia Towery
    Guest

    Re: CreateObject("Excel.Application")

    no - the task manager only shows that if the client cancels the process =
    or the file is saved and is a large size. Otherwise it works as it =
    should.

    Thanks for trying.

    "Jim Cone" <[email protected]> wrote in message
    news:[email protected]...
    > Olivia,
    > Out of suggestions, however, when the app does work correctly and quits,
    > does Task Manager still show any Excel.exe processes running?
    > Regards,
    > Jim Cone
    > San Francisco, USA
    >
    >
    >
    > http://www.officeletter.com/blink/specialsort.html
    > "Olivia Towery"
    > <[email protected]>
    > wrote in message
    > Yes, I have the xls.DisplayAlerts = False statement.
    > The app works great on most workbooks. The problem only occurs when the
    > workbook is larger than say 1000 lines per sheet. When it is less than
    > that, the app quits and redirects as it should.
    > Any other suggestions are greatly appreciated.
    >
    >
    >
    > "Jim Cone" <[email protected]>
    > wrote in message
    > > Olivia,
    > >
    > > Are you closing the active workbook?
    > > Save As does not close both workbooks - only one.
    > > When you close a workbook or quit Excel, Excel throws up a
    > > message asking if you want to save any open workbooks.
    > > It must be responded to.
    > > To avoid this...Active.Workbook.Close SaveChanges:=False ' or True
    > > or possibly xls.DisplayAlerts = False
    > >
    > > Also, it is easy to leave orphan references in Excel which can prevent
    > > Excel from closing.
    > > Are you setting all object variables to nothing? (child then parent)
    > > It's usually best to avoid references to ActiveSomething, instead use
    > > object references to refer to the ActiveSheet, ActiveWorkbook,

    ActiveCell
    > > and all others.
    > > Avoid the use of the "With" construct.
    > > Finally, though it appears you are getting away with it, I would not use
    > > xls as the application object reference.
    > >
    > > Regards,
    > > Jim Cone
    > > San Francisco, USA
    > > http://www.realezsites.com/bus/primitivesoftware
    > >
    > >
    > > "Olivia Towery"
    > > <[email protected]>
    > > wrote in message
    > > Each month this object runs in an asp application to create a workbook

    > from
    > > 5 separate tables in a SQL 7.0 db. The app is great and performs
    > > successfully with the exception if the file is large - maybe 1000 lines

    > per
    > > spreadsheet - 800KB. The last lines of code are as follows:
    > >
    > > xls.ActiveWorkbook.SaveAs server.MapPath(".") & "\" & fName
    > > xls.Quit
    > > Set xls = Nothing ' Release the object variable.
    > > set rsTbl1 = Nothing
    > >
    > > Response.Redirect ("https://www.foo.org/" & strChap & "/admin/" &
    > > strRedirect & ".asp?File=" & strfile & "")
    > >
    > > Once it completes the SaveAs command, the app hangs. It never quits or

    > goes
    > > to the response.redirect. Does anyone no how to fix this issue? Or if

    I
    > > should be in another newsgroup?
    > > Olivia
    > >
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1