|
Loop with Time
Hi, New to this programming hence I am looking for some help. I need to write a macro/vba in Excel which will open diferent workbooks according a specific time, i.e. Book 1 (open) then open Book 2 after 2 minutes, Book three after 4 minutes of the first (2 minutes of teh second). Your help will be much appreciated. Thanks,:) -- portucale ------------------------------------------------------------------------ portucale's Profile: http://www.excelforum.com/member.php...o&userid=35979 View this thread: http://www.excelforum.com/showthread...hreadid=557646 |
Loop with Time
|
Loop with Time
Hi Excelent,
I have been looking at your code. Since you have worked with the Application On Time command I wonder if you have run into the following difficulty: If you try to close the workbook that has the timer running from another workbook, programatically, the Before_Close event will run the Stoptimer but it won't kill it. It will refire again. This has caused me ample problems. Try it "excelent" wrote: Maby u can use this as inspiration http://pmexcelent.dk/FileMaster.xls |
Loop with Time
You should kill the timer. Chip Pearson explains it all on
http://www.cpearson.com/excel/ontime.htm -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Excelent, I have been looking at your code. Since you have worked with the Application On Time command I wonder if you have run into the following difficulty: If you try to close the workbook that has the timer running from another workbook, programatically, the Before_Close event will run the Stoptimer but it won't kill it. It will refire again. This has caused me ample problems. Try it "excelent" wrote: Maby u can use this as inspiration http://pmexcelent.dk/FileMaster.xls |
Loop with Time
nope cant say i have, i made this sheet for another, he newer told me of any
problems. Anyway i use this code to stop timer Private Sub Workbook_BeforeClose(Cancel As Boolean) StopTimer End Sub "Antonio" skrev: Hi Excelent, I have been looking at your code. Since you have worked with the Application On Time command I wonder if you have run into the following difficulty: If you try to close the workbook that has the timer running from another workbook, programatically, the Before_Close event will run the Stoptimer but it won't kill it. It will refire again. This has caused me ample problems. Try it "excelent" wrote: Maby u can use this as inspiration http://pmexcelent.dk/FileMaster.xls |
Loop with Time
Hi Bob,
I have done intensive work on this issue. I know how it is suposed to be done but it just does not work. It is a bug, an annoying one. Again, try closing a workbook with a live On Time procedure from a different workbook, if you rely on the Before_Close event it does not work. It never works. You have to call the kill timer sub independently and it does not always work. "Bob Phillips" wrote: You should kill the timer. Chip Pearson explains it all on http://www.cpearson.com/excel/ontime.htm -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Excelent, I have been looking at your code. Since you have worked with the Application On Time command I wonder if you have run into the following difficulty: If you try to close the workbook that has the timer running from another workbook, programatically, the Before_Close event will run the Stoptimer but it won't kill it. It will refire again. This has caused me ample problems. Try it "excelent" wrote: Maby u can use this as inspiration http://pmexcelent.dk/FileMaster.xls |
Loop with Time
As long as you call it with exactly the same scheduled time as when you set
it, it works fine. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Bob, I have done intensive work on this issue. I know how it is suposed to be done but it just does not work. It is a bug, an annoying one. Again, try closing a workbook with a live On Time procedure from a different workbook, if you rely on the Before_Close event it does not work. It never works. You have to call the kill timer sub independently and it does not always work. "Bob Phillips" wrote: You should kill the timer. Chip Pearson explains it all on http://www.cpearson.com/excel/ontime.htm -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Excelent, I have been looking at your code. Since you have worked with the Application On Time command I wonder if you have run into the following difficulty: If you try to close the workbook that has the timer running from another workbook, programatically, the Before_Close event will run the Stoptimer but it won't kill it. It will refire again. This has caused me ample problems. Try it "excelent" wrote: Maby u can use this as inspiration http://pmexcelent.dk/FileMaster.xls |
Loop with Time
I wish I were wrong, I really wish.
Try this: tta.xls Module1 Sub main() Workbooks("ttb.xls").Close SaveChanges:=False End Sub ttb.xls ThisWorkbook Public Sub Workbook_Open() timer End Sub Public Sub Workbook_BeforeClose(cancel As Boolean) cancel_timer End Sub ttb.xls Module1 Public rt As Double Public Sub timer() rt = Now + TimeValue("00:00:02") Application.OnTime rt, "timer" End Sub Public Sub cancel_timer() Application.OnTime rt, "timer", , False End Sub Do this now: Open ttb.xls Open tta.xls Assing sub main() in tta.xls to a button, click on the button, it does close ttb.xls but....ttb.xls reopens. This is not supposed to happen and it creates all sorts of problems. If you know that I am wrong please correct me, you would help me tremendously. There is a workaround, but it is not reliable. "Bob Phillips" wrote: As long as you call it with exactly the same scheduled time as when you set it, it works fine. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Bob, I have done intensive work on this issue. I know how it is suposed to be done but it just does not work. It is a bug, an annoying one. Again, try closing a workbook with a live On Time procedure from a different workbook, if you rely on the Before_Close event it does not work. It never works. You have to call the kill timer sub independently and it does not always work. "Bob Phillips" wrote: You should kill the timer. Chip Pearson explains it all on http://www.cpearson.com/excel/ontime.htm -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Excelent, I have been looking at your code. Since you have worked with the Application On Time command I wonder if you have run into the following difficulty: If you try to close the workbook that has the timer running from another workbook, programatically, the Before_Close event will run the Stoptimer but it won't kill it. It will refire again. This has caused me ample problems. Try it "excelent" wrote: Maby u can use this as inspiration http://pmexcelent.dk/FileMaster.xls |
Loop with Time
Well I have just tried it and it works fine for me. File B does not re-open
at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... I wish I were wrong, I really wish. Try this: tta.xls Module1 Sub main() Workbooks("ttb.xls").Close SaveChanges:=False End Sub ttb.xls ThisWorkbook Public Sub Workbook_Open() timer End Sub Public Sub Workbook_BeforeClose(cancel As Boolean) cancel_timer End Sub ttb.xls Module1 Public rt As Double Public Sub timer() rt = Now + TimeValue("00:00:02") Application.OnTime rt, "timer" End Sub Public Sub cancel_timer() Application.OnTime rt, "timer", , False End Sub Do this now: Open ttb.xls Open tta.xls Assing sub main() in tta.xls to a button, click on the button, it does close ttb.xls but....ttb.xls reopens. This is not supposed to happen and it creates all sorts of problems. If you know that I am wrong please correct me, you would help me tremendously. There is a workaround, but it is not reliable. "Bob Phillips" wrote: As long as you call it with exactly the same scheduled time as when you set it, it works fine. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Bob, I have done intensive work on this issue. I know how it is suposed to be done but it just does not work. It is a bug, an annoying one. Again, try closing a workbook with a live On Time procedure from a different workbook, if you rely on the Before_Close event it does not work. It never works. You have to call the kill timer sub independently and it does not always work. "Bob Phillips" wrote: You should kill the timer. Chip Pearson explains it all on http://www.cpearson.com/excel/ontime.htm -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Excelent, I have been looking at your code. Since you have worked with the Application On Time command I wonder if you have run into the following difficulty: If you try to close the workbook that has the timer running from another workbook, programatically, the Before_Close event will run the Stoptimer but it won't kill it. It will refire again. This has caused me ample problems. Try it "excelent" wrote: Maby u can use this as inspiration http://pmexcelent.dk/FileMaster.xls |
Loop with Time
It does not reopen if you run the sub in tta.xls manually.
If you run it programatically it does reopen. I have tried it on several computers Office 2003 and 07, all the same. Please let me know. Thanks. "Bob Phillips" wrote: Well I have just tried it and it works fine for me. File B does not re-open at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... I wish I were wrong, I really wish. Try this: tta.xls Module1 Sub main() Workbooks("ttb.xls").Close SaveChanges:=False End Sub ttb.xls ThisWorkbook Public Sub Workbook_Open() timer End Sub Public Sub Workbook_BeforeClose(cancel As Boolean) cancel_timer End Sub ttb.xls Module1 Public rt As Double Public Sub timer() rt = Now + TimeValue("00:00:02") Application.OnTime rt, "timer" End Sub Public Sub cancel_timer() Application.OnTime rt, "timer", , False End Sub Do this now: Open ttb.xls Open tta.xls Assing sub main() in tta.xls to a button, click on the button, it does close ttb.xls but....ttb.xls reopens. This is not supposed to happen and it creates all sorts of problems. If you know that I am wrong please correct me, you would help me tremendously. There is a workaround, but it is not reliable. "Bob Phillips" wrote: As long as you call it with exactly the same scheduled time as when you set it, it works fine. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Bob, I have done intensive work on this issue. I know how it is suposed to be done but it just does not work. It is a bug, an annoying one. Again, try closing a workbook with a live On Time procedure from a different workbook, if you rely on the Before_Close event it does not work. It never works. You have to call the kill timer sub independently and it does not always work. "Bob Phillips" wrote: You should kill the timer. Chip Pearson explains it all on http://www.cpearson.com/excel/ontime.htm -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Excelent, I have been looking at your code. Since you have worked with the Application On Time command I wonder if you have run into the following difficulty: If you try to close the workbook that has the timer running from another workbook, programatically, the Before_Close event will run the Stoptimer but it won't kill it. It will refire again. This has caused me ample problems. Try it "excelent" wrote: Maby u can use this as inspiration http://pmexcelent.dk/FileMaster.xls |
Loop with Time
Sorry, I do get the problem. I added some debug code previously that
overrode it. Don't have a solution as yet, but I will post back if I think of anything. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... It does not reopen if you run the sub in tta.xls manually. If you run it programatically it does reopen. I have tried it on several computers Office 2003 and 07, all the same. Please let me know. Thanks. "Bob Phillips" wrote: Well I have just tried it and it works fine for me. File B does not re-open at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... I wish I were wrong, I really wish. Try this: tta.xls Module1 Sub main() Workbooks("ttb.xls").Close SaveChanges:=False End Sub ttb.xls ThisWorkbook Public Sub Workbook_Open() timer End Sub Public Sub Workbook_BeforeClose(cancel As Boolean) cancel_timer End Sub ttb.xls Module1 Public rt As Double Public Sub timer() rt = Now + TimeValue("00:00:02") Application.OnTime rt, "timer" End Sub Public Sub cancel_timer() Application.OnTime rt, "timer", , False End Sub Do this now: Open ttb.xls Open tta.xls Assing sub main() in tta.xls to a button, click on the button, it does close ttb.xls but....ttb.xls reopens. This is not supposed to happen and it creates all sorts of problems. If you know that I am wrong please correct me, you would help me tremendously. There is a workaround, but it is not reliable. "Bob Phillips" wrote: As long as you call it with exactly the same scheduled time as when you set it, it works fine. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Bob, I have done intensive work on this issue. I know how it is suposed to be done but it just does not work. It is a bug, an annoying one. Again, try closing a workbook with a live On Time procedure from a different workbook, if you rely on the Before_Close event it does not work. It never works. You have to call the kill timer sub independently and it does not always work. "Bob Phillips" wrote: You should kill the timer. Chip Pearson explains it all on http://www.cpearson.com/excel/ontime.htm -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Excelent, I have been looking at your code. Since you have worked with the Application On Time command I wonder if you have run into the following difficulty: If you try to close the workbook that has the timer running from another workbook, programatically, the Before_Close event will run the Stoptimer but it won't kill it. It will refire again. This has caused me ample problems. Try it "excelent" wrote: Maby u can use this as inspiration http://pmexcelent.dk/FileMaster.xls |
Loop with Time
Hi Bob,
I appreciate your quality contribution to this forum, I learn a lot from your comments. The issue is now in good hands. My workaround is in tta.xls: Application.Run "cancel_timer" Workbooks("ttb.xls").Close SaveChanges:=False The problem is that it does not work reliably. If you let the workbook (not the simple test one, but a real big workbook) run for a while, when it comes to be closed it throws an error. On Error Goto Next it is not a solution. Perhaps it happens more on data intensive applications like mine. Regards, Antonio "Bob Phillips" wrote: Sorry, I do get the problem. I added some debug code previously that overrode it. Don't have a solution as yet, but I will post back if I think of anything. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... It does not reopen if you run the sub in tta.xls manually. If you run it programatically it does reopen. I have tried it on several computers Office 2003 and 07, all the same. Please let me know. Thanks. "Bob Phillips" wrote: Well I have just tried it and it works fine for me. File B does not re-open at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... I wish I were wrong, I really wish. Try this: tta.xls Module1 Sub main() Workbooks("ttb.xls").Close SaveChanges:=False End Sub ttb.xls ThisWorkbook Public Sub Workbook_Open() timer End Sub Public Sub Workbook_BeforeClose(cancel As Boolean) cancel_timer End Sub ttb.xls Module1 Public rt As Double Public Sub timer() rt = Now + TimeValue("00:00:02") Application.OnTime rt, "timer" End Sub Public Sub cancel_timer() Application.OnTime rt, "timer", , False End Sub Do this now: Open ttb.xls Open tta.xls Assing sub main() in tta.xls to a button, click on the button, it does close ttb.xls but....ttb.xls reopens. This is not supposed to happen and it creates all sorts of problems. If you know that I am wrong please correct me, you would help me tremendously. There is a workaround, but it is not reliable. "Bob Phillips" wrote: As long as you call it with exactly the same scheduled time as when you set it, it works fine. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Bob, I have done intensive work on this issue. I know how it is suposed to be done but it just does not work. It is a bug, an annoying one. Again, try closing a workbook with a live On Time procedure from a different workbook, if you rely on the Before_Close event it does not work. It never works. You have to call the kill timer sub independently and it does not always work. "Bob Phillips" wrote: You should kill the timer. Chip Pearson explains it all on http://www.cpearson.com/excel/ontime.htm -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Excelent, I have been looking at your code. Since you have worked with the Application On Time command I wonder if you have run into the following difficulty: If you try to close the workbook that has the timer running from another workbook, programatically, the Before_Close event will run the Stoptimer but it won't kill it. It will refire again. This has caused me ample problems. Try it "excelent" wrote: Maby u can use this as inspiration http://pmexcelent.dk/FileMaster.xls |
Loop with Time
Antonio,
I tried something like that, but the way that you have it I cannot see that working at all. cancel_timer is in tlb not tla, so you need to qualify with the workbook and that is where it failed for me. I will continue trying so keep checking back (upto the end of the week). -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Bob, I appreciate your quality contribution to this forum, I learn a lot from your comments. The issue is now in good hands. My workaround is in tta.xls: Application.Run "cancel_timer" Workbooks("ttb.xls").Close SaveChanges:=False The problem is that it does not work reliably. If you let the workbook (not the simple test one, but a real big workbook) run for a while, when it comes to be closed it throws an error. On Error Goto Next it is not a solution. Perhaps it happens more on data intensive applications like mine. Regards, Antonio "Bob Phillips" wrote: Sorry, I do get the problem. I added some debug code previously that overrode it. Don't have a solution as yet, but I will post back if I think of anything. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... It does not reopen if you run the sub in tta.xls manually. If you run it programatically it does reopen. I have tried it on several computers Office 2003 and 07, all the same. Please let me know. Thanks. "Bob Phillips" wrote: Well I have just tried it and it works fine for me. File B does not re-open at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... I wish I were wrong, I really wish. Try this: tta.xls Module1 Sub main() Workbooks("ttb.xls").Close SaveChanges:=False End Sub ttb.xls ThisWorkbook Public Sub Workbook_Open() timer End Sub Public Sub Workbook_BeforeClose(cancel As Boolean) cancel_timer End Sub ttb.xls Module1 Public rt As Double Public Sub timer() rt = Now + TimeValue("00:00:02") Application.OnTime rt, "timer" End Sub Public Sub cancel_timer() Application.OnTime rt, "timer", , False End Sub Do this now: Open ttb.xls Open tta.xls Assing sub main() in tta.xls to a button, click on the button, it does close ttb.xls but....ttb.xls reopens. This is not supposed to happen and it creates all sorts of problems. If you know that I am wrong please correct me, you would help me tremendously. There is a workaround, but it is not reliable. "Bob Phillips" wrote: As long as you call it with exactly the same scheduled time as when you set it, it works fine. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Bob, I have done intensive work on this issue. I know how it is suposed to be done but it just does not work. It is a bug, an annoying one. Again, try closing a workbook with a live On Time procedure from a different workbook, if you rely on the Before_Close event it does not work. It never works. You have to call the kill timer sub independently and it does not always work. "Bob Phillips" wrote: You should kill the timer. Chip Pearson explains it all on http://www.cpearson.com/excel/ontime.htm -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Antonio" wrote in message ... Hi Excelent, I have been looking at your code. Since you have worked with the Application On Time command I wonder if you have run into the following difficulty: If you try to close the workbook that has the timer running from another workbook, programatically, the Before_Close event will run the Stoptimer but it won't kill it. It will refire again. This has caused me ample problems. Try it "excelent" wrote: Maby u can use this as inspiration http://pmexcelent.dk/FileMaster.xls |
| All times are GMT +1. The time now is 05:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com