Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.close problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.close problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.close problem
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.close problem
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.close problem
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.close problem
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.close problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveWorkbook.close problem | Excel Programming | |||
In Before Close Sub ActiveWorkBook.Close(False) repeat procedure | Excel Programming | |||
Problem with Activeworkbook.Close SaveChanges:=False | Excel Programming | |||
ActiveWorkbook.Close | Excel Programming | |||
ActiveWorkbook.Close | Excel Programming |