+ Reply to Thread
Results 1 to 4 of 4

can't close files opened in an excel application??

  1. #1
    urgent
    Guest

    can't close files opened in an excel application??

    i have an application which first checks if there is an excel file in certain
    location, and then call DTS package to run. but my problem is everytime when
    the application runs through, i have one excel file open and i couldn't
    delete or remove...after running a few times, they take up all resource and
    then got error: Subscribe out of range..

    i tried to close workbooks, but only the application workbook found, other
    files' workbooks can't be located! anybody has any clues? can i use process
    to kill? but if so, no process such object in exce.

  2. #2
    Robin Hammond
    Guest

    Re: can't close files opened in an excel application??

    Do you mean an Excel file, or an instance of Excel?

    If the latter, then you should create the Excel instance in your code, tell
    Excel to open the file, do your processing, close the file, destroy the
    instance of Excel.

    Set a reference to Excel in your app, then it would go something like this

    Dim oXL as Excel.Application
    Dim wInput as Workbook
    set oXL = CreateObject("Excel.Application")
    set wInput = oXL.Workbooks.Open ("your file name")
    'do your processing
    wInput.Close False
    'close the app
    oXL.Quit
    'make sure it's gone
    Set oXL = Nothing

    Robin Hammond
    www.enhanceddatasystems.com

    "urgent" <[email protected]> wrote in message
    news:[email protected]...
    >i have an application which first checks if there is an excel file in
    >certain
    > location, and then call DTS package to run. but my problem is everytime
    > when
    > the application runs through, i have one excel file open and i couldn't
    > delete or remove...after running a few times, they take up all resource
    > and
    > then got error: Subscribe out of range..
    >
    > i tried to close workbooks, but only the application workbook found, other
    > files' workbooks can't be located! anybody has any clues? can i use
    > process
    > to kill? but if so, no process such object in exce.




  3. #3
    urgent
    Guest

    Re: can't close files opened in an excel application??

    i tried as you said, but doesn't work. here are my codes:

    -------------------------------------------------------------------------
    'create a DTS package
    Set D.pkg = New DTS.Package2
    'find if the excel file exists
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    If fs.fileexists("\\Jeeves\Users\Transfer\Disaster\TAX" & sDate &
    ".xls") = True Then
    D.pkg.Connections.Item(1).DataSource =
    "\\Jeeves\Users\Transfer\Disaster\TAX" & sDate & ".xls"
    Else
    MsgBox "not found \\Jeeves\Users\Transfer\Disaster\TAX" & sDate &
    ".xls"

    End If
    'run the DTS with in Excel
    D.pkg.Execute

    --------------------------------------------------------------
    but for somehow reason, these excel files stay as VBAProjects in my
    application everytime when i open the application or close. i can't close
    them and workbooks can't identify them. if i keep running my macro then i
    got 'Subscribe out of range' - which i guess because of memeory used up. the
    interesting thing is if i don't open the application directly but open excel
    program, then i wouldn't see those excel files...

    do you have any clues...thanks.

    --------------------------------------------------------

    "Robin Hammond" wrote:

    > Do you mean an Excel file, or an instance of Excel?
    >
    > If the latter, then you should create the Excel instance in your code, tell
    > Excel to open the file, do your processing, close the file, destroy the
    > instance of Excel.
    >
    > Set a reference to Excel in your app, then it would go something like this
    >
    > Dim oXL as Excel.Application
    > Dim wInput as Workbook
    > set oXL = CreateObject("Excel.Application")
    > set wInput = oXL.Workbooks.Open ("your file name")
    > 'do your processing
    > wInput.Close False
    > 'close the app
    > oXL.Quit
    > 'make sure it's gone
    > Set oXL = Nothing
    >
    > Robin Hammond
    > www.enhanceddatasystems.com
    >
    > "urgent" <[email protected]> wrote in message
    > news:[email protected]...
    > >i have an application which first checks if there is an excel file in
    > >certain
    > > location, and then call DTS package to run. but my problem is everytime
    > > when
    > > the application runs through, i have one excel file open and i couldn't
    > > delete or remove...after running a few times, they take up all resource
    > > and
    > > then got error: Subscribe out of range..
    > >
    > > i tried to close workbooks, but only the application workbook found, other
    > > files' workbooks can't be located! anybody has any clues? can i use
    > > process
    > > to kill? but if so, no process such object in exce.

    >
    >
    >


  4. #4
    Robin Hammond
    Guest

    Re: can't close files opened in an excel application??

    I'm afraid not. I've never tried a DTS on an Excel file, only in isolation
    within SQL.

    I don't know if this will work but you could try this at the end of your
    process and see if it helps.

    Dim oXL as Excel.Application
    on error resume next
    set oXL = GetObject(,"Excel.Application")
    if not oxl is nothing then _
    oxl.workbooks("TAX" & sDate & ".xls").close (false)
    on error goto 0

    Robin Hammond
    www.enhanceddatasystems.com

    "urgent" <[email protected]> wrote in message
    news:[email protected]...
    >i tried as you said, but doesn't work. here are my codes:
    >
    > -------------------------------------------------------------------------
    > 'create a DTS package
    > Set D.pkg = New DTS.Package2
    > 'find if the excel file exists
    > Dim fs As Object
    > Set fs = CreateObject("Scripting.FileSystemObject")
    > If fs.fileexists("\\Jeeves\Users\Transfer\Disaster\TAX" & sDate &
    > ".xls") = True Then
    > D.pkg.Connections.Item(1).DataSource =
    > "\\Jeeves\Users\Transfer\Disaster\TAX" & sDate & ".xls"
    > Else
    > MsgBox "not found \\Jeeves\Users\Transfer\Disaster\TAX" & sDate &
    > ".xls"
    >
    > End If
    > 'run the DTS with in Excel
    > D.pkg.Execute
    >
    > --------------------------------------------------------------
    > but for somehow reason, these excel files stay as VBAProjects in my
    > application everytime when i open the application or close. i can't close
    > them and workbooks can't identify them. if i keep running my macro then i
    > got 'Subscribe out of range' - which i guess because of memeory used up.
    > the
    > interesting thing is if i don't open the application directly but open
    > excel
    > program, then i wouldn't see those excel files...
    >
    > do you have any clues...thanks.
    >
    > --------------------------------------------------------
    >
    > "Robin Hammond" wrote:
    >
    >> Do you mean an Excel file, or an instance of Excel?
    >>
    >> If the latter, then you should create the Excel instance in your code,
    >> tell
    >> Excel to open the file, do your processing, close the file, destroy the
    >> instance of Excel.
    >>
    >> Set a reference to Excel in your app, then it would go something like
    >> this
    >>
    >> Dim oXL as Excel.Application
    >> Dim wInput as Workbook
    >> set oXL = CreateObject("Excel.Application")
    >> set wInput = oXL.Workbooks.Open ("your file name")
    >> 'do your processing
    >> wInput.Close False
    >> 'close the app
    >> oXL.Quit
    >> 'make sure it's gone
    >> Set oXL = Nothing
    >>
    >> Robin Hammond
    >> www.enhanceddatasystems.com
    >>
    >> "urgent" <[email protected]> wrote in message
    >> news:[email protected]...
    >> >i have an application which first checks if there is an excel file in
    >> >certain
    >> > location, and then call DTS package to run. but my problem is everytime
    >> > when
    >> > the application runs through, i have one excel file open and i couldn't
    >> > delete or remove...after running a few times, they take up all resource
    >> > and
    >> > then got error: Subscribe out of range..
    >> >
    >> > i tried to close workbooks, but only the application workbook found,
    >> > other
    >> > files' workbooks can't be located! anybody has any clues? can i use
    >> > process
    >> > to kill? but if so, no process such object in exce.

    >>
    >>
    >>




+ 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