+ Reply to Thread
Results 1 to 3 of 3

Won't close w/routine

  1. #1
    Ronbo
    Guest

    Won't close w/routine

    With help from the experts here I have come up with the following routine
    that creates a copy of the open workbook then performs calculations and saves
    the data then opens the original workbook and paste in the data and then
    closes and deletes the created workbook.

    The problem is that when it gets to "Workbooks(sName).Close
    SaveChanges:=False" in the Sub DeleteTempWB() it stops and shows a run time
    error #9 "script out of range". Yet when I run DeleteTempWB from within the
    workbook it works fine. I put the routine in differant areas and I get the
    same. If I include another routine/procedure from the workbook, they work
    fine. Any ideas would will be appreciated.

    The code is

    Sub ChangeToEvenProcedure()
    Application.Run "CreateTempWB" - creates a copy w/saveas
    Application.Run "ChangeToEven" - does the calculations and copies 12 cells
    Application.Run "OpenOriginal" - opens the original and pastes in the data
    Application.Run "DeleteTempWB" - close and delete the copy

    Sub OpenOriginal()
    sName = ThisWorkbook.FullName
    sName = Left(sName, Len(sName) - 5) & ".xls"
    Workbooks.Open sName
    Sheets("Sheet1").Select
    Range("C31").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    End Sub


    Sub DeleteTempWB()
    sName = ThisWorkbook.Name
    sName = Left(sName, Len(sName) - 4) & "X.xls"
    Workbooks(sName).Close SaveChanges:=False
    Kill ThisWorkbook.Path & "\" & sName
    End Sub




  2. #2
    Dave Peterson
    Guest

    Re: Won't close w/routine

    I'd check to see if there was a workbook named sName open at the time.

    dim wkbk as workbook
    ....
    set wkbk = nothing
    on error resume next
    set wkbk = workbooks(sname)
    on error goto 0
    if wkbk is nothing then
    msgbox sname & " isn't open"
    else
    wkbk.close savechanges:=false
    end if

    on error resume next
    Kill ThisWorkbook.Path & "\" & sName
    on error goto 0

    Just in case there isn't a file with that name.

    ==
    I'd add a couple of msgboxes to verify that sName was what I wanted when I got
    to each of those other lines, too.

    Ronbo wrote:
    >
    > With help from the experts here I have come up with the following routine
    > that creates a copy of the open workbook then performs calculations and saves
    > the data then opens the original workbook and paste in the data and then
    > closes and deletes the created workbook.
    >
    > The problem is that when it gets to "Workbooks(sName).Close
    > SaveChanges:=False" in the Sub DeleteTempWB() it stops and shows a run time
    > error #9 "script out of range". Yet when I run DeleteTempWB from within the
    > workbook it works fine. I put the routine in differant areas and I get the
    > same. If I include another routine/procedure from the workbook, they work
    > fine. Any ideas would will be appreciated.
    >
    > The code is
    >
    > Sub ChangeToEvenProcedure()
    > Application.Run "CreateTempWB" - creates a copy w/saveas
    > Application.Run "ChangeToEven" - does the calculations and copies 12 cells
    > Application.Run "OpenOriginal" - opens the original and pastes in the data
    > Application.Run "DeleteTempWB" - close and delete the copy
    >
    > Sub OpenOriginal()
    > sName = ThisWorkbook.FullName
    > sName = Left(sName, Len(sName) - 5) & ".xls"
    > Workbooks.Open sName
    > Sheets("Sheet1").Select
    > Range("C31").Select
    > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    > False, Transpose:=False
    > End Sub
    >
    > Sub DeleteTempWB()
    > sName = ThisWorkbook.Name
    > sName = Left(sName, Len(sName) - 4) & "X.xls"
    > Workbooks(sName).Close SaveChanges:=False
    > Kill ThisWorkbook.Path & "\" & sName
    > End Sub


    --

    Dave Peterson

  3. #3
    Ronbo
    Guest

    Re: Won't close w/routine

    Thanks a lot Dave. The idea to debug with the msgbox was perfect. It showed
    that the filename it was looking to delete did not exist. It was looking for
    XX rather than X, so I took the X out of the "DeleteTempWB" and it works
    perfect.



    "Dave Peterson" wrote:

    > I'd check to see if there was a workbook named sName open at the time.
    >
    > dim wkbk as workbook
    > ....
    > set wkbk = nothing
    > on error resume next
    > set wkbk = workbooks(sname)
    > on error goto 0
    > if wkbk is nothing then
    > msgbox sname & " isn't open"
    > else
    > wkbk.close savechanges:=false
    > end if
    >
    > on error resume next
    > Kill ThisWorkbook.Path & "\" & sName
    > on error goto 0
    >
    > Just in case there isn't a file with that name.
    >
    > ==
    > I'd add a couple of msgboxes to verify that sName was what I wanted when I got
    > to each of those other lines, too.
    >
    > Ronbo wrote:
    > >
    > > With help from the experts here I have come up with the following routine
    > > that creates a copy of the open workbook then performs calculations and saves
    > > the data then opens the original workbook and paste in the data and then
    > > closes and deletes the created workbook.
    > >
    > > The problem is that when it gets to "Workbooks(sName).Close
    > > SaveChanges:=False" in the Sub DeleteTempWB() it stops and shows a run time
    > > error #9 "script out of range". Yet when I run DeleteTempWB from within the
    > > workbook it works fine. I put the routine in differant areas and I get the
    > > same. If I include another routine/procedure from the workbook, they work
    > > fine. Any ideas would will be appreciated.
    > >
    > > The code is
    > >
    > > Sub ChangeToEvenProcedure()
    > > Application.Run "CreateTempWB" - creates a copy w/saveas
    > > Application.Run "ChangeToEven" - does the calculations and copies 12 cells
    > > Application.Run "OpenOriginal" - opens the original and pastes in the data
    > > Application.Run "DeleteTempWB" - close and delete the copy
    > >
    > > Sub OpenOriginal()
    > > sName = ThisWorkbook.FullName
    > > sName = Left(sName, Len(sName) - 5) & ".xls"
    > > Workbooks.Open sName
    > > Sheets("Sheet1").Select
    > > Range("C31").Select
    > > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    > > False, Transpose:=False
    > > End Sub
    > >
    > > Sub DeleteTempWB()
    > > sName = ThisWorkbook.Name
    > > sName = Left(sName, Len(sName) - 4) & "X.xls"
    > > Workbooks(sName).Close SaveChanges:=False
    > > Kill ThisWorkbook.Path & "\" & sName
    > > End Sub

    >
    > --
    >
    > Dave Peterson
    >


+ 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