Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Time
I am using On Time routine from Chip Pearsons site as Follows:
Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() My code StartTimer End Sub Where do I sore the code and how do I actually start the procedure/ Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Time
In the vbe with our workbook as the active project,
Insert=Module Place it there To start it, in the project explorer in the vbe, select the ThisWorkbook entry. In the dropdowns at the top, select workbook on the left and Open on the right. Private Sub Workbook_Open StartTimer ' or if you want to run you code on opening 'Macro2 End Sub -- Regards, Tom Ogilvy "Henry" wrote in message ... I am using On Time routine from Chip Pearsons site as Follows: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() My code StartTimer End Sub Where do I sore the code and how do I actually start the procedure/ Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Time
You don't start the timer in the ontime procedure, you start it outside. The
OnTime will run that procedure after the specified delay. Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub Invokee() StartTimer End Sub Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() My code End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Henry" wrote in message ... I am using On Time routine from Chip Pearsons site as Follows: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() My code StartTimer End Sub Where do I sore the code and how do I actually start the procedure/ Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Time
Hi Tom
Thanks for the reply When I open the workbook I get Compile Error: Ambiguous name detected: StartTimer "Tom Ogilvy" wrote: In the vbe with our workbook as the active project, Insert=Module Place it there To start it, in the project explorer in the vbe, select the ThisWorkbook entry. In the dropdowns at the top, select workbook on the left and Open on the right. Private Sub Workbook_Open StartTimer ' or if you want to run you code on opening 'Macro2 End Sub -- Regards, Tom Ogilvy "Henry" wrote in message ... I am using On Time routine from Chip Pearsons site as Follows: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() My code StartTimer End Sub Where do I sore the code and how do I actually start the procedure/ Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Time
Do you have two procedures named StartTimer? That is the usual cause of this
error. Also, you should not name a module the same as a procedure, although would be more unlikely to be the case. -- Regards, Tom Ogilvy "Henry" wrote in message ... Hi Tom Thanks for the reply When I open the workbook I get Compile Error: Ambiguous name detected: StartTimer "Tom Ogilvy" wrote: In the vbe with our workbook as the active project, Insert=Module Place it there To start it, in the project explorer in the vbe, select the ThisWorkbook entry. In the dropdowns at the top, select workbook on the left and Open on the right. Private Sub Workbook_Open StartTimer ' or if you want to run you code on opening 'Macro2 End Sub -- Regards, Tom Ogilvy "Henry" wrote in message ... I am using On Time routine from Chip Pearsons site as Follows: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() My code StartTimer End Sub Where do I sore the code and how do I actually start the procedure/ Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Time
OK I did find a duplicate StartTimer()
I have deleted all code except module1: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() ' ' Application.DisplayAlerts = False applications.ScreenUpdating = False Workbooks.OpenText Filename:="C:\Mypath\myfile.txt", _ Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1)), TrailingMinusNumbers:=True ActiveWorkbook.SaveAs Filename:= _ "C:\Mypath\myfile.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close Application.DisplayAlerts = True Applications.ScreenUpdating = True StartTimer End Sub and on ThisWorkbook Private Sub Workbook_Open() StartTimer Macro2 End Sub Macro2 runs on open but the 30 second update is not working Thanks "Tom Ogilvy" wrote: Do you have two procedures named StartTimer? That is the usual cause of this error. Also, you should not name a module the same as a procedure, although would be more unlikely to be the case. -- Regards, Tom Ogilvy "Henry" wrote in message ... Hi Tom Thanks for the reply When I open the workbook I get Compile Error: Ambiguous name detected: StartTimer "Tom Ogilvy" wrote: In the vbe with our workbook as the active project, Insert=Module Place it there To start it, in the project explorer in the vbe, select the ThisWorkbook entry. In the dropdowns at the top, select workbook on the left and Open on the right. Private Sub Workbook_Open StartTimer ' or if you want to run you code on opening 'Macro2 End Sub -- Regards, Tom Ogilvy "Henry" wrote in message ... I am using On Time routine from Chip Pearsons site as Follows: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() My code StartTimer End Sub Where do I sore the code and how do I actually start the procedure/ Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Time
Well, by calling macro2, which calls starttimer and calling starttimer
directly, you should have two runs scheduled rather than none at all. What I was suggesting was to call one or the other, not both: Private Sub Workbook_Open() Macro2 End Sub Macro2 executes a starttimer, so it should continue the chain. -- Regards, Tom Ogilvy "Henry" wrote in message ... OK I did find a duplicate StartTimer() I have deleted all code except module1: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() ' ' Application.DisplayAlerts = False applications.ScreenUpdating = False Workbooks.OpenText Filename:="C:\Mypath\myfile.txt", _ Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1)), TrailingMinusNumbers:=True ActiveWorkbook.SaveAs Filename:= _ "C:\Mypath\myfile.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close Application.DisplayAlerts = True Applications.ScreenUpdating = True StartTimer End Sub and on ThisWorkbook Private Sub Workbook_Open() StartTimer Macro2 End Sub Macro2 runs on open but the 30 second update is not working Thanks "Tom Ogilvy" wrote: Do you have two procedures named StartTimer? That is the usual cause of this error. Also, you should not name a module the same as a procedure, although would be more unlikely to be the case. -- Regards, Tom Ogilvy "Henry" wrote in message ... Hi Tom Thanks for the reply When I open the workbook I get Compile Error: Ambiguous name detected: StartTimer "Tom Ogilvy" wrote: In the vbe with our workbook as the active project, Insert=Module Place it there To start it, in the project explorer in the vbe, select the ThisWorkbook entry. In the dropdowns at the top, select workbook on the left and Open on the right. Private Sub Workbook_Open StartTimer ' or if you want to run you code on opening 'Macro2 End Sub -- Regards, Tom Ogilvy "Henry" wrote in message ... I am using On Time routine from Chip Pearsons site as Follows: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() My code StartTimer End Sub Where do I sore the code and how do I actually start the procedure/ Thanks! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Time
Latest Recap if I understand correctly...
In this workbook Private Sub Workbook_Open() StartTimer End Sub Module1 Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRunWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRunWhat, schedule:=True End Sub Sub Macro2() Workbooks.OpenText Filename:="C:\Mypath\myfile.txt", _ Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1)), TrailingMinusNumbers:=True ActiveWorkbook.SaveAs Filename:= _ "C:\mypath\myfile.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close End Sub And it still doesnt run. Im sorry Im just not getting it. I aprreciate all of your help. "Tom Ogilvy" wrote: Well, by calling macro2, which calls starttimer and calling starttimer directly, you should have two runs scheduled rather than none at all. What I was suggesting was to call one or the other, not both: Private Sub Workbook_Open() Macro2 End Sub Macro2 executes a starttimer, so it should continue the chain. -- Regards, Tom Ogilvy "Henry" wrote in message ... OK I did find a duplicate StartTimer() I have deleted all code except module1: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() ' ' Application.DisplayAlerts = False applications.ScreenUpdating = False Workbooks.OpenText Filename:="C:\Mypath\myfile.txt", _ Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1)), TrailingMinusNumbers:=True ActiveWorkbook.SaveAs Filename:= _ "C:\Mypath\myfile.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close Application.DisplayAlerts = True Applications.ScreenUpdating = True StartTimer End Sub and on ThisWorkbook Private Sub Workbook_Open() StartTimer Macro2 End Sub Macro2 runs on open but the 30 second update is not working Thanks "Tom Ogilvy" wrote: Do you have two procedures named StartTimer? That is the usual cause of this error. Also, you should not name a module the same as a procedure, although would be more unlikely to be the case. -- Regards, Tom Ogilvy "Henry" wrote in message ... Hi Tom Thanks for the reply When I open the workbook I get Compile Error: Ambiguous name detected: StartTimer "Tom Ogilvy" wrote: In the vbe with our workbook as the active project, Insert=Module Place it there To start it, in the project explorer in the vbe, select the ThisWorkbook entry. In the dropdowns at the top, select workbook on the left and Open on the right. Private Sub Workbook_Open StartTimer ' or if you want to run you code on opening 'Macro2 End Sub -- Regards, Tom Ogilvy "Henry" wrote in message ... I am using On Time routine from Chip Pearsons site as Follows: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() My code StartTimer End Sub Where do I sore the code and how do I actually start the procedure/ Thanks! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Time
You had some typos in your commands. This worked for me:
In module1 Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRunWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, schedule:=True End Sub Sub Stop_Timer() Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, schedule:=False End Sub Sub Macro2() On Error Resume Next Kill "C:\Mypath\myfile.xls" On Error GoTo 0 Workbooks.OpenText Filename:="C:\Mypath\myfile.txt", _ Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1)), TrailingMinusNumbers:=True ActiveWorkbook.SaveAs Filename:= _ "C:\mypath\myfile.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close SaveChanges:=False ' added line to schedule the next run StartTimer End Sub -------------------- In the Thisworkbook Module Private Sub Workbook_Open() StartTimer End Sub -- Regards, Tom Ogilvy -------- "Henry" wrote in message ... Latest Recap if I understand correctly... In this workbook Private Sub Workbook_Open() StartTimer End Sub Module1 Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRunWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRunWhat, schedule:=True End Sub Sub Macro2() Workbooks.OpenText Filename:="C:\Mypath\myfile.txt", _ Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1)), TrailingMinusNumbers:=True ActiveWorkbook.SaveAs Filename:= _ "C:\mypath\myfile.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close End Sub And it still doesnt run. Im sorry Im just not getting it. I aprreciate all of your help. "Tom Ogilvy" wrote: Well, by calling macro2, which calls starttimer and calling starttimer directly, you should have two runs scheduled rather than none at all. What I was suggesting was to call one or the other, not both: Private Sub Workbook_Open() Macro2 End Sub Macro2 executes a starttimer, so it should continue the chain. -- Regards, Tom Ogilvy "Henry" wrote in message ... OK I did find a duplicate StartTimer() I have deleted all code except module1: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() ' ' Application.DisplayAlerts = False applications.ScreenUpdating = False Workbooks.OpenText Filename:="C:\Mypath\myfile.txt", _ Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1)), TrailingMinusNumbers:=True ActiveWorkbook.SaveAs Filename:= _ "C:\Mypath\myfile.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close Application.DisplayAlerts = True Applications.ScreenUpdating = True StartTimer End Sub and on ThisWorkbook Private Sub Workbook_Open() StartTimer Macro2 End Sub Macro2 runs on open but the 30 second update is not working Thanks "Tom Ogilvy" wrote: Do you have two procedures named StartTimer? That is the usual cause of this error. Also, you should not name a module the same as a procedure, although would be more unlikely to be the case. -- Regards, Tom Ogilvy "Henry" wrote in message ... Hi Tom Thanks for the reply When I open the workbook I get Compile Error: Ambiguous name detected: StartTimer "Tom Ogilvy" wrote: In the vbe with our workbook as the active project, Insert=Module Place it there To start it, in the project explorer in the vbe, select the ThisWorkbook entry. In the dropdowns at the top, select workbook on the left and Open on the right. Private Sub Workbook_Open StartTimer ' or if you want to run you code on opening 'Macro2 End Sub -- Regards, Tom Ogilvy "Henry" wrote in message ... I am using On Time routine from Chip Pearsons site as Follows: Public RunWhen As Double Public Const cRunIntervalSeconds = 30 Public Const cRUnWhat = "Macro2" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earlisttime = RunWhen, procedu=cRUnWhat, schedule:=True End Sub Sub Macro2() My code StartTimer End Sub Where do I sore the code and how do I actually start the procedure/ Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time calculations for Scheduled Time vs. Actual Time Worked | Excel Discussion (Misc queries) | |||
straight time, time and a half, and double time | Excel Discussion (Misc queries) | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions |