Grateful for any advice on an ActiveWorkbook.close problem.

I am running a macro that opens each excel file in a folder in turn. The
macro then calls another macro that deletes the links on a worksheet in that
file, then it runs a function that calculates averages using data on that
worksheet and plugs resulting data into cells on that worksheet, then various
items of data are uploaded to a new Access database. The focus then returns
to the original macro that then closes the workbook; using
Activeworkbook.close savechanges:=false

Everytime it stops at this point and shows the standard Microsoft runtime
error 1004. If I click on debug button on that message the file then closes?
I have tried hard coding the filename and it still produces the same error.

If I comment out the delete links part of the macro it works fine, which to
me indicates that the problem is connected with deleting the links. Does
anyone have any ideas what could be causing this error.


--
with kind regards

Spike

Don't use activeworkbook. Instead set a variable to the workbook when you
open it

Set bk = workbooks.open(filename:="book1.xls")

'you code here

bk.close savechanges:=false


"Gary''s Student" wrote:

> Just make sure that when you are closing the ActiveWorkbook, that it is not
> the workbook in which the macro resides. This would effectively kill the
> macro.
> --
> Gary''s Student - gsnu200854
>
>
> "Spike" wrote:
>
> > Grateful for any advice on an ActiveWorkbook.close problem.
> >
> > I am running a macro that opens each excel file in a folder in turn. The
> > macro then calls another macro that deletes the links on a worksheet in that
> > file, then it runs a function that calculates averages using data on that
> > worksheet and plugs resulting data into cells on that worksheet, then various
> > items of data are uploaded to a new Access database. The focus then returns
> > to the original macro that then closes the workbook; using
> > Activeworkbook.close savechanges:=false
> >
> > Everytime it stops at this point and shows the standard Microsoft runtime
> > error 1004. If I click on debug button on that message the file then closes?
> > I have tried hard coding the filename and it still produces the same error.
> >
> > If I comment out the delete links part of the macro it works fine, which to
> > me indicates that the problem is connected with deleting the links. Does
> > anyone have any ideas what could be causing this error.
> >
> >
> > --
> > with kind regards
> >
> > Spike

A very good suggestion!
--
Gary''s Student - gsnu200854


"Joel" wrote:

> Don't use activeworkbook. Instead set a variable to the workbook when you
> open it
>
> Set bk = workbooks.open(filename:="book1.xls")
>
> 'you code here
>
> bk.close savechanges:=false
>
>
> "Gary''s Student" wrote:
>
> > Just make sure that when you are closing the ActiveWorkbook, that it is not
> > the workbook in which the macro resides. This would effectively kill the
> > macro.
> > --
> > Gary''s Student - gsnu200854
> >
> >
> > "Spike" wrote:
> >
> > > Grateful for any advice on an ActiveWorkbook.close problem.
> > >
> > > I am running a macro that opens each excel file in a folder in turn. The
> > > macro then calls another macro that deletes the links on a worksheet in that
> > > file, then it runs a function that calculates averages using data on that
> > > worksheet and plugs resulting data into cells on that worksheet, then various
> > > items of data are uploaded to a new Access database. The focus then returns
> > > to the original macro that then closes the workbook; using
> > > Activeworkbook.close savechanges:=false
> > >
> > > Everytime it stops at this point and shows the standard Microsoft runtime
> > > error 1004. If I click on debug button on that message the file then closes?
> > > I have tried hard coding the filename and it still produces the same error.
> > >
> > > If I comment out the delete links part of the macro it works fine, which to
> > > me indicates that the problem is connected with deleting the links. Does
> > > anyone have any ideas what could be causing this error.
> > >
> > >
> > > --
> > > with kind regards
> > >
> > > Spike

Thanks, yes i have tried setting a variable to hold the workbook name and
close that variable which produces the same error
--
with kind regards

Spike


"Gary''s Student" wrote:

> A very good suggestion!
> --
> Gary''s Student - gsnu200854
>
>
> "Joel" wrote:
>
> > Don't use activeworkbook. Instead set a variable to the workbook when you
> > open it
> >
> > Set bk = workbooks.open(filename:="book1.xls")
> >
> > 'you code here
> >
> > bk.close savechanges:=false
> >
> >
> > "Gary''s Student" wrote:
> >
> > > Just make sure that when you are closing the ActiveWorkbook, that it is not
> > > the workbook in which the macro resides. This would effectively kill the
> > > macro.
> > > --
> > > Gary''s Student - gsnu200854
> > >
> > >
> > > "Spike" wrote:
> > >
> > > > Grateful for any advice on an ActiveWorkbook.close problem.
> > > >
> > > > I am running a macro that opens each excel file in a folder in turn. The
> > > > macro then calls another macro that deletes the links on a worksheet in that
> > > > file, then it runs a function that calculates averages using data on that
> > > > worksheet and plugs resulting data into cells on that worksheet, then various
> > > > items of data are uploaded to a new Access database. The focus then returns
> > > > to the original macro that then closes the workbook; using
> > > > Activeworkbook.close savechanges:=false
> > > >
> > > > Everytime it stops at this point and shows the standard Microsoft runtime
> > > > error 1004. If I click on debug button on that message the file then closes?
> > > > I have tried hard coding the filename and it still produces the same error.
> > > >
> > > > If I comment out the delete links part of the macro it works fine, which to
> > > > me indicates that the problem is connected with deleting the links. Does
> > > > anyone have any ideas what could be causing this error.
> > > >
> > > >
> > > > --
> > > > with kind regards
> > > >
> > > > Spike

My guess is the file has already been closed. I would need to see the code.
One thing you may try is to step thought the code using F8. I would set a
watch on the object as you step though the code and see which instruction
cuases the watch item to be set to nothing.

for this instruction
Set bk = workbooks.open(filename:="book1.xls")

Highlight bk with mouse. then right click hihglighted variable bk and
select ADD WATCH. The when above instruction is executed you can see the
variable bk and see when the object is no longer contains any information.

"Spike" wrote:

> Thanks, yes i have tried setting a variable to hold the workbook name and
> close that variable which produces the same error
> --
> with kind regards
>
> Spike
>
>
> "Gary''s Student" wrote:
>
> > A very good suggestion!
> > --
> > Gary''s Student - gsnu200854
> >
> >
> > "Joel" wrote:
> >
> > > Don't use activeworkbook. Instead set a variable to the workbook when you
> > > open it
> > >
> > > Set bk = workbooks.open(filename:="book1.xls")
> > >
> > > 'you code here
> > >
> > > bk.close savechanges:=false
> > >
> > >
> > > "Gary''s Student" wrote:
> > >
> > > > Just make sure that when you are closing the ActiveWorkbook, that it is not
> > > > the workbook in which the macro resides. This would effectively kill the
> > > > macro.
> > > > --
> > > > Gary''s Student - gsnu200854
> > > >
> > > >
> > > > "Spike" wrote:
> > > >
> > > > > Grateful for any advice on an ActiveWorkbook.close problem.
> > > > >
> > > > > I am running a macro that opens each excel file in a folder in turn. The
> > > > > macro then calls another macro that deletes the links on a worksheet in that
> > > > > file, then it runs a function that calculates averages using data on that
> > > > > worksheet and plugs resulting data into cells on that worksheet, then various
> > > > > items of data are uploaded to a new Access database. The focus then returns
> > > > > to the original macro that then closes the workbook; using
> > > > > Activeworkbook.close savechanges:=false
> > > > >
> > > > > Everytime it stops at this point and shows the standard Microsoft runtime
> > > > > error 1004. If I click on debug button on that message the file then closes?
> > > > > I have tried hard coding the filename and it still produces the same error.
> > > > >
> > > > > If I comment out the delete links part of the macro it works fine, which to
> > > > > me indicates that the problem is connected with deleting the links. Does
> > > > > anyone have any ideas what could be causing this error.
> > > > >
> > > > >
> > > > > --
> > > > > with kind regards
> > > > >
> > > > > Spike

Thank you all for your help. It appers that the workbook/s the macro is
opening are very heavily locked down, have made the necessary alterations and
now works fine.
--
with kind regards

Spike


"Alan McQuaid via OfficeKB.com" wrote:

> Hi Spike,
>
> It's possible that one of the workbooks has an Open/Close event macro in its
> workbook module.
>
> Try inserting the below messages above and below your ActiveWorkbook.close
> line
>
> Application.EnableEvents = False ' Stops any Auto events from running on
> other macro workbooks
> Activeworkbook.close savechanges:=false
> Application.EnableEvents = True ' Re-enables the events
>
> Alan
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200905/1
>
>