Won't close w/routine

[Ronbo]

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]

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

[Ronbo]

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
>